Common Table Expressions ( CTE )

Question 1

What is the primary purpose of a **CTE** in SQL?

A.

A) To permanently store data in a new table

B.

B) To create a reusable, temporary virtual table for the main query

Explanation: A **CTE** uses the **WITH** clause to define a named subquery that acts as a lightweight, temporary table, improving readability and reusability.

C.

C) To replace the **FROM** clause entirely

D.

D) To execute multiple independent queries simultaneously

Question 2

Which of the following best describes the primary advantage of using a CTE over a subquery?**

A.

A) CTEs execute faster than subqueries in all database systems.

B.

B) CTEs do not require a FROM clause in the main query.

C.

C) CTEs can be named and referenced multiple times within a single query, improving readability and reusability.

Explanation: CTEs provide a named reference to query results that can be reused multiple times in a single query, whereas subqueries must be rewritten each time. This improves code clarity and maintainability, though execution speed depends on the database optimizer.

D.

D) CTEs automatically eliminate duplicate rows without using DISTINCT.

Question 3

In the context of CTEs, what does a **natural join** accomplish when joining a CTE to a main table?**

A.

A) It automatically selects only columns from the CTE, excluding main table columns.

B.

B) It matches rows based on foreign key relationships defined in the schema.

C.

C) It performs an inner join on all columns with identical names.

Explanation: A natural join matches rows on columns that have the same name in both tables. For example, joining a CTE with a `CustomerID` column to the `Orders` table uses the shared `CustomerID` column without requiring an explicit ON clause.

D.

D) It requires explicit ON conditions to function properly.

Question 4

What operator connects the anchor and recursive parts?

A.

A) UNION

B.

B) UNION ALL

Explanation: `UNION ALL` combines the anchor seed with recursive results without deduplication.

C.

C) INTERSECT

D.

D) EXCEPT

Question 5

Which clause is required to define a recursive CTE?

A.

A) WITH DISTINCT

B.

B) WITH RECURSIVE

Explanation: Recursive CTEs use **WITH RECURSIVE** to enable self-referencing queries.

C.

C) WITH UNION

D.

D) WITH WHERE

📝 Common Table Expressions ( CTE )

Quiz Complete!