Self-Joins for Hierarchies | Dataford Interview Questions

How do you write SQL to perform a self-join for hierarchical or comparison logic?

Using the employees and departments tables, return each employee with their direct manager and department. Include employees without managers and employees whose manager reference does not match an employee.

Output

One row per employee with employee_id, employee_name, manager_name, department_name, and hierarchy_status
Sort by employee_id ascending

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
manager_id	INT	Employee identifier of the direct manager
department_id	INT	Employee's department identifier

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

Your question is Self-Joins for Hierarchies. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem