Quiz for Recursive CTEs

Q1: What must a recursive CTE include?

A. A primary key B. An infinite loop C. A base case and a recursive case D. A JOIN clause

Q2: What does CTE stand for in SQL?

A. Common Table Expression B. Complex Table Expression C. Composite Table Entity D. Common Transfer Expression

Q3: Which keyword is used to define a recursive CTE?

A. JOIN B. RECURSIVE C. CONNECT D. REPEAT

Q4: What is the base case in a recursive CTE?

A. The first query part B. The join clause C. The final result D. The recursive step

Q5: What must a recursive CTE contain to avoid infinite recursion?

A. A HAVING clause B. An index C. A termination condition D. A GROUP BY clause

Q6: Which clause combines the anchor and recursive members?

A. UNION ALL B. INTERSECT C. EXCEPT D. GROUP BY

Q7: Which SQL clause is optional in recursive CTEs but helps control depth?

A. LIMIT B. MAXRECURSION C. GROUP BY D. ROLLUP

Q8: Recursive CTEs are typically used to process what kind of data?

A. Flat tables B. Hierarchical data C. Key-value pairs D. Temporal data

Q9: What happens if a recursive CTE has no termination condition?

A. Syntax error B. Only one row is returned C. Infinite loop or runtime error D. The query auto-stops

Q10: Which of the following is true about recursive CTEs?

A. They can only be used in SELECT B. They must use ORDER BY C. They require UNION or UNION ALL D. They cannot use WHERE clause

Q11: How many queries make up a recursive CTE?

A. One B. Two C. Three D. Unlimited

Q12: Which operator is NOT typically used in recursive CTEs?

A. UNION ALL B. UNION C. MINUS D. INTERSECT

Q13: In SQL Server, which option controls the recursion depth?

A. TOP B. RECURSIONLIMIT C. MAXRECURSION D. CTEDEPTH

Q14: Can recursive CTEs be nested?

A. No B. Yes, to any level C. Only two levels D. Only in MySQL

Q15: What is a common use-case for recursive CTEs?

A. Flatten JSON B. Find prime numbers C. Sum rows D. Generate hierarchical reports

Q16: Which databases support recursive CTEs?

A. MySQL 8+ B. PostgreSQL C. SQL Server D. All of the above

Q17: Which of the following is mandatory in a recursive CTE definition?

A. A GROUP BY clause B. Anchor and recursive members C. ORDER BY on anchor D. Temporary table creation

Q18: How do recursive CTEs differ from regular CTEs?

A. They use JOINs only B. They require GROUP BY C. They reference themselves D. They must have a LIMIT clause

Q19: Which keyword is used to start the CTE definition?

A. WITH B. START C. INITIATE D. AS

Q20: What happens when MAXRECURSION is exceeded?

A. Partial results returned B. Error thrown C. Query ignored D. Recursion restarts

Q21: What should you verify before running a recursive CTE?

A. Index availability B. Termination condition exists C. Primary key defined D. CTE must be temporary