SQL - Structured Query Language Common Table Expressions (CTEs) Questions and Answers

Question 1

Which of the following best describes the scope and purpose of a Common Table Expression (CTE)?

Accepted Answer

A named temporary result set that exists only for the duration of a single SQL statement (SELECT, INSERT, UPDATE, or DELETE).

Answer

A Common Table Expression (CTE) is defined using the WITH clause and creates a temporary, named result set. Its scope is limited to the single statement that immediately follows it, after which it is discarded. It is not a permanent object like a view, nor does it persist for an entire session like a temporary table.

Question 2

A developer needs to query an `Employees` table to find all employees who are part of a specific manager's organizational hierarchy (i.e., their direct and indirect reports). Which type of CTE is best suited for this task?

Accepted Answer

A recursive CTE

Answer

Recursive CTEs are specifically designed to handle hierarchical or graph-like data structures, such as organizational charts or parts explosions. A recursive CTE starts with a base case (the 'anchor member', e.g., the manager) and then iteratively references itself to traverse the hierarchy (the 'recursive member', e.g., finding employees who report to the people already in the result set) until the entire hierarchy is returned.

Question 3

What is the correct syntax for defining two separate, non-dependent Common Table Expressions, `HighValueCustomers` and `RecentOrders`, in a single query?

Accepted Answer

WITH HighValueCustomers AS (...), RecentOrders AS (...)
SELECT ...

Answer

To define multiple CTEs in a single query, you use the `WITH` keyword only once at the very beginning. Each subsequent CTE definition is separated by a comma. The final CTE is not followed by a comma before the main `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement begins.

Question 4

In a recursive CTE, what is the role of the 'anchor member'?

Accepted Answer

It is the non-recursive part of the query that provides the initial or base result set for the recursion to begin.

Answer

The anchor member is the initial query in a recursive CTE. It runs only once and does not reference the CTE itself. It establishes the base result set (T0), which is then used as the input for the first iteration of the recursive member. The recursion continues until the recursive member returns an empty set.

Question 5

A data analyst is writing a complex report. They first need to calculate total sales per region, and then, using that result, calculate the average sale amount for regions exceeding $1,000,000 in total sales. Why would a CTE be a good choice for this scenario?

Accepted Answer

Because CTEs improve readability and modularity by breaking the complex problem into logical, named steps.

Answer

CTEs are highly valued for their ability to improve the readability and maintainability of complex SQL queries. By allowing the analyst to define the 'total sales per region' calculation in a named CTE, the main query can then simply refer to that name, making the logic for the second step (calculating the average) much clearer and easier to understand.

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language Common Table Expressions (CTEs) Questions and Answers