Scenario SQL Question

Find All Subordinates Under a Manager

Build mastery in Self Joins Hierarchical with this scenario-based SQL challenge and live execution support.

Problem Statement

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.

Relevant Tables

employees

SQL Concepts Used in This Scenario

Strengthen your understanding with these targeted learning topics:

Self JoinsJoins & Relationships
Recursive CTEs for Hierarchical DataSubqueries & CTEs
Common Table ExpressionsSubqueries & CTEs