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

Questions 16–20

1. 16foundation · easy

   Which T-SQL function is used to parse JSON text and return the data as rows and columns?

   • AOPENJSON
   • BJSON_QUERY
   • CJSON_OBJECT
   • DJSON_MODIFY
   Reveal Solution Discussion Select an answer first
2. 17application · medium

   A data cleaning process needs to identify potentially duplicate customer records where names are spelled slightly differently (e.g., 'Jon Smith' vs 'John Smith'). The process should flag pairs with a similarity score above a certain threshold. Which T-SQL function should you use to compute the similarity?

   • AUse JARO_WINKLER_DISTANCE to get a similarity score between 0 and 1, where higher values indicate more similar strings.
   • BUse SOUNDEX to compare the phonetic representation of the names.
   • CUse REGEXP_LIKE to check if the names match a pattern that allows for one character difference.
   • DUse EDIT_DISTANCE to compute the number of character edits needed to transform one name into the other.
   Reveal Solution Discussion Select an answer first
4. 18foundation · easy

   Which T-SQL function is used to calculate the edit distance between two strings?

   • ADIFFERENCE
   • BSOUNDEX
   • CEDIT_DISTANCE
   • DJARO_WINKLER_DISTANCE
   Reveal Solution Discussion Select an answer first
5. 19application · medium

   A sales analytics team needs a query that returns, for each product, the total sales per month and the running total of sales across months, ordered by month. They want to avoid repeating the aggregation subquery. Which T-SQL construct should you use?

   • AA single query with GROUP BY product, month and no window function
   • BA CTE that aggregates sales by product and month, then a window function with SUM() OVER (PARTITION BY product ORDER BY month) on the CTE
   • CA temporary table populated by an INSERT...EXEC, then a second query with a self-join on month
   • DA correlated subquery in the SELECT list that sums all sales for the product up to the current month
   Reveal Solution Discussion Select an answer first
6. 20expert · hard

   A data ingestion pipeline receives JSON files that sometimes contain malformed JSON. You need to load valid records into a table and log any rows that fail to parse, without stopping the entire batch. Which approach should you use?

   • AUse a cursor to loop through each row and use RAISERROR to log errors
   • BLoad all rows into a staging table as NVARCHAR, then use ISJSON() to filter valid rows and insert them, while logging invalid rows
   • CUse OPENJSON with a TRY...CATCH block per row to catch parsing errors and log them
   • DUse JSON_QUERY to extract the data and ignore errors
   Reveal Solution Discussion Select an answer first