Recursive CTE concept in SQL - Mini Project: Build & Apply

Build an Employee Hierarchy Using Recursive CTE

📖 Scenario: You work in a company database where employees have managers. You want to find the full chain of command for each employee, starting from the top manager down to the employee.

🎯 Goal: Create a recursive Common Table Expression (CTE) to list each employee along with their manager hierarchy.

📋 What You'll Learn

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

Insert the given employee data with exact values.

Create a recursive CTE named employee_hierarchy to find all levels of management.

Select the employee name and their manager chain from the CTE.

💡 Why This Matters

🌍 Real World

Companies often store employee-manager relationships in databases. Recursive CTEs help find full reporting chains easily.

💼 Career

Understanding recursive CTEs is important for database analysts and developers working with hierarchical data.

1

Create the employees table and insert data

Write SQL 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', 2), (4, 'Diana', 2), (5, 'Eve', 1).

SQL

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

Need a hint?

Use CREATE TABLE to define the structure. Use INSERT INTO with multiple rows to add data.

2

Define the base query for the recursive CTE

Write the first part of the recursive CTE named employee_hierarchy that selects employees who have no manager (where manager_id is NULL). Select id, name, and set manager_chain to the employee's name.

SQL

WITH RECURSIVE employee_hierarchy AS (
  -- Base query: select employees with no manager
  SELECT id, name, name AS manager_chain
  FROM employees
  WHERE manager_id IS NULL
  -- Your code here
)

Need a hint?

The base query finds top-level managers who have no manager themselves.

3

Add the recursive part to build the manager chain

Add the recursive SELECT to employee_hierarchy that joins employees to employee_hierarchy on employees.manager_id = employee_hierarchy.id. Select employees.id, employees.name, and concatenate employee_hierarchy.manager_chain with ' > ' and employees.name as manager_chain.

SQL

  UNION ALL
  -- Recursive query: join employees to employee_hierarchy
  SELECT employees.id, employees.name, employee_hierarchy.manager_chain || ' > ' || employees.name
  FROM employees
  JOIN employee_hierarchy ON employees.manager_id = employee_hierarchy.id
  -- Your code here
)

Need a hint?

The recursive part adds employees under their managers, building the chain.

4

Select the full employee hierarchy from the CTE

Write a SELECT statement after the recursive CTE to get name and manager_chain from employee_hierarchy. Order the results by id ascending.

SQL

SELECT name, manager_chain
FROM employee_hierarchy
ORDER BY id ASC;
-- Your code here

Need a hint?

This final query shows each employee with their full manager chain.