SQL Topic collection

Self Joins & Hierarchical Queries SQL Topic exercises

Query organization charts, trees, and parent-child relationships.

Exercises
16
Difficulty
Advanced

What this collection tests

Skills and query patterns

Self joins, recursive CTEs, hierarchies, and parent-child data

Curated exercises

Choose an exercise

Work in the live editor where supported. Advanced guided labs state their engine boundary before you open the workspace.

  1. Exercise 1Easy

    Find Employee Manager Details Using Self Join

    Display each employee along with their manager's name using a self-join. Top-level employees (manager_id IS NULL) must still appear with manager_name = NULL. Return employee_id, employee_name, manager_id, manager_name — ordered by employee_id.

    • Joins
    • NULL handling
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  2. Exercise 2Easy

    Employees Earning More Than Their Manager

    Find every employee whose salary is strictly greater than their direct manager's salary. Return employee_name, employee_salary, manager_name, manager_salary — ordered by employee_name, employee_id.

    • Joins
    • Filtering
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  3. Exercise 3Medium

    Find Pairs of Employees in the Same Department

    Find pairs of employees who work in the same department. Exclude self-pairs and avoid duplicate (A,B)/(B,A) pairs by enforcing employee1_id < employee2_id. Skip rows where department_id IS NULL. Return employee1_name, employee2_name, department_id — ordered by department_id, employee1_name, employee2_name.

    • Joins
    • NULL handling
    • Filtering

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  4. Exercise 4Medium

    Direct Manager and Skip-Level Manager

    For each employee show both the direct manager and the skip-level manager (manager's manager). Use two LEFT JOINs so employees without managers (or without skip-level managers) still appear with NULLs. Return employee_id, employee_name, direct_manager_name, skip_level_manager_name — ordered by employee_id.

    • Joins
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  5. Exercise 5Medium

    Each Employee's Direct-Report Count

    For every employee, count how many people report DIRECTLY to them (0 if none). Return manager_id (the employee's id), manager_name, total_reports — ordered by total_reports DESC, manager_id.

    • Joins
    • Aggregation
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  6. Exercise 6Hard

    Organizational Hierarchy with Path (Recursive CTE)

    Use a recursive CTE to walk the org chart from CEO downward. For each row return employee_id, employee_name, manager_id, level (CEO=1), and hierarchy_path (the reporting chain shown as 'CEO -> VP -> ... -> employee'). Order by level, employee_id.

    • Recursive CTE
    • CTEs
    • Joins

    SQLite + PostgreSQL + MySQL live · 2 guided

    Open exercise →
  7. Exercise 7Hard

    Find All Subordinates Under a Manager (Including Herself)

    Walk the org chart downward from employee_id 5 (Emma Sales Mgr). Return Emma plus every direct and indirect report. level_under_manager = 0 for Emma, 1 for direct reports, 2 for skip-level, etc. Return employee_id, employee_name, level_under_manager, manager_id — ordered by level_under_manager, employee_id.

    • Recursive CTE
    • Joins
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  8. Exercise 8Hard

    Find All Managers in Reporting Chain (Path to CEO)

    Walk UPWARD from employee_id 10 (Jack Engineer) to the CEO. Return the employee, then each manager, then their manager, etc. level = 1 at Jack, increasing toward the top. Return employee_id, employee_name, manager_id, level — ordered by level.

    • Recursive CTE
    • Joins
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  9. Exercise 9Hard

    Bill-of-Materials Hierarchy (Recursive Walk)

    Use a recursive CTE to display the complete bill of materials for product_id 1 (Bicycle). Quantities multiply down the tree (a Wheel needs 2, each Wheel has 32 Spokes → 64 Spokes total). Return product_id, product_name, component_id, component_name, quantity, level — ordered by level, component_id.

    • Recursive CTE
    • CTEs
    • Joins

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  10. Exercise 10Hard

    Total Cost of Product Including All Sub-Components

    Recursive CTE on bill_of_materials joined to components to calculate extended_cost (unit_cost × cumulative_quantity) for product_id 1 (Bicycle). Return product_id, product_name, component_id, component_name, unit_cost, extended_cost, level — ordered by level, component_id.

    • Recursive CTE
    • CTEs
    • Joins

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  11. Exercise 11Hard

    Department Hierarchy with TRUE Cumulative Budget Rollup

    Build a recursive CTE that walks the department hierarchy AND computes cumulative_budget = own budget + every descendant department's budget. Use a second 'subtree' CTE that lists every (root, descendant) pair so cumulative_budget can be SUMmed. Return department_id, department_name, parent_department_id, budget, cumulative_budget, hierarchy_level — ordered by department_id.

    • Recursive CTE
    • CTEs
    • Joins

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  12. Exercise 12Hard

    Total Payroll Under Each Manager (Subtree Salary Sum)

    For every employee who is a manager (has at least one report), compute the total payroll of their entire subtree, INCLUDING the manager's own salary. Use a recursive (root, descendant) CTE on employees. Return manager_id, manager_name, subtree_size, total_payroll — ordered by total_payroll DESC, manager_id.

    • Recursive CTE
    • CTEs
    • Joins

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  13. Exercise 13Hard

    Lowest Common Ancestor of Two Employees

    Find the lowest common ancestor (LCA) of employee_id 8 (Henry) and employee_id 13 (Mia) — the deepest employee that appears in both of their manager chains. Walk both chains upward via recursive CTEs, intersect on employee_id, and pick the one with the smallest combined depth. Return ancestor_id, ancestor_name, depth_from_a, depth_from_b — exactly one row.

    • Recursive CTE
    • CTEs
    • Joins

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  14. Exercise 14Hard

    Reverse Bill of Materials — Where Used? (Spoke)

    Walk UPWARD from component_id 7 (Spoke) through bill_of_materials to find every product (and intermediate sub-assembly) that USES Spoke. Return product_id, product_name, level — ordered by level, product_id.

    • Recursive CTE
    • Joins
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  15. Exercise 15Hard

    DFS Pre-Order Org Chart with Indentation

    Print the org chart in depth-first pre-order: parents appear before their children, sibling order = ascending employee_id. Each employee_name is prefixed with two spaces per level above 1. Carry a sort_path string built from zero-padded employee_ids so ORDER BY sort_path produces correct DFS order. Return employee_id, indented_name, hierarchy_level, sort_path — ordered by sort_path.

    • Recursive CTE
    • Joins
    • Subqueries

    SQLite + PostgreSQL live · 3 guided

    Open exercise →
  16. Exercise 16Hard

    Detect Cycles in a Parent Hierarchy Safely

    Start an upward recursive walk from every hierarchy_nodes row. Detect the first repeated node in each origin path without looping forever. Return origin_id, repeated_node_id, repeated_node_name, depth, visited_path — ordered by origin_id.

    • Recursive CTE
    • Joins
    • Subqueries

    SQLite + PostgreSQL + MySQL live · 2 guided

    Open exercise →
01

Attempt

Write a query or design from the brief before opening any hints.

02

Validate

Run it against the included dataset and inspect the output.

03

Explain

Compare the verified answer and explain each choice aloud.

Learn before practicing Self Joins & Hierarchical Queries

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.