Employees who earn more than their manager
Find every employee who earns more than their own manager. Show the employee's name and salary, and the manager's name and salary.
What it tests
Whether you can treat one table as two — a self join — and read a self-referencing key the right way round.
The pattern
Self join. "Their manager", "their parent", "the person who referred them" — a column pointing at another row of the same table.
How to approach it
- Notice that manager_id points to another row of employees.
- Use the table twice: e for the employee, m for the manager.
- Join with
m.emp_id = e.manager_id(m is e's manager). - Keep rows where e.salary > m.salary. An inner join is fine here: people with no manager can't earn more than one.
Edge cases to mention
- Employees with no manager (Asha): the inner join drops them, which is correct here.
- Equal salaries: "more than" means strictly greater, so a tie is not included — confirm with the interviewer.
- A manager_id that points to a deleted employee: the inner join drops the row; a foreign key should prevent it.
Common mistakes
- Joining the wrong way round (
e.emp_id = m.manager_id), which compares people with their reports. - Leaving columns unprefixed, so name and salary are ambiguous.
Not true: e.emp_id = m.manager_id joins each employee e to their manager m.
In fact: That condition makes e the manager of m. To join each employee e to their manager m, write m.emp_id = e.manager_id.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.