MediumSQL
Find Each Employee's Manager
SQLDatabases
The staff table stores every person including their manager (viamanager_id). The CEO has manager_id = NULL.
Write a SQL query that returns each non-CEO employee's name alongside their
manager's name, ordered by employee name.
Table: staff
| id | name | manager_id |
|---|---|---|
| 1 | CEO | NULL |
| 2 | Alice | 1 |
| 3 | Bob | 1 |
| 4 | Carol | 2 |
| 5 | Dave | 2 |
| 6 | Eve | 3 |
Expected output (columns: employee, manager)
Alice|CEO
Bob|CEO
Carol|Alice
Dave|Alice
Eve|BobA self-join joins a table to itself using two aliases to treat rows as
different "roles" (employee vs manager).
Sample tests
Test #15 non-CEO employees across 2 levels
Input: "CREATE TABLE staff (id INTEGER, name TEXT, manager_id INTEGER);\nINSERT INTO staff VALUES\n (1, 'CEO', NULL),\n (2, 'Alice', 1),\n (3, 'Bob', 1),\n (4, 'Carol', 2),\n (5, 'Dave', 2),\n (6, 'Eve', 3);"
Output: "Alice|CEO\nBob|CEO\nCarol|Alice\nDave|Alice\nEve|Bob"