support@Free-Braindumps.com
Register Login
Cisco
CompTIA
ISC
EC-Council
EXIN
Fortinet
ITIL
Juniper
Microsoft
VMware
Avaya
SAP
Google
NVIDIA
Salesforce
Amazon
Palo Alto Networks
ISC2
Splunk
ServiceNow
All Vendors
  1. Home
  2. Exams
  3. Microsoft
  4. DP-800

Microsoft DP-800 Exam Prep
Developing AI-Enabled Database Solutions (Page 4 )

Updated On: 13-Sep-2026
Page 2 of 14
PDF with 101 Questions
View Related Case Study

HOTSPOT (Drag and Drop is not supported)
You need to meet the development requirements for the FeedbackJson column.
How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:


Scenario: Contoso identifies the following requirements for querying data in the FeedbackJson column of the CustomerFeedback table:
Extract the customer feedback text from the JSON document. Filter rows where the JSON text contains a keyword. Calculate a fuzzy similarity score between the feedback text and a known issue description. Order the results by similarity score, with the highest score first.
---The FeedbackJson column has a full-text index and stores JSON documents in the following format.

Box 1: JSON_VALUE(f.FeedbackJson, '$.text') AS FeedbackText Use the JSON_VALUE function.
Example Query Assuming you have a table named CustomerFeedback with a column FeedbackDetails (of type NVARCHAR (MAX) or the new JSON native type) and you want to find feedback where the comment field is similar to the word 'unhappy' (allowing for a maximum edit distance of 1):
DECLARE @Keyword NVARCHAR(100) = 'unhappy'; DECLARE @MaxDistance INT = 1;
SELECT FeedbackID, JSON_VALUE(FeedbackDetails, '$.comment') AS ExtractedComment FROM CustomerFeedback WHERE EDIT_DISTANCE(JSON_VALUE(FeedbackDetails, '$.comment'), @Keyword) <= @MaxDistance;
Box 2: EDIT_DISTANCE(JSON_VALUE(F.FeedbackJson, '$.text'), @KEYWORD) <= 3 Scenario: Filter rows where the JSON text contains a keyword.
This is in the WHERE statement that filters the resulting rows. The JSON key is "text", not "details.comment".
Note: To filter rows in an Azure SQL database where a specific value within a JSON column has an edit distance relationship to a @KEYWORD, the WHERE clause can use the EDIT_DISTANCE function in conjunction with JSON_VALUE.
The WHERE clause of the T-SQL statement should be formed as follows:
WHERE EDIT_DISTANCE(JSON_VALUE(FeedbackColumn, '$.YourJsonKey'), @KEYWORD) <= @MaxDistance
FeedbackColumn: This is the name of your table column that stores the customer feedback in JSON format.
$.YourJsonKey: This is the JSON path expression used by the JSON_VALUE function to extract the scalar value (e.g., text, number) from the JSON text within the FeedbackColumn. You will need to replace '$.YourJsonKey' with the actual path to the specific field in your JSON data you want to filter on (e.g., '$.comment', '$.feedbackSummary').
@KEYWORD: This is a variable holding the target keyword you are comparing against. @MaxDistance or @MinSimilarity: The EDIT_DISTANCE function returns an integer representing the number
of transformations (insertions, deletions, substitutions) needed to change one string into the other. EDIT_DISTANCE_SIMILARITY returns a percentage value from 0 to 100. You would define a threshold to filter results based on how "close" the JSON value is to your keyword.
Box 3: SimilarityScore Scenario: Order the results by similarity score, with the highest score first.
This is in the ORDER BY statement.



View Related Case Study

HOTSPOT (Drag and Drop is not supported)
You need to create a solution that meets the development requirements for retrieving the patient lists.
How should you complete the Transact-SQL code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:



Scenario: Fabrikam identifies the following development requirements: *-> Provide the functionality to retrieve a list of the names of patients who underwent medical procedures during the last 30 days.
Box 1: WHERE p.PatientId IN WHERE: Used to filter the rows returned by the main query. IN: Used to check if the PatientId exists within the list generated by the subquery.
Box 2: WHERE pr.PatientId = p.PatientId
Correlated Subquery: Using WHERE pr.PatientId = p.PatientId links the subquery to the main query, ensuring the database only looks at procedures belonging to that specific patient.
Incorrect: * HAVING HAVING is used to filter groups after an GROUP BY clause is applied. Since your subquery does not use GROUP BY, HAVING would cause a syntax error.


Reference:

https://medium.com/sql-explained/sql-basics-for-everyone-part-2-1638493175cd



