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

Questions 26–30

1. 26application · medium

   A reporting query needs to show, for each salesperson, the total sales for the current year and the difference from the previous year's total. The data is in a Sales table with columns SalespersonID, SaleDate, and Amount. Which query approach should you use?

   • AUse a CTE to aggregate sales by salesperson and year, then use LAG() to get the previous year's total
   • BUse a correlated subquery to sum sales for the previous year for each row
   • CUse a self-join on the Sales table to match current and previous year records
   • DUse ROW_NUMBER() to rank salespeople by total sales and then compare ranks
   Reveal Solution Discussion Select an answer first
2. 27foundation · easy

   Which T-SQL construct is used to catch and handle runtime errors in a batch of T-SQL code?

   • ATRY...CATCH
   • BBEGIN...END
   • CWHILE
   • DIF...ELSE
   Reveal Solution Discussion Select an answer first
4. 28application · medium

   You are writing a stored procedure that inserts multiple rows into a table. If any insert fails due to a constraint violation, you want to log the error and continue with the next row, rather than aborting the entire transaction. Which error handling approach should you use?

   • AUse RAISERROR before each insert to check for potential violations.
   • BUse a single TRY...CATCH block around the entire batch of inserts.
   • CUse THROW after each insert to re-raise any error that occurs.
   • DWrap each insert in its own TRY...CATCH block and continue after the CATCH.
   Reveal Solution Discussion Select an answer first
5. 29foundation · easy

   Which type of table is used to store relationships between nodes in a SQL Server graph database?

   • AEdge table
   • BTemporary table
   • CExternal table
   • DNode table
   Reveal Solution Discussion Select an answer first
6. 30expert · hard

   A customer database has a column FullName that contains names with inconsistent formatting, such as extra spaces, hyphens, and apostrophes. You need to normalize the names by removing all characters that are not letters, spaces, hyphens, or apostrophes, and also identify names that are likely duplicates using a similarity score. Which combination of functions should you use?

   • AREGEXP_REPLACE to remove unwanted characters and EDIT_DISTANCE to compare normalized names
   • BREGEXP_LIKE to test for valid names and JARO_WINKLER_DISTANCE to compare
   • CREPLACE to remove spaces and SOUNDEX to compare phonetically
   • DCHARINDEX and SUBSTRING to extract parts and then use a self-join
   Reveal Solution Discussion Select an answer first