SQL Quiz: Recursive Ctes
10 questions · exam conditions
0:00
Recursive CtesQuestion 1 of 10

A directed edges(parent, child) table contains (1, 2), (2, 3), and (3, 1). Consider: WITH RECURSIVE reach(node) AS (SELECT 1 UNION SELECT e.child FROM edges e JOIN reach r ON e.parent = r.node) SELECT node FROM reach; Assume UNION removes duplicate rows across the recursive result.

What happens when the query is executed?

It terminates with nodes 1, 2, and 3, because revisiting node 1 adds no new row.
It recurses indefinitely, because each edge traversal produces a row even when that node was already found.
It terminates with nodes 1 and 2, because the cycle causes node 3 and later rows to be discarded.
It terminates only after a depth column is added, because UNION cannot suppress rows produced in later iterations.
← Back to quizzes

SQL Quiz

SQL Quiz: Recursive Ctes

Practice Recursive Ctes in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Recursive Ctes, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A directed edges(parent, child) table contains (1, 2), (2, 3), and (3, 1). Consider: WITH RECURSIVE reach(node) AS (SELECT 1 UNION SELECT e.child FROM edges e JOIN reach r ON e.parent = r.node) SELECT node FROM reach; Assume UNION removes duplicate rows across the recursive result.

What happens when the query is executed?

  1. It terminates with nodes 1, 2, and 3, because revisiting node 1 adds no new row. (correct answer)
  2. It recurses indefinitely, because each edge traversal produces a row even when that node was already found.
  3. It terminates with nodes 1 and 2, because the cycle causes node 3 and later rows to be discarded.
  4. It terminates only after a depth column is added, because UNION cannot suppress rows produced in later iterations.
Explanation: When you see a recursive CTE with UNION (not UNION ALL), the critical question is: how does SQL prevent infinite loops? The answer lies in duplicate elimination. UNION removes rows that already exist in the accumulated result set, so if a node is re-encountered, it produces no new row — and recursion stops when no new rows are produced. Tracing through the query: the base case seeds reach with node 1. The first recursive step finds 1 → 2, adding node 2. The second finds 2 → 3, adding node 3. The third step finds 3 → 1, but 1 already exists in reach — UNION suppresses it as a duplicate. No new row is inserted, the working table is empty, and recursion halts. The final result is {1, 2, 3}, making A correct. B describes what would happen with UNION ALL, which keeps duplicates. With UNION ALL, the cycle 1 → 2 → 3 → 1 → ... would indeed recurse forever (or until a depth limit is hit). The question explicitly states UNION removes duplicates, so B is based on a false premise. C is wrong because UNION doesn't discard rows mid-traversal based on cycles — it deduplicates after each iteration. Node 3 is a genuinely new value and gets added normally. D is a distractor suggesting UNION can't suppress later iterations. This is incorrect — UNION compares each candidate row against the entire accumulated result, regardless of which iteration produced it. Study tip: Always distinguish UNION from UNION ALL in recursive CTEs — this distinction alone determines whether a cycle causes infinite recursion or graceful termination.

Question 2

An employees(employee_id, manager_id) table contains (1, NULL), (2, 1), (3, 99), (4, 3), and (5, NULL). There is no employee 99. A recursive CTE anchors on manager_id IS NULL and repeatedly joins employees whose manager_id equals an employee already in the CTE.

Ignoring row order, which employee IDs will the CTE contain?

  1. 1, 2, 5; employees 3 and 4 are disconnected from every anchored root. (correct answer)
  2. 1, 2, 3, 4, 5; every employee eventually enters during recursive evaluation.
  3. 1, 2, 4, 5; employee 4 is included as a descendant even though employee 3 is omitted.
  4. 1, 5; only rows produced by the anchor remain when multiple roots are present.
