Chapter 11 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. A CTE is introduced with the keyword: - A) USING - B) WITH - C) HAVING - D) DECLARE

Q2. The main benefit of a (non-recursive) CTE over a nested subquery is: - A) It's always faster - B) Readability — naming steps in a top-to-bottom pipeline - C) It removes duplicates - D) It enables joins

Q3. Only a CTE (not a subquery) can do which of these? - A) Aggregate - B) Join - C) Recurse - D) Filter

Q4. A recursive CTE requires the keyword: - A) WITH RECURSIVE - B) LOOP - C) WITH CYCLE - D) RECURSE

Q5. The three parts of a recursive CTE are: - A) SELECT, FROM, WHERE - B) Anchor (base case), UNION ALL, recursive term - C) Anchor, JOIN, GROUP BY - D) WITH, HAVING, ORDER BY

Q6. The recursive term must: - A) Reference the CTE's own name - B) Use GROUP BY - C) Avoid joins - D) Return one row

Q7. A recursive CTE stops when: - A) After exactly 100 iterations - B) A pass produces no new rows - C) The first row is found - D) It never stops

Q8. To list a node's depth/level in a tree, you typically: - A) Use COUNT(*) - B) Carry a counter incremented in the recursive term - C) Sort the rows - D) Use DISTINCT

Q9. A data cycle (A→B→A) in a recursive CTE causes: - A) An error immediately - B) Potential infinite recursion (guard with CYCLE or a depth limit) - C) Duplicate removal - D) Nothing

Q10. Since PostgreSQL 12, simple single-use CTEs are: - A) Always materialized (an optimization fence) - B) Inlined/optimized with the outer query by default - C) Not allowed - D) Converted to temp tables

True/False

Q11. Recursive CTEs typically use UNION ALL, not UNION. (True / False)

Q12. A self-join can traverse a hierarchy to arbitrary depth, so recursive CTEs are unnecessary. (True / False)

Short answer

Q13. Explain the difference between recursing "down" (to descendants) and "up" (to ancestors) in a tree, in terms of what the anchor selects and how the recursive term joins.

---

Answer key

Q1 — B. WITH.

Q2 — B. Readability: named, reusable, step-by-step.

Q3 — C. Recursion is unique to CTEs (WITH RECURSIVE).

Q4 — A. WITH RECURSIVE.

Q5 — B. Anchor + UNION ALL + recursive term.

Q6 — A. It must reference the CTE itself — that's what makes it recurse.

Q7 — B. Termination is when a pass adds no new rows.

Q8 — B. Increment a level counter in the recursive term.

Q9 — B. Cycles risk infinite recursion; use the CYCLE clause or a depth cap.

Q10 — B. Inlined by default since v12; MATERIALIZED/NOT MATERIALIZED override.

Q11 — True. UNION ALL is standard; UNION dedupes each pass (slower, occasionally desired).

Q12 — False. A self-join reaches a fixed number of levels (one per join). Arbitrary/unknown depth needs recursion.

Q13. Recursing down: the anchor selects the root(s) (e.g., parent_id IS NULL or a chosen node), and the recursive term joins children to the CTE (child.parent_id = cte.id), expanding to descendants. Recursing up: the anchor selects the target node, and the recursive term joins to the parent (parent.id = cte.parent_id), walking toward the root. The direction is set by which side of the parent/child relationship the join follows.

Scoring: 11–13 you've mastered a rare skill; 8–10 re-read the recursive structure; below 8, redo Exercises C–D.