SQL Common Table Expressions (CTEs) 2 — Questions and Answers
Question 1: Which keyword introduces a Common Table Expression in standard SQL?
- WITH (Correct answer)
- USING
- DECLARE
- DEFINE
Correct answer: WITH
A CTE is defined using the WITH keyword followed by the CTE name and its query.
Question 2: How long does a non-recursive CTE persist?
- Only for the single statement it is attached to (Correct answer)
- Until the session ends
- Until the transaction commits
- Permanently in the database
Correct answer: Only for the single statement it is attached to
A CTE exists only for the duration of the single query in which it is defined.
Question 3: What separates multiple CTEs defined in the same WITH clause?
- A comma (Correct answer)
- A semicolon
- The AND keyword
- A new WITH keyword
Correct answer: A comma
Multiple CTEs are chained in one WITH clause, separated by commas.
Question 4: Can a later CTE in the same WITH clause reference an earlier CTE?
- Yes, earlier CTEs are visible to later ones (Correct answer)
- No, CTEs are fully isolated
- Only if both are recursive
- Only in subqueries
Correct answer: Yes, earlier CTEs are visible to later ones
CTEs are evaluated in order, so a later CTE can reference any CTE defined before it.
Question 5: Which statement type can a CTE precede in many databases like PostgreSQL?
- SELECT, INSERT, UPDATE, and DELETE (Correct answer)
- Only SELECT
- Only INSERT
- Only DDL statements
Correct answer: SELECT, INSERT, UPDATE, and DELETE
In PostgreSQL, a WITH clause can prefix SELECT, INSERT, UPDATE, or DELETE statements.
Question 6: What is a primary readability benefit of using a CTE over a nested subquery?
- It names a query block so it can be referenced clearly (Correct answer)
- It always runs faster
- It permanently stores results
- It bypasses indexes
Correct answer: It names a query block so it can be referenced clearly
A CTE assigns a readable name to a query block, improving clarity over deeply nested subqueries.
Question 7: In the syntax WITH cte_name AS (...), what does cte_name represent?
- The temporary result set name used later in the query (Correct answer)
- A permanent table name
- A column alias
- A schema name
Correct answer: The temporary result set name used later in the query
cte_name is the identifier you use to reference the CTE's result set in the main query.
SQL Common Table Expressions (CTEs) 2
Which keyword introduces a Common Table Expression in standard SQL?