Explanation: When working with recursive CTEs, the key question is always: where does the traversal begin, and what can it actually reach? The anchor clause defines your starting roots, and each recursive step can only extend from rows already in the result set — it cannot "discover" disconnected nodes on its own. Here, the anchor selects employees where manager_id IS NULL, giving you employees 1 and 5. The recursive step then finds any employee whose manager_id matches someone already collected. Employee 2 has manager_id = 1, so it joins in next. No remaining employee has manager_id equal to 2 or 5, so recursion terminates. The final set is {1, 2, 5} — making A correct. The trap in B is assuming the CTE performs a global scan and includes everyone. It doesn't — recursion only propagates along valid edges. Employee 3 has manager_id = 99, but employee 99 doesn't exist anywhere in the table, so 3 is never reachable from any anchor root. Because 3 never enters the CTE, employee 4 (whose manager is 3) also has no path in — eliminating C, which incorrectly assumes 4 can be included while 3 is skipped. You can't traverse through a node that was never collected. D is wrong because multiple NULL-rooted anchors are perfectly valid; a recursive CTE unions all anchor rows together before recursing, so both 1 and 5 are included from the start. Your study tip: always mentally trace the path from anchor to each node. If any link in the chain is broken — missing employee, wrong manager reference — the entire subtree below that break is unreachable.

Question 3

A recursive CTE traverses a hierarchy and carries a depth column. During testing, one database happens to display all depth-0 rows first, then all depth-1 rows, and then all depth-2 rows. The final query has no ORDER BY clause.

Which conclusion is valid when the query is moved to another conforming database system?

  1. The same breadth-first order is required because recursive iterations define final result ordering.
  2. The rows must switch to depth-first order because recursive CTEs use stack-based traversal.
  3. No traversal order is guaranteed; an explicit final ORDER BY is needed for stable presentation. (correct answer)
  4. Anchor rows are guaranteed first, but rows from recursive iterations may appear in any order afterward.
Explanation: Whenever you see a question about recursive CTEs and result ordering, anchor yourself to one foundational SQL rule: the SQL standard makes no guarantee about the physical order of rows returned unless an explicit ORDER BY clause is present. This applies to regular queries, CTEs, and recursive CTEs alike. The observed breadth-first pattern in the first database is purely incidental — it reflects that system's internal implementation details (perhaps how it queues iteration results), not any requirement of the SQL standard. When you move the same query to a different conforming database, that engine is free to return rows in whatever order its optimizer chooses. Adding a depth column tracks which level a row came from, but it does nothing to enforce how those rows are sorted in the final result set. C is correct: without an explicit ORDER BY depth (or whatever column you care about), output order is undefined and unstable across systems. A is wrong because recursive iterations do not define final result ordering — SQL engines are free to materialize and reorder intermediate results however they like. The iteration logic is deterministic, but the output sequence is not guaranteed. B is wrong on two fronts: recursive CTEs don't universally use stack-based traversal, and even if one implementation did, the SQL standard doesn't mandate depth-first ordering. You're mixing implementation details with language guarantees. D is a tempting half-truth. Even anchor rows have no guaranteed position in the final result without an ORDER BY; the standard doesn't promise they appear first. Study tip: Any time a SQL question mentions ordering without an explicit ORDER BY, the answer almost always hinges on "no ordering is guaranteed." Treat apparent ordering as coincidental, never contractual.

Question 4

Assume recursive CTEs are fully evaluated before an outer query filter is applied and that the database does not automatically detect cycles. Consider: WITH RECURSIVE walk(node, depth) AS (SELECT 1, 0 UNION ALL SELECT CASE WHEN node = 1 THEN 2 ELSE 1 END, depth + 1 FROM walk) SELECT node FROM walk WHERE depth <= 3;

What is the most accurate description of the query's behavior?

  1. It returns four alternating rows because the outer WHERE clause stops recursion after depth 3.
  2. It returns two rows because UNION ALL retains only the first occurrence of each node.
  3. It returns eight rows because each of the four permitted depths generates both node values.
  4. It continues recursing beyond depth 3, so it does not complete unless the system imposes a limit. (correct answer)
Explanation: When working with recursive CTEs, you need to understand where filtering happens versus where recursion stops. These are two completely different things — and confusing them is exactly what this question tests. The recursive CTE here alternates between nodes 1 and 2 indefinitely: depth 0 returns node 1, depth 1 returns node 2, depth 2 returns node 1, and so on forever. The critical insight is that the WHERE depth <= 3 clause belongs to the outer query, not to the recursive definition itself. The passage explicitly states that recursive CTEs are fully evaluated before the outer filter applies — and the database doesn't detect cycles. This means the recursion has no internal termination condition and will keep generating rows without bound. The outer WHERE clause never gets a chance to stop it. D is correct: the query recurses indefinitely until the system intervenes (typically via a recursion depth limit or timeout). A is the most tempting wrong answer — it assumes the outer WHERE acts as a recursion stopper, which it does not. The filter runs after recursion completes, but recursion never completes here. B is wrong because UNION ALL explicitly keeps all rows including duplicates; it does not deduplicate like UNION. C is wrong for the same reason as A — it assumes four depths are generated and returned cleanly, but again, the outer WHERE cannot halt an infinite recursive loop. The key study takeaway: to safely limit recursion, the termination condition must live inside the recursive part of the CTE (e.g., WHERE depth < 3 in the recursive branch), not in the outer query.

