Bird
0
0

Which of the following correctly initiates a recursive CTE named HierarchyTree in SQL?

easy📝 Syntax Q3 of 15
SQL - Common Table Expressions (CTEs)
Which of the following correctly initiates a recursive CTE named HierarchyTree in SQL?
AWITH RECURSIVE HierarchyTree AS (SELECT * FROM Table WHERE parent_id IS NULL UNION ALL SELECT t.* FROM Table t JOIN HierarchyTree h ON t.parent_id = h.id)
BWITH HierarchyTree RECURSIVE AS (SELECT * FROM Table WHERE parent_id IS NULL UNION ALL SELECT t.* FROM Table t JOIN HierarchyTree h ON t.parent_id = h.id)
CWITH RECURSIVE HierarchyTree (SELECT * FROM Table WHERE parent_id IS NULL UNION SELECT t.* FROM Table t JOIN HierarchyTree h ON t.parent_id = h.id)
DWITH HierarchyTree AS RECURSIVE (SELECT * FROM Table WHERE parent_id IS NULL UNION ALL SELECT t.* FROM Table t JOIN HierarchyTree h ON t.parent_id = h.id)
Step-by-Step Solution
Solution:

  1. Step 1: Recall recursive CTE syntax

    The correct syntax starts with WITH RECURSIVE CTEName AS (anchor_query UNION ALL recursive_query).

  2. Step 2: Identify correct option

    WITH RECURSIVE HierarchyTree AS (SELECT * FROM Table WHERE parent_id IS NULL UNION ALL SELECT t.* FROM Table t JOIN HierarchyTree h ON t.parent_id = h.id) correctly uses WITH RECURSIVE HierarchyTree AS and includes UNION ALL.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Recursive CTEs require WITH RECURSIVE CTEName AS [OK]
Quick Trick: Recursive CTE starts with WITH RECURSIVE CTEName AS [OK]
Common Mistakes:
  • Placing RECURSIVE after CTE name
  • Using UNION instead of UNION ALL
  • Omitting RECURSIVE keyword

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Advanced Query Patterns - Finding gaps in sequences - Quiz 12easy CASE Expressions - CASE in SELECT for computed columns - Quiz 15hard Common Table Expressions (CTEs) - Why CTEs are needed - Quiz 6medium Database Design and Normalization - Database design best practices - Quiz 5medium Database Design and Normalization - Star schema concept - Quiz 2easy Indexes and Query Performance - Single column index - Quiz 14medium Stored Procedures and Functions - Variables and SET statements - Quiz 11easy Transactions and Data Integrity - Savepoints within transactions - Quiz 5medium Triggers - Trigger for audit logging - Quiz 5medium Triggers - DELETE trigger - Quiz 2easy