Module assessment

1.

You need to write a query that calculates a running total of sales ordered by date, while still showing individual order details. Which T-SQL approach should you use?

A correlated subquery that sums all orders with a date less than or equal to the current row's date

A recursive CTE that accumulates totals by iterating through each date

An aggregate function with OVER (ORDER BY) clause to create a window function

A self-join of the table where all rows with earlier dates are joined and summed

2.

You're building a JSON response from relational data and need to include an array of related order items for each customer. Which combination of functions creates nested JSON with arrays from grouped rows?

FOR JSON PATH with nested subqueries

JSON_OBJECT combined with JSON_ARRAYAGG to aggregate rows into arrays

OPENJSON with a WITH clause to define the output schema

JSON_VALUE and JSON_QUERY to extract and combine elements

3.

You need to find customer records where names might be misspelled, such as finding 'John Smith' when the database contains 'Jon Smyth'. Which approach provides the best results for name matching?

Use LIKE with wildcards such as WHERE Name LIKE '%ohn%'

Use SOUNDEX to compare phonetic representations

Use JARO_WINKLER_DISTANCE which is optimized for name comparison and considers prefix matching

Use REGEXP_LIKE to create a pattern matching all possible misspelling variations

4.

You're writing a stored procedure that updates multiple tables within a transaction. An error occurs after the first table is updated. What's the correct pattern to ensure data consistency?

Use SET XACT_ABORT ON only, which automatically rolls back on any error

Use TRY...CATCH with IF @@TRANCOUNT > 0 ROLLBACK in the CATCH block, then re-raise with THROW

Check @@ERROR after each statement and call ROLLBACK if non-zero

Use multiple nested transactions with SAVE TRANSACTION points for each table update

5.

You need to traverse an organizational hierarchy to find all employees who report to a manager at any level, not just direct reports. Which approach best handles variable-depth hierarchical traversal?

Multiple self-joins with one join per possible level in the hierarchy

A recursive CTE that traverses the hierarchy by joining on the manager-employee relationship

A window function with PARTITION BY on the manager column

A correlated subquery that finds managers for each employee

Feedback