Question 5

A directed edges(parent, child) table contains ('A', 'B'), ('A', 'C'), ('B', 'D'), and ('C', 'D'). Consider: WITH RECURSIVE reach(node) AS (SELECT 'A' UNION ALL SELECT e.child FROM edges e JOIN reach r ON e.parent = r.node) SELECT COUNT(*) FROM reach WHERE node = 'D';

What count does the query return?

  1. 0, because recursive CTEs return only nodes reached directly from the anchor.
  2. 1, because a reachable node is represented once regardless of the number of paths.
  3. 2, because D is generated once through B and once through C. (correct answer)
  4. 4, because both paths contribute their intermediate and final nodes to the count.
Explanation: When working with recursive CTEs, you need to trace the execution step by step rather than assuming SQL automatically deduplicates results. A recursive CTE generates rows in successive iterations — the anchor produces the starting set, and each recursive step appends new rows based on joins against the previous iteration's output. Here's how this query executes: The anchor selects 'A', giving reach = {A}. Iteration 1 joins edges where parent = 'A', producing B and C. Iteration 2 joins edges where parent IN ('B', 'C') — both B→D and C→D match, so two rows of 'D' are added. Iteration 3 finds no new children of D, so recursion stops. The final reach table contains A, B, C, D, D — five rows total, with D appearing twice. The WHERE node = 'D' filter returns a count of 2, making C correct. A is wrong because recursive CTEs absolutely do traverse multiple hops — that's their entire purpose. The anchor is just the starting point, not the limit of traversal. B reflects what would happen if you used UNION instead of UNION ALL. Using UNION would deduplicate rows and yield a count of 1, but UNION ALL preserves every generated row, including duplicates. D is a miscount — the intermediate nodes A, B, and C are not counted because the filter isolates only D, and D appears exactly twice. A key pattern to remember: UNION ALL in a recursive CTE means no deduplication. If you want distinct reachable nodes, you must either use UNION or wrap the result in SELECT DISTINCT.

Question 6

An employees table contains (employee_id, manager_id) rows (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), and (6, 3). The following CTE is executed: WITH RECURSIVE org(employee_id) AS (SELECT employee_id FROM employees WHERE employee_id = 2 UNION ALL SELECT e.employee_id FROM employees e JOIN org o ON e.manager_id = o.employee_id) SELECT employee_id FROM org;

Ignoring row order, which employee IDs are returned?

  1. 1, 2, representing the selected employee and that employee's manager.
  2. 2, 4, 5, representing the selected employee and that employee's descendants. (correct answer)
  3. 2, 3, 4, 5, representing employees at the selected level and the next level.
  4. 1, 2, 4, 5, representing the selected branch including its top-level manager.
Explanation: Recursive CTEs follow a two-part pattern: an anchor (the starting row) and a recursive member (the repeated join that expands results). The key to tracing them is understanding the direction of traversal — are you walking up the hierarchy (toward managers) or down (toward subordinates)? In this query, the anchor selects employee_id = 2. The recursive member then joins employees to org on e.manager_id = o.employee_id — meaning it finds employees whose manager is already in the result set. This travels downward through the hierarchy. Starting with {2}, the next iteration finds employees whose manager is 2: that's 4 and 5. The iteration after that looks for employees whose manager is 4 or 5 — none exist. The recursion stops, returning 2, 4, 5, confirming B is correct. A is wrong because the join condition e.manager_id = o.employee_id fetches children, not parents. To retrieve manager 1, you'd need to reverse the join direction. C incorrectly includes employee 3, who reports to manager 1 — not to employee 2. Employee 3 is a sibling of 2, not a descendant. D combines the mistakes of A and C, adding both the parent (1) and sibling-branch descendant (3) while correctly including 4 and 5 — a mix of upward and sideways traversal that the query simply doesn't perform. When you encounter a recursive CTE question, immediately identify the join condition and ask: which column is the "seed" side and which is the "expansion" side? That tells you the direction of traversal and prevents confusing parent-walking with child-walking.

