Recursive CTE — Concept Check
Questions on Recursive CTE. A recursive CTE has an anchor query that seeds the recursion and a recursive member that repeatedly joins the CTE to itself until no new rows are produced.
Questions in this quiz
8 questions on Recursive CTE — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.In a recursive CTE, what ensures that the recursion stops?
- AThe recursive member returns zero rows.
- BThe anchor query runs multiple times.
- CA depth limit is always required.
- DThere is a UNION instead of UNION ALL.
2.You are using a recursive CTE to traverse an employee hierarchy. What might happen if you forget to include a termination condition?
- AThe CTE will only return the anchor query results.
- BThe CTE will automatically detect cycles.
- CThe CTE will terminate after one iteration.
- DThe CTE will loop infinitely.
3.A recursive CTE is used to generate a category tree. What is a common method to prevent cycles in PostgreSQL?
- ALimiting the recursion depth to 10.
- BUsing a path array to track visited nodes.
- CUsing UNION instead of UNION ALL.
- DEnsuring all nodes have a parent_id.
4.What is the role of the anchor query in a recursive CTE?
- AIt joins the CTE to itself repeatedly.
- BIt seeds the recursion with initial results.
- CIt prevents infinite loops by setting a limit.
- DIt deduplicates results in the CTE.
5.A developer uses UNION ALL in a recursive CTE. What effect does this have on the results?
- AIt prevents infinite recursion.
- BIt deduplicates the result set.
- CIt retains duplicates in the result set.
- DIt automatically detects cycles.
6.When using a recursive CTE to find all reports under a manager, what does the recursive member typically do?
- ASelects the root manager only.
- BJoins employees to the current level via manager_id.
- CLimits the recursion depth to prevent cycles.
- DEnsures all employees have unique manager_ids.
7.In PostgreSQL, how can you natively detect cycles in a recursive CTE?
- AUsing UNION instead of UNION ALL.
- BBy ensuring all nodes have a parent_id.
- CBy setting a depth limit.
- DUsing the CYCLE clause.
8.A recursive CTE is used to compute Fibonacci numbers. What is a potential risk if not carefully implemented?
- AIt may run indefinitely without a termination condition.
- BIt will always compute the correct sequence.
- CIt will only compute up to the 10th Fibonacci number.
- DIt will automatically deduplicate results.