DB Exercises
0
← Exercises/
Joins•5 of 12
PrevNext
JoinsIntermediatePRO

Organizational Chart Hierarchy with SELF JOIN

Scenario

During company-wide re-org planning, HR needs a consolidated management report displaying each employee alongside the full name of their direct manager. For top executives with no manager, display 'Executive Board'.

Task
Join the `employees` table to itself (Self-Join) matching each employee's `manager_id` to the manager's `emp_id`. Return: 1. `e.emp_id` 2. `e.full_name` AS `employee_name` 3. `e.job_title` 4. `COALESCE(m.full_name, 'Executive Board')` AS `manager_name` Use a `LEFT JOIN` so employees without a manager (CEOs/founders) are retained. Order the results by `e.emp_id` ascending.
Expected Output Format

Org chart hierarchy showing employee name, title, and manager name.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Organizational Chart Hierarchy with SELF JOIN” SQL Problem

Business Scenario

During company-wide re-org planning, HR needs a consolidated management report displaying each employee alongside the full name of their direct manager. For top executives with no manager, display 'Executive Board'.

Expected Output & Criteria

Org chart hierarchy showing employee name, title, and manager name.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE employees (
        emp_id INT PRIMARY KEY,
        full_name VARCHAR(100) NOT NULL,
        job_title VARCHAR(80) NOT NULL,
        manager_id INT REFERENCES employees(emp_id)
      );

Target SQL Clauses & Operators

JOINCOALESCEORDER BY

Want more Joins practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Joins Track→