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 temporary, named result set stored in memory or on disk that persists for the entire user session.

Answer

A permanent database object, similar to a view, that is used to simplify complex queries.

Answer

A special type of subquery that can only be used in the WHERE clause to filter results.

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

A nested CTE

Answer

A standard CTE with multiple joins

Answer

A sequential CTE

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

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

Answer

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

Answer

DEFINE CTE HighValueCustomers AS (...), RecentOrders AS (...)
SELECT ...

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

It is the part of the CTE that references itself to perform the iterative processing.

Answer

It is the final SELECT statement that queries the results of the CTE.

Answer

It is a termination condition that stops the CTE from entering an infinite loop.

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

Because CTEs are the only way to perform aggregate functions like SUM() and AVG().

Answer

Because CTEs are indexed automatically, making the multi-step calculation faster than any other method.

Answer

Because CTEs store their results permanently, allowing the analyst to query them later without rerunning the calculation.

Question 6

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

Accepted Answer

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

Answer

A CTE can be referenced multiple times within the same query.

Answer

A CTE can be defined within another CTE (nested CTE).

Answer

A single `WITH` clause can be used to define multiple CTEs, separated by commas.