Question 7

Employees 10, 20, and 30 are named CEO, Manager, and Engineer. Employee 20 reports to 10, and employee 30 reports to 20. A CTE starts at employee 30 and walks upward: WITH RECURSIVE chain(id, manager_id, path) AS (SELECT id, manager_id, name FROM employees WHERE id = 30 UNION ALL SELECT m.id, m.manager_id, m.name || ' > ' || c.path FROM employees m JOIN chain c ON m.id = c.manager_id) SELECT path FROM chain WHERE manager_id IS NULL;

Which path is selected from the final CTE row?

  1. Engineer > Manager > CEO, because each newly found manager is appended to the existing path.
  2. Manager > CEO, because the anchor employee is replaced during the first recursive iteration.
  3. CEO > Engineer, because only the root and original anchor remain in the completed path.
  4. CEO > Manager > Engineer, because each newly found manager is prepended to the existing path. (correct answer)
Explanation: When working with recursive CTEs, the key is to trace exactly how the path string gets built in each iteration — specifically, which side of the || concatenation gets the new value versus the accumulated value. The anchor row starts at employee 30 (Engineer), so path = 'Engineer' and manager_id = 20. In the first recursive step, the query finds employee 20 (Manager) because m.id = c.manager_id. It then builds: m.name || ' > ' || c.path, which is 'Manager' || ' > ' || 'Engineer' = 'Manager > Engineer'. In the second recursive step, employee 10 (CEO) is found, producing 'CEO' || ' > ' || 'Manager > Engineer' = 'CEO > Manager > Engineer'. This row has manager_id IS NULL, so it's selected — confirming D is correct. A is wrong because it describes appending (putting the new name after the existing path), which would produce Engineer > Manager > CEO. That would require c.path || ' > ' || m.name, but the query does the opposite. B is wrong because recursive CTEs don't replace anchor rows — each iteration adds new rows to the result set while referencing previous ones. The anchor row is never removed. C is wrong on two counts: it skips the middle employee entirely and reverses the order, neither of which matches the concatenation logic. As a study tip: whenever you see recursive CTE path-building, sketch out each iteration row by row and pay close attention to which operand is on the left of || — that determines whether names are prepended or appended.

Question 8

A tree has a root, children, grandchildren, and great-grandchildren. The following pattern is used: WITH RECURSIVE tree(id, depth) AS (SELECT id, 0 FROM items WHERE id = 10 UNION ALL SELECT i.id, t.depth + 1 FROM items i JOIN tree t ON i.parent_id = t.id WHERE t.depth < 2) SELECT id, depth FROM tree;

Which levels can appear in the result?

  1. Only the root and its children, because recursion stops before processing depth 2.
  2. The root, its children, and its grandchildren, but not its great-grandchildren. (correct answer)
  3. The children, grandchildren, and great-grandchildren, but not the anchor root.
  4. The root through its great-grandchildren, because depth 2 is still permitted to recurse.
Explanation: When working with recursive CTEs, the key is understanding exactly when the recursion stops. The WHERE clause in the recursive member controls which rows are allowed to generate new children — not which rows appear in the result. Here's how the query unfolds step by step. The anchor selects the root node at depth = 0. The recursive member then joins items whose parent is in the current set, incrementing depth by 1, but only when t.depth < 2. So rows at depth = 0 (root) can recurse and produce depth = 1 children. Rows at depth = 1 can recurse and produce depth = 2 grandchildren. Rows at depth = 2 cannot recurse further because 2 < 2 is false — but the grandchildren themselves are still added to the result. This means the final result contains depths 0, 1, and 2: the root, children, and grandchildren. That confirms B is correct. A is wrong because it misreads the condition. t.depth < 2 allows depth-1 rows to recurse, so grandchildren (depth 2) do appear. The recursion stops after producing depth 2, not before. C is wrong because the anchor row (the root at depth 0) is always included — it's the base case of the recursion and is never filtered out. D is wrong because depth-2 rows fail the t.depth < 2 check, so they cannot generate great-grandchildren (depth 3). A useful trick: mentally ask, "which existing rows are allowed to recurse?" The filter applies to the parent generating children, not to the child being added. Depth 2 rows exist in the result but are blocked from spawning further.

Question 9

A bill of materials states that assembly A contains 2 units of B and 3 units of C. Each B contains 4 units of D, and each C contains 1 unit of D. A recursive CTE anchors on A with cumulative quantity 1. In each recursive step, it multiplies the parent's cumulative quantity by the component quantity on the traversed relationship.

If the query sums the cumulative quantities for all rows representing component D, how many units of D are required for one A?

  1. Five units, because the component quantities on the two relationships into D are added.
  2. Eleven units, because each path's quantities are multiplied before the path totals are added. (correct answer)
  3. Fourteen units, because all quantities encountered on both recursive paths are added together.
  4. Twenty-four units, because the quantities from both branches are multiplied into one combined total.
Explanation: When working with recursive CTEs on hierarchical data like bills of materials, the key insight is that each path through the hierarchy is independent, and quantities multiply as you traverse edges, then the path totals sum at the end. Here's how the traversal works for component D. Starting from A (cumulative quantity = 1), the CTE explores two distinct paths to D:
  • Path 1: A → B → D. The cumulative quantity becomes 1×2×4=81 \times 2 \times 4 = 8 units.
  • Path 2: A → C → D. The cumulative quantity becomes 1×3×1=31 \times 3 \times 1 = 3 units.
Summing both rows where the component is D gives 8+3=118 + 3 = 11 units. That confirms B is correct. A is wrong because it adds only the direct component quantities on the edges leading into D (4+1=54 + 1 = 5), completely ignoring the multiplicative effect of the parent-level quantities (how many Bs and Cs are needed per A). C is wrong because 14 would result from naively adding all quantities encountered across both paths (2+4+3+1=102 + 4 + 3 + 1 = 10... actually no clean path to 14), reflecting a misunderstanding of how cumulative multiplication interacts with path independence. D is wrong because 24 treats both paths as a single combined multiplication (2×4×3×1=242 \times 4 \times 3 \times 1 = 24), conflating two separate recursive branches into one operation. A useful tip: always trace each root-to-leaf path separately in a recursive CTE, multiply quantities along each path, then aggregate across paths at the end.

Question 10

Assume the database supports WITH RECURSIVE. Consider this query: WITH RECURSIVE nums(n) AS (SELECT 2 UNION ALL SELECT n + 3 FROM nums WHERE n < 10) SELECT n FROM nums;

Ignoring row order, which values does the query return?

  1. 2, 5, 8, because the recursive condition prevents any result greater than 10.
  2. 2, 5, 8, 10, because the recursive term stops at the boundary value.
  3. 2, 5, 8, 11, because the condition is tested before generating the next value. (correct answer)
  4. 2, 5, 8, 11, 14, because one additional iteration occurs after the condition fails.
Explanation: When you see a WITH RECURSIVE query, the critical skill is tracing the execution step by step, paying close attention to when the stopping condition is evaluated relative to when values are generated. Here's how this query unfolds. The anchor term produces 2. The recursive term then takes each current value, checks WHERE n < 10, and if true, generates n + 3. Starting from 2: since 2 < 10, it generates 5. Since 5 < 10, it generates 8. Since 8 < 10, it generates 11. Now the engine checks whether 11 < 10 — it's not, so recursion stops. The final result set is 2, 5, 8, 11, making C correct. The key insight: the condition WHERE n < 10 filters which existing rows are used as input for the next iteration. It does not filter the output values themselves. So 8 passes the condition, 8 + 3 = 11 is generated, and then 11 fails the condition and produces nothing further — but 11 itself is already in the result. A is wrong because it assumes values greater than 10 are excluded from the output, confusing a filter on the recursive input with a filter on the final results. B is wrong for a similar reason — 10 never appears because we jump from 8 to 11 in steps of 3. D is wrong because it imagines an extra iteration occurring after the condition fails, which doesn't happen; the failed condition simply produces no new rows. As a study tip, always trace recursive CTEs manually row by row, and remember: the WHERE clause controls what feeds the next step, not what gets returned.