DRAG DROP (Drag and Drop is not supported)
You have an Azure SQL database that contains a table named dbo.Orders.
You have an application that calls a stored procedure named dbo.usp_CreateOrder to insert rows into dbo.Orders.
When an insert fails, the application receives inconsistent error details.
You need to implement error handling to ensure that any failures inside the procedure abort the transaction and return a consistent error to the caller.
How should you complete the stored procedure? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

  1. See Explanation section for answer.

Answer(s): A

Explanation:


Box 1: SET @OrderID = SCOPE_IDENTITY() By exclusion. The other statements cannot be inserted here.
Note: In an Azure SQL database, the code SET @OrderID = SCOPE_IDENTITY() captures the last auto-generated identity value (such as an ID) created by an INSERT statement and assigns it to a variable called @OrderID.
This is a standard way to retrieve the unique ID of a record you just created so you can use it in subsequent steps, like adding related "Order Details" rows.
Note 2: BEGIN TRY BEGIN TRANSACTION;
-- Your logic here (e.g., calling the INSERT) INSERT INTO YourTable (Column1) VALUES (@Param1);
-- If everything succeeds, commit COMMIT TRANSACTION;
Box 2: IF @@TRANCOUNT > 0
BEGIN CATCH Check for an active transaction before rolling back IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRANSACTION; END
-- Re-throw the error to the calling application THROW; END CATCH END;


Reference:



Your team is developing an Azure SQL dataset solution from a locally cloned GitHub repository by using Microsoft Visual Studio Code and GitHub Copilot Chat.
You need to disable the GitHub Copilot repository-level instructions for yourself without affecting other users.
What should you do?

  1. From Visual Studio Code, modify your GitHub Copilot Chat user settings.
  2. Add a --debug flag when you start the GitHub Copilot Chat extension.
  3. Delete .github/copilot-instructions.md.

Answer(s): A

Explanation:

To disable GitHub Copilot repository-level instructions for yourself without affecting others, you can modify your User Settings in Visual Studio Code. This allows you to override or ignore specific repository-wide configurations like copilot-instructions.md at a personal level.
How to Disable Repository-Level Instructions 1. Open User Settings: Press Ctrl+, (Windows/Linux) or Cmd+, (macOS) to open the VS Code Settings editor.
2. Search for Copilot Chat: In the search bar, type github.copilot.chat.customInstructions.
3. Configure Custom Instructions: Find the setting for Github > Copilot > Chat: Custom Instructions. Ensure the User tab is selected at the top to apply changes only to your account and not the shared workspace.
4. Toggle via Chat UI: Open the Chat view (Ctrl+Alt+I or Cmd+Shift+L). Click the Configure Chat (gear icon) at the bottom of the chat panel. Select the Instructions tab and uncheck or remove any active repository-level files to disable their influence on your session.


Reference:



You have an Azure SQL database that contains the following SQL graph tables:
-A NODE table named dbo.Person
-An EDGE table named dbo.Knows
Each row in dbo.Person contains the following columns:
-PersonID (int)
-DisplayName (nvarchar(100))
You need to use a MATCH operator and exactly two directed Knows relationships to return the PersonID and DisplayName of people that are reachable from the person identified by an input parameter named @StartPersonId.
Which Transact-SQL query should you use?





Answer(s): D

Explanation:

To achieve this using SQL Graph in Azure SQL, you use the MATCH clause to define the path. Since you need exactly two directed relationships (A → B → C), you must chain the edge table twice in the same direction.
Transact-SQL Statement SELECT Person3.ID, Person3.NameColumn -- Replace with your actual nvarchar(100) column name FROM PersonNodeTable AS Person1, KnowsEdgeTable AS Knows1, PersonNodeTable AS Person2, KnowsEdgeTable AS Knows2, PersonNodeTable AS Person3 WHERE MATCH(Person1-(Knows1)->Person2-(Knows2)->Person3) AND Person1.ID = @InputID; Use code with caution.
Key Components MATCH Clause: Defines the traversal pattern. The syntax (Node)-(Edge)->(Node) ensures the relationship is directed.
Chaining: To get "exactly two" steps, you define three node aliases and two edge aliases.
Aliases: Each instance of the table must have a unique alias (e.g., Person1, Person2, Person3) so the engine can distinguish between the different points in the path.
Filtering: The WHERE clause uses your input parameter (@InputID) to set the starting point of the graph traversal
Incorrect: [Not A] Need three persons, not two.
[Not B] Do not use JOIN.
[Not C] Incorrect MATCH statement.


Reference:



You have a SQL database in Microsoft Fabric that contains a column named Payload. Payload stores customer data in JSON documents that have the following format.

Data analysis shows that some customers have subaddressing in their email address, for example, user1 +promo@contoso.com.
You need to return a normalized email value that removes the subaddressing, for example, user1 +promo@contoso.com must be normalized to user1@contoso.com.
Which Transact-SQL expression should you use?

  1. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*$’, ‘’)
  2. REGEXP_SUBSTR(JSON_VALUE(Payload, ‘$.customer_email’), ‘^[^+]+@.*$=’)
  3. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*@’, ‘@’)
  4. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*’, ‘’)

Answer(s): C

Explanation:

In a Microsoft Fabric SQL database, you can use the REGEXP_REPLACE function (introduced as part of the SQL Server 2025/Always-up-to-date engine updates) to normalize email subaddresses.
Solution using REGEXP_REPLACE The command you proposed correctly extracts the email string from the JSON document and applies a regular expression to strip the plus-sign subaddress.
SELECT
REGEXP_REPLACE(JSON_VALUE(Payload, '$.customer_email'), '\+.*@', '@') AS NormalizedEmail FROM YourTable;
Breakdown of the Command JSON_VALUE: Efficiently extracts the customer_email as a scalar string from your Payload column.
REGEXP_REPLACE: Searches for the pattern \+.*@ (a literal plus sign followed by any characters until an @) and replaces that entire matched segment with just @.
Native Support: Unlike older versions of SQL Server that required complex CHARINDEX and SUBSTRING workarounds, Fabric SQL databases now include this modern regex functionality.


Reference:



DRAG DROP (Drag and Drop is not supported)
You have an Azure SQL database that contains a table named dbo.Orders. dbo.Orders contains a column named CreateDate that stores order creation dates.
You need to create a stored procedure that filters Orders by CreateDate for a single calendar day. The solution must be SARGable.
How should you complete the Transact-SQL code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

  1. See Explanation section for answer.

Answer(s): A

Explanation:


Box 1: DATEADD(day, 1, @StartDate) -- Define the boundaries for a SARGable range DECLARE @StartOfNextDay DATE = DATEADD(day, 1, @TargetDate);
See sample code below.
Box 2: @StartDate To filter a table by a single calendar day in a SARGable (Search ARGument-able) way, you must avoid applying functions (like YEAR(), MONTH(), or CAST()) to the database column itself. Applying a function to the column forces the database to perform an index scan (checking every row) rather than a more efficient index seek.
The recommended pattern is to use a half-open interval: Column >= StartDate AND Column < NextDay.
Box 3: @EndDate
Sample code: CREATE PROCEDURE dbo.GetOrdersByDate @TargetDate DATE AS BEGIN SET NOCOUNT ON;
-- Define the boundaries for a SARGable range DECLARE @StartOfNextDay DATE = DATEADD(day, 1, @TargetDate);
SELECT OrderID, OrderDate, CustomerID FROM dbo.Orders WHERE OrderDate >= @TargetDate AND OrderDate < @StartOfNextDay; END; GO


Reference:



You have an Azure SQL database.
You need to create a scalar user-defined function (UDF) that returns the number of whole years between an input parameter named @OrderDate and the current date/time as a single positive integer. The function must be created in Azure SQL Database.
You write the following code.

What should you insert at line 05?

  1. RETURN DATEDIFF(year, GETDATE(), @OrderDate);
  2. DATEDIFF(month, @orderdate, GETDATE()) / 12
  3. DATEPART(year, GETDATE()) - DATEPART(year, @orderdate)
  4. RETURN DATEDIFF(year, @OrderDate, GETDATE());

Answer(s): D

Explanation:

Use RETURN to produce the scalar value of the function.
In an Azure SQL Database scalar function (a user-defined function that returns a single value), you must use the RETURN statement to return the scalar value. The RETURN statement immediately terminates the function's execution and returns the value specified in its argument to the calling statement or procedure. The value returned must be of the data type specified in the RETURNS clause of the function definition.
The second argument to DATEDIFF should be @OrderDate as it is the start date, while the third argument is the end date, which is the current date.
Note: DATEDIFF (Transact-SQL)
This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
Syntax DATEDIFF ( datepart , startdate , enddate )
Arguments datepart Specifies the units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second.


Reference:



  PDF
Viewing page 4 of 14
Viewing questions 25 - 32 out of 101 questions


Post your Comments and Discuss Microsoft DP-800 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!