100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 270
A query must pick latest order per customer. The team selects: Use ROW_NUMBER by customer and descending OrderDate. Which implementation action puts this design into practice?
- Build JSON_OBJECT('id':OrderID,'total':Amount)
- Partition by CustomerID, order by OrderDate DESC and stable key
- OUTER APPLY(SELECT TOP 1 ... WHERE CustomerID=... ORDER BY PaidAt DESC)
- Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID)
Explanation
Correct Answer
B:Partition by CustomerID, order by OrderDate DESC and stable key. This is the concrete setup for use row_number by customer and descending orderdate, and compare selected order with each customer's maximum date supplies a direct confirmation.
Incorrect Answers
A:Build JSON_OBJECT('id':OrderID,'total':Amount). This concerns a developer creates json object from named SQL expressions. Here, partition by CustomerID, order by OrderDate DESC and stable key.
C:OUTER APPLY(SELECT TOP 1 ... WHERE CustomerID=... ORDER BY PaidAt DESC). This concerns each order with customer's most recent payment. Here, partition by CustomerID, order by OrderDate DESC and stable key.
D:Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID). This concerns top three sales orders per salesperson. Here, partition by CustomerID, order by OrderDate DESC and stable key.
Question 271
A query must pick latest order per customer. After implementing the design, which check most directly validates the expected behavior?
- Validate output array content for sample orders
- Compare selected order with each customer's maximum date
- Test customers with no payments retain outer row
- Compare resulting rows and typed amounts
Explanation
Correct Answer
B:Compare selected order with each customer's maximum date. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that window order omits stable tie breaker.
Incorrect Answers
A:Validate output array content for sample orders. This concerns a json array aggregation produces product ids per customer. Instead, compare selected order with each customer's maximum date.
C:Test customers with no payments retain outer row. This concerns each order with customer's most recent payment. Instead, compare selected order with each customer's maximum date.
D:Compare resulting rows and typed amounts. This concerns a json payload includes an order array to become SQL rows. Instead, compare selected order with each customer's maximum date.
Question 272
A query must pick latest order per customer. A test instead finds: Two same-day orders return arbitrary selected rows. Which underlying issue best explains this finding?
- Window ordering lacks a deterministic tie-breaker
- Window order omits stable tie breaker
- NOT IN was used with nullable values instead of NOT EXISTS
- CROSS APPLY used where OUTER APPLY was required
Explanation
Correct Answer
B:Window order omits stable tie breaker. It accounts for the specific observed failure: two same-day orders return arbitrary selected rows. The intended solution is use row_number by customer and descending orderdate with the required setup.
Incorrect Answers
A:Window ordering lacks a deterministic tie-breaker. This concerns top ranks drift across executions on ties. Here, window order omits stable tie breaker.
C:NOT IN was used with nullable values instead of NOT EXISTS. This concerns nulls in subquery yield empty results. Here, window order omits stable tie breaker.
D:CROSS APPLY used where OUTER APPLY was required. This concerns orders vanish when payment row is absent. Here, window order omits stable tie breaker.
Question 273
A JSON payload includes an order array to become SQL rows. Which database design or engineering approach most directly meets this requirement?
- Use JSON_OBJECT with supported key:value pairs
- Use OUTER APPLY with TOP(1) ORDER BY
- Use JSON_ARRAYAGG where supported
- Use OPENJSON WITH to shred the array
Explanation
Correct Answer
D:Use OPENJSON WITH to shred the array. The implementation is openjson(@payload,'$.orders') with typed properties; success can be checked by compare resulting rows and typed amounts. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use JSON_OBJECT with supported key:value pairs. This concerns a developer creates json object from named SQL expressions. Instead, use OPENJSON WITH to shred the array.
B:Use OUTER APPLY with TOP(1) ORDER BY. This concerns each order with customer's most recent payment. Instead, use OPENJSON WITH to shred the array.
C:Use JSON_ARRAYAGG where supported. This concerns a json array aggregation produces product ids per customer. Instead, use OPENJSON WITH to shred the array.
Question 274
A JSON payload includes an order array to become SQL rows. The team selects: Use OPENJSON WITH to shred the array. Which implementation action puts this design into practice?
- Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID)
- OPENJSON(@payload,'$.orders') WITH typed properties
- JSON_VALUE(Payload,'$.customer.id') with appropriate cast
- WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE CustomerID=c.ID)
Explanation
Correct Answer
B:OPENJSON(@payload,'$.orders') WITH typed properties. This is the concrete setup for use openjson with to shred the array, and compare resulting rows and typed amounts supplies a direct confirmation.
Incorrect Answers
A:Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID). This concerns top three sales orders per salesperson. Here, oPENJSON(@payload,'$.orders') WITH typed properties.
C:JSON_VALUE(Payload,'$.customer.id') with appropriate cast. This concerns reading one scalar value from json document. Here, oPENJSON(@payload,'$.orders') WITH typed properties.
D:WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE CustomerID=c.ID). This concerns finding customers with no orders. Here, oPENJSON(@payload,'$.orders') WITH typed properties.
Question 275
A JSON payload includes an order array to become SQL rows. After implementing the design, which check most directly validates the expected behavior?
- Compare extraction with known log text
- Validate output array content for sample orders
- Compare selected order with each customer's maximum date
- Compare resulting rows and typed amounts
Explanation
Correct Answer
D:Compare resulting rows and typed amounts. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that json path or case-sensitive property names do not match.
Incorrect Answers
A:Compare extraction with known log text. This concerns a log-cleaning query extracts the first matched token. Instead, compare resulting rows and typed amounts.
B:Validate output array content for sample orders. This concerns a json array aggregation produces product ids per customer. Instead, compare resulting rows and typed amounts.
C:Compare selected order with each customer's maximum date. This concerns pick latest order per customer. Instead, compare resulting rows and typed amounts.
Question 276
A JSON payload includes an order array to become SQL rows. A test instead finds: Nested array values are NULL in projection. Which underlying issue best explains this finding?
- JSON path or case-sensitive property names do not match
- STRING_AGG used instead of required JSON array
- REPLACE used where a pattern was required
- JSON path is wrong or payload shape differs
Explanation
Correct Answer
A:JSON path or case-sensitive property names do not match. It accounts for the specific observed failure: nested array values are null in projection. The intended solution is use openjson with to shred the array with the required setup.
Incorrect Answers
B:STRING_AGG used instead of required JSON array. This concerns aggregated values become comma-separated plain text. The relevant SQL or system operation is JSON path or case-sensitive property names do not match.
C:REPLACE used where a pattern was required. This concerns only literal substring is replaced. The relevant SQL or system operation is JSON path or case-sensitive property names do not match.
D:JSON path is wrong or payload shape differs. This concerns returned value is null for present nested field. The relevant SQL or system operation is JSON path or case-sensitive property names do not match.
Question 277
A developer must read one scalar value from JSON document. Which database design or engineering approach most directly meets this requirement?
- Use FOR JSON PATH with documented nesting aliases
- Use JSON_VALUE for scalar extraction
- Use JSON_OBJECT with supported key:value pairs
- Use COUNT(*) OVER(PARTITION BY TeamID)
Explanation
Correct Answer
B:Use JSON_VALUE for scalar extraction. The implementation is json_value(payload,'$.customer.id') with appropriate cast; success can be checked by test extraction against known example payload. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use FOR JSON PATH with documented nesting aliases. This concerns a service sends SQL query results as nested json objects. Instead, use JSON_VALUE for scalar extraction.
C:Use JSON_OBJECT with supported key:value pairs. This concerns a developer creates json object from named SQL expressions. Instead, use JSON_VALUE for scalar extraction.
D:Use COUNT(*) OVER(PARTITION BY TeamID). This concerns counting rows per team while retaining row detail. Instead, use JSON_VALUE for scalar extraction.
Question 278
A developer must read one scalar value from JSON document. The team selects: Use JSON_VALUE for scalar extraction. Which implementation action puts this design into practice?
- Build JSON_OBJECT('id':OrderID,'total':Amount)
- DENSE_RANK() OVER(ORDER BY Score DESC)
- OPENJSON(@payload,'$.orders') WITH typed properties
- JSON_VALUE(Payload,'$.customer.id') with appropriate cast
Explanation
Correct Answer
D:JSON_VALUE(Payload,'$.customer.id') with appropriate cast. This is the concrete setup for use json_value for scalar extraction, and test extraction against known example payload supplies a direct confirmation.
Incorrect Answers
A:Build JSON_OBJECT('id':OrderID,'total':Amount). This concerns a developer creates json object from named SQL expressions. Here, jSON_VALUE(Payload,'$.customer.id') with appropriate cast.
B:DENSE_RANK() OVER(ORDER BY Score DESC). This concerns a query ranks tied products by same position without gaps. Here, jSON_VALUE(Payload,'$.customer.id') with appropriate cast.
C:OPENJSON(@payload,'$.orders') WITH typed properties. This concerns a json payload includes an order array to become SQL rows. Here, jSON_VALUE(Payload,'$.customer.id') with appropriate cast.
Question 279
A developer must read one scalar value from JSON document. After implementing the design, which check most directly validates the expected behavior?
- Verify customers with zero rows are included
- Test extraction against known example payload
- Parse output and verify key types
- Compare extraction with known log text
Explanation
Correct Answer
B:Test extraction against known example payload. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that json path is wrong or payload shape differs.
Incorrect Answers
A:Verify customers with zero rows are included. This concerns finding customers with no orders. Instead, test extraction against known example payload.
C:Parse output and verify key types. This concerns a developer creates json object from named SQL expressions. Instead, test extraction against known example payload.
D:Compare extraction with known log text. This concerns a log-cleaning query extracts the first matched token. Instead, test extraction against known example payload.
Question 280
A developer must read one scalar value from JSON document. A test instead finds: Returned value is NULL for present nested field. Which underlying issue best explains this finding?
- JSON path or case-sensitive property names do not match
- JSON path is wrong or payload shape differs
- Default RANGE frame was used instead of intended ROWS frame
- Unnecessary string conversion altered JSON types
Explanation
Correct Answer
B:JSON path is wrong or payload shape differs. It accounts for the specific observed failure: returned value is null for present nested field. The intended solution is use json_value for scalar extraction with the required setup.
Incorrect Answers
A:JSON path or case-sensitive property names do not match. This concerns nested array values are null in projection. The relevant SQL or system operation is JSON path is wrong or payload shape differs.
C:Default RANGE frame was used instead of intended ROWS frame. This concerns daily running totals are unexpectedly grouped by tied dates. The relevant SQL or system operation is JSON path is wrong or payload shape differs.
D:Unnecessary string conversion altered JSON types. This concerns generated output contains string-encoded numbers. The relevant SQL or system operation is JSON path is wrong or payload shape differs.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests