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

Questions 11–15

1. 11expert · hard

   A reporting application needs to display a list of products with their prices and a calculated tax amount. The tax rate depends on the product category and changes infrequently. The application currently queries a view that joins the Products and Categories tables. You need to add the tax calculation without changing the application code. The calculation must be easy to update when the tax rate changes. What should you do?

   • ACreate a stored procedure that returns the products with the tax amount and change the application to execute it.
   • BCreate a table-valued function that returns the products with the tax amount and change the application to call it.
   • CCreate a trigger that updates a TaxAmount column in the Products table whenever the price changes.
   • DModify the view to include the tax amount using a scalar function that encapsulates the tax logic.
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   A library database has a table Loans with columns BookID, MemberID, and DueDate. Librarians need to see a list of overdue books for a given member, including the book title and the number of days overdue. The list must be generated by passing a member ID. The result will be used in a report that joins with other tables. What should you create?

   • ACreate a view that filters for overdue loans for a fixed member.
   • BCreate a table-valued function that accepts a member ID and returns the overdue loans with the computed days overdue.
   • CCreate a scalar function that returns the number of days overdue for a loan.
   • DCreate a stored procedure that returns the overdue loans for a member.
   Reveal Solution Discussion Select an answer first
4. 13expert · hard

   A database has a table that is updated by multiple applications. A business rule requires that whenever a row is updated, a history record must be created. The rule must be enforced even if an application bypasses a stored procedure and performs a direct UPDATE. You need to ensure the history is always recorded. What should you do?

   • ACreate a stored procedure that updates the table and inserts the history record
   • BCreate an AFTER UPDATE trigger on the table
   • CCreate a scalar function that updates the history record
   • DCreate a view that updates the table and inserts the history record
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   Which statement correctly describes a benefit of using stored procedures?

   • AStored procedures always return a single scalar value.
   • BStored procedures can reduce network traffic by executing multiple statements on the server in a single call.
   • CStored procedures can be used in a FROM clause as a data source.
   • DStored procedures are automatically executed when data is inserted into a table.
   Reveal Solution Discussion Select an answer first
6. 15application · medium

   An order management system has a stored procedure that inserts a new order and then updates the inventory. The procedure currently uses a scalar function to calculate the order total. You need to add a business rule that prevents an order from being inserted if the customer's credit limit is exceeded. The rule must be enforced even if the order is inserted through a different application or a direct INSERT. What should you do?

   • AAdd the credit check logic inside the stored procedure before the INSERT.
   • BCreate a scalar function that checks the credit limit and call it from the application code.
   • CCreate an AFTER INSERT trigger on the Orders table that checks the credit limit and rolls back the transaction if exceeded.
   • DCreate a view that filters out orders exceeding the credit limit.
   Reveal Solution Discussion Select an answer first