A query needs to return all employees whose salary is above the average salary of their department. The data is in Employees with columns EmployeeID, Name, DepartmentID, and Salary. Which query structure should you use?

Use a correlated subquery in the WHERE clause that computes the average salary for the employee's department.

Use a non-correlated subquery that computes the overall average salary and compare each employee's salary to it.

Use a self-join on DepartmentID and then filter using a HAVING clause.

Use a window function with AVG() OVER (PARTITION BY DepartmentID) and filter in the WHERE clause.

You receive a JSON string from an external API that contains an array of product objects. You need to insert these products into a relational Products table. Which T-SQL function should you use to parse the JSON and return the data as rows?

Use JSON_OBJECT to convert the JSON array into a relational format.

Use OPENJSON to parse the JSON array and then insert the results into the Products table.

Use STRING_SPLIT to split the JSON string by commas and then insert the values.

Use JSON_ARRAY to extract the array elements.

A stored procedure imports data from a staging table into a production table. If a data conversion error occurs during the import, you need to log the error details and roll back the entire transaction. Which error handling approach should you use?

Wrap the import in a TRY...CATCH block with a transaction, and in the CATCH block use THROW to re-raise the error after logging

Use a cursor to loop through rows and check each one manually

Use IF @@ERROR <> 0 after each statement to check for errors

Use RAISERROR before the import to preemptively check for issues

What is the defining characteristic of a correlated subquery in T-SQL?

It references columns from the outer query.

It is used only in the SELECT clause.

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 3

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

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 1–5

1application · medium
A query needs to return all employees whose salary is above the average salary of their department. The data is in Employees with columns EmployeeID, Name, DepartmentID, and Salary. Which query structure should you use?
Select an answer first
2application · medium
You receive a JSON string from an external API that contains an array of product objects. You need to insert these products into a relational Products table. Which T-SQL function should you use to parse the JSON and return the data as rows?
Select an answer first
3foundation · easy
Which T-SQL clause is used to begin the definition of a common table expression?
Select an answer first
4application · medium
A stored procedure imports data from a staging table into a production table. If a data conversion error occurs during the import, you need to log the error details and roll back the entire transaction. Which error handling approach should you use?
Select an answer first
5foundation · easy
What is the defining characteristic of a correlated subquery in T-SQL?
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.