SQL Common Table Expressions (CTEs) 3

Question 1

What keyword is required to define a recursive CTE in standard SQL?

Accepted Answer

RECURSIVE

Answer

Standard SQL requires WITH RECURSIVE to define a recursive CTE.

Question 2

A recursive CTE consists of which two parts joined together?

Accepted Answer

An anchor member and a recursive member

Answer

Recursive CTEs combine an anchor (base) member with a recursive member, usually via UNION ALL.

Question 3

Which set operator typically connects the anchor and recursive members of a recursive CTE?

Accepted Answer

UNION ALL

Answer

UNION ALL is standard for combining the anchor and recursive members of a recursive CTE.

Question 4

Recursive CTEs are especially well suited to querying which kind of data?

Accepted Answer

Hierarchical or tree-structured data like org charts

Answer

Recursive CTEs excel at traversing hierarchies such as organizational charts or bill-of-materials.

Question 5

What stops a recursive CTE from running indefinitely?

Accepted Answer

The recursive member eventually returns no rows

Answer

Recursion terminates naturally when the recursive member produces no additional rows.

SQL Practice Test