Common Table Expressions (CTEs) Flashcards

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

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

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.

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?

Answer: A recursive CTE

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.

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

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

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.

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

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

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.

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?

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

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.

Which of the following statements about Common Table Expressions (CTEs) is FALSE?

Answer: A CTE improves query performance by creating a physical, indexed temporary table.

While CTEs can improve query readability and sometimes allow the optimizer to create a more efficient plan, they do not inherently improve performance by creating physical, indexed tables. The result set of a CTE is not typically materialized (stored) or indexed like a temporary table would be. Its main benefit is simplifying complex logic.