You need to generate a report that shows, for each customer, their total order amount and the percentage of the company's total that this represents. The data is in Orders with columns CustomerID and Amount. You also need to rank customers by total amount. Which query structure should you use to achieve all three requirements in a single query?

Use a correlated subquery to compute the grand total for each row and then use ROW_NUMBER() for ranking.

Use a self-join to compute the grand total and then use DENSE_RANK() for ranking.

Use GROUP BY with ROLLUP to get the grand total and then use RANK() in a separate query.

Use a CTE to aggregate totals per customer, then use window functions SUM() OVER () for the grand total and RANK() OVER (ORDER BY TotalAmount DESC) for ranking.

A stored procedure performs multiple data modification statements. If any statement fails, you need to ensure that all changes are rolled back and an error is logged. Which pattern should you implement?

BEGIN TRY ... BEGIN TRANSACTION ... COMMIT ... END TRY BEGIN CATCH ... ROLLBACK ... Log error ... END CATCH

Use a cursor to execute each statement and check @@ERROR after each one

Use RAISERROR before each statement to check for potential errors

Use IF @@ROWCOUNT = 0 to detect failures

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 3

Write Advanced T-SQL Code DP-800 Practice Questions (Page 2)

Part of the Design and develop database solutions domain, which accounts for 35–40% of the DP-800 exam. Microsoft does not publish an official question count, but from its 120-minute exam (~50–80 total, ~18–32 in this domain), expect 5–8 from this objective — we provide 31 practice questions to prepare you well beyond it. (estimate)

31questions here

7free pages

8concepts

35–40%of the exam

Questions 6–10

6foundation · easy
Which T-SQL function is used to replace substrings that match a regular expression pattern?
Select an answer first
7foundation · easy
Which T-SQL function is designed to measure the similarity between two strings using the Jaro-Winkler algorithm?
Select an answer first
8expert · hard
You need to generate a report that shows, for each customer, their total order amount and the percentage of the company's total that this represents. The data is in Orders with columns CustomerID and Amount. You also need to rank customers by total amount. Which query structure should you use to achieve all three requirements in a single query?
Select an answer first
9application · medium
A stored procedure performs multiple data modification statements. If any statement fails, you need to ensure that all changes are rolled back and an error is logged. Which pattern should you implement?
Select an answer first
10foundation · easy
Which T-SQL function is used to construct a JSON object from a list of key-value pairs?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-800

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-800” is a trademark of its owner, used for identification only.

Write Advanced T-SQL Code DP-800 Practice Questions (Page 2)

Questions 6–10

Which T-SQL function is used to replace substrings that match a regular expression pattern?

Which T-SQL function is designed to measure the similarity between two strings using the Jaro-Winkler algorithm?

A stored procedure performs multiple data modification statements. If any statement fails, you need to ensure that all changes are rolled back and an error is logged. Which pattern should you implement?

Which T-SQL function is used to construct a JSON object from a list of key-value pairs?

Keep studying — Design and develop database solutions