Recursive CTE for hierarchical data in SQL - Mini Project: Build & Apply

Building a Hierarchical Employee Manager Query with Recursive CTE

📖 Scenario: Imagine you work in a company database team. Your task is to help find the chain of managers for employees. The company stores employee data with each employee having a manager ID pointing to their direct manager. You want to build a query that shows the full management chain for each employee.

🎯 Goal: Create a recursive Common Table Expression (CTE) in SQL that lists each employee along with their full chain of managers up to the top-level boss.

📋 What You'll Learn

Create a table called employees with columns id, name, and manager_id.

Insert the exact employee data with IDs and manager relationships as specified.

Define a recursive CTE named management_chain that starts with employees who have no manager.

Recursively join to find each employee's manager chain.

Select employee names and their full manager chain in the final query.

💡 Why This Matters

🌍 Real World

Companies often store employee-manager relationships in databases. Recursive CTEs help query full management chains or organizational hierarchies easily.

💼 Career

Understanding recursive CTEs is valuable for database developers, analysts, and anyone working with hierarchical data in SQL databases.

1

Create the employees table and insert data

Write SQL statements to create a table called employees with columns id (integer), name (text), and manager_id (integer). Then insert these exact rows: (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eva', 2).

SQL

-- Create the employees table and insert the data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as given.

2

Define the base query for the recursive CTE

Write the first part of a recursive CTE named management_chain that selects id, name, and manager_id from employees where manager_id IS NULL. This will be the anchor member of the CTE.

SQL

WITH RECURSIVE management_chain AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    -- Your code here
)

Need a hint?

The anchor member selects employees without managers.

3

Add the recursive member to the CTE

Extend the management_chain CTE by adding a UNION ALL clause. Join employees as e with management_chain as mc on e.manager_id = mc.id. Select e.id, e.name, and e.manager_id in this recursive part.

SQL

    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN management_chain mc ON e.manager_id = mc.id
    -- Your code here
)

Need a hint?

The recursive member joins employees to the chain on manager ID.

4

Select from the recursive CTE to show full hierarchy

Write a final SELECT statement that selects id, name, and manager_id from the management_chain CTE to display the full employee hierarchy.

SQL

SELECT id, name, manager_id FROM management_chain;
-- Your code here

Need a hint?

The final query selects all rows from the recursive CTE.