SQL interview question: Employee and Manager Self Join
Employee Reporting Structure
HR needs a report showing each employee alongside their direct manager's information for org chart visualization.
Join the employees table to itself to show each employee with their manager's name and title.
Return employee_name (full name), employee_title, manager_name (full name), and manager_title. Exclude employees with no manager. Order by the employee's id and limit to 10.
Skills: self-join for hierarchical relationships
Tables
employees 37 rows
- id integer PK
- first_name text
- last_name text
- email text
- department_id integer → departments
- salary integer
- hire_date text
- manager_id integer → employees
- title text
| id | first_name | last_name | department_id | salary | hire_date | manager_id | title | |
|---|---|---|---|---|---|---|---|---|
| 1 | Alice | Chen | [email protected] | 1 | 250000 | 2015-01-15 | NULL | CEO |
| 2 | Robert | Martinez | [email protected] | 2 | 180000 | 2016-03-20 | NULL | CEO |
| 3 | Sarah | Johnson | [email protected] | 1 | 150000 | 2017-06-10 | 1 | VP Engineering |