[Solved] Given the table Employees(id, manager_id) and this recursive CTE:WITH RECURSIVE EmpCTE AS ( SELECT id, manager_id FROM Employees WHERE id = 1 UNION ALL SELECT e.id, e.... | SQL

SQL - Common Table Expressions (CTEs)

Given the table Employees(id, manager_id) and this recursive CTE:

WITH RECURSIVE EmpCTE AS (
  SELECT id, manager_id FROM Employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id FROM Employees e JOIN EmpCTE c ON e.manager_id = c.id
)
SELECT id FROM EmpCTE ORDER BY id;

What does this query return?

AAll employees who have no manager

BAll employees who report directly or indirectly to employee with id 1

COnly the employee with id 1

DAll employees in the table

Step-by-Step Solution

Solution:

Step 1: Analyze the anchor query
The anchor selects employee with id = 1.
Step 2: Analyze the recursive part
The recursive part joins Employees where manager_id matches the id from previous results, thus finding direct reports, then their reports, and so on.
Final Answer:
All employees who report directly or indirectly to employee with id 1 -> Option B
Quick Check:
Recursive CTE finds all subordinates under id 1 [OK]

Quick Trick: Recursive join on manager_id finds all reports [OK]

Common Mistakes:

Thinking it returns only the anchor row
Assuming it returns employees without managers
Believing it returns all employees regardless of manager

Master "Common Table Expressions (CTEs)" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given the table Employees(id, manager_id) and this recursive CTE:

Step 1: Analyze the anchor query

Step 2: Analyze the recursive part

Final Answer:

Quick Check:

Want More Practice?