A team needs to provide read-only access to a subset of columns from a set of tables. The access must be granted to a group of users, and the users must be able to write their own queries. The underlying tables are large, and performance is a concern. You need to balance security, flexibility, and performance. What should you do?

Create a view with only the allowed columns and grant SELECT on the view

Create a stored procedure that returns the allowed columns and grant EXECUTE

Create a table-valued function that returns the allowed columns

Create a trigger that copies allowed columns to a separate table

A university database has a table Enrollments with columns StudentID, CourseID, and Grade. Users need to retrieve, for a given course, the list of students and their grades, and also the average grade for that course. You want to create a single reusable object that returns both the detail rows and the average. What should you create?

Create a stored procedure that returns two result sets: the detail rows and the average.

Create a view that includes the average grade as a column in each row.

Create a table-valued function that returns the student grades, and a separate scalar function for the average.

Create a scalar function that returns the average grade only.

A sales application needs to display a customer's full name as 'LastName, FirstName' in multiple reports. The formatting logic is used in many queries, and you want to ensure consistency and avoid repeating the expression. You also need to allow the logic to be used in SELECT lists and WHERE clauses. What should you create?

A stored procedure that returns the formatted name

A table-valued function that returns a single row with the formatted name

A view that includes the formatted name column

A scalar function that takes first and last name and returns the formatted string

A database has a table that is updated by a stored procedure and also by direct UPDATE statements from a legacy application. A business rule requires that a specific column must be set to a calculated value on every update. The calculation is complex and used in other parts of the system. You need to enforce the rule consistently and avoid duplicating the calculation logic. What should you do?

Create a scalar function and have the legacy application call it before updating

Create an AFTER UPDATE trigger that calls a scalar function to calculate the value and updates the column

Create a view that calculates the value and use it for all updates

Modify the stored procedure to calculate the value and update the column

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 2

Implement Programmability Objects DP-800 Practice Questions (Page 5)

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 32 practice questions to prepare you well beyond it. (estimate)

32questions here

7free pages

5concepts

35–40%of the exam

Questions 21–25

21expert · hard
A team needs to provide read-only access to a subset of columns from a set of tables. The access must be granted to a group of users, and the users must be able to write their own queries. The underlying tables are large, and performance is a concern. You need to balance security, flexibility, and performance. What should you do?
Select an answer first
22application · medium
A university database has a table Enrollments with columns StudentID, CourseID, and Grade. Users need to retrieve, for a given course, the list of students and their grades, and also the average grade for that course. You want to create a single reusable object that returns both the detail rows and the average. What should you create?
Select an answer first
23application · medium
A sales application needs to display a customer's full name as 'LastName, FirstName' in multiple reports. The formatting logic is used in many queries, and you want to ensure consistency and avoid repeating the expression. You also need to allow the logic to be used in SELECT lists and WHERE clauses. What should you create?
Select an answer first
24foundation · easy
A developer needs to create a function that accepts a category ID and returns a result set containing all products in that category. Which type of function should the developer create?
Select an answer first
25expert · hard
A database has a table that is updated by a stored procedure and also by direct UPDATE statements from a legacy application. A business rule requires that a specific column must be set to a calculated value on every update. The calculation is complex and used in other parts of the system. You need to enforce the rule consistently and avoid duplicating the calculation logic. What should you do?
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.

Implement Programmability Objects DP-800 Practice Questions (Page 5)

Questions 21–25

A developer needs to create a function that accepts a category ID and returns a result set containing all products in that category. Which type of function should the developer create?

Keep studying — Design and develop database solutions