SQL self join or sub-query interview question (employee-manager salary)

One of my favorite interview questions that tips even seasoned SQL guys (maybe because it's too simple) is around querying data that involves a self join.

Question:
Given an Employee table which has 3 fields - Id (Primary key), Salary and Manager Id, where manager id is the id of the employee that manages the current employee, find all employees that make more than their manager in terms of salary. Bonus: Write the table creation script.

Read more about the solution at SQL self join interview question on my programming interviews blog's post (https://www.programminginterviews.info/2013/03/sql-self-join-or-sub-query-interview-question-employee-manager-salary.html).

Comments

• Anonymous
  April 08, 2014
  SELECT e.first_name, e.employee_id,e.salary, m.first_name,m.salary as manager, e.manager_id fROM  employees e, employees m WHERE e.manager_id = m.employee_id and e.salary >m.salary;

• Anonymous
  May 09, 2014
  thanks for nice info . You can find more questions in <a href="skillgun.com/.../interview-questions-and-answers">sql interview questions and answers</a>

• Anonymous
  May 19, 2014
  skillgun.com/.../interview-questions-and-answers

• Anonymous
  August 03, 2014
  select x.sal,x.ename,y.ename from emp x,emp y where x.mgr =y.empno and x.sal > y.sal;

• Anonymous
  August 25, 2014
  Writing using JOIN: SELECT E.name FROM Employee E JOIN Employee M ON E.manager_id = M.id WHERE E.salary > M.salary

• Anonymous
  May 18, 2015
  The comment has been removed

  • Anonymous
    July 13, 2016
    with EMP_MNG (Emp_Id, Emp_Name, Manager_Id, Manager_Name, Lvl) as(select Emp_Id, Emp_Name, Manager_Id, convert(varchar(20),'NULL') as Manager_Name, 0 as Lvlfrom Employeewhere Manager_Id is nullunion allselect E.Emp_Id, E.Emp_Name, E.Manager_Id, M.Emp_Name as Manager_Name, M.Lvl + 1from EMP_MNG M inner join Employee E on M.Emp_Id = E.Manager_Id)select * from EMP_MNG

• Anonymous
  October 17, 2015
  To get the hierarchy, create the structure like below,

1.  One table for Employee.  Store the manager data also in Employee (afterall a Manager is also an Employee).
2.  Create another table for hierarchy as EmployeeID and SuperEmployeeID.  If an employee is to report to multiple managers, put one entry for each manager in this table.
3.  To find the hierarchy, loop thru the hierarchy table to reach the case where the SuperEmployeeID is NULL.  You might end up in multiple hierarchies if an Employee is reporting to multiple managers. Hope this helps!

• Anonymous
  November 27, 2015
  Emp table: EId       EName     MId -----      ----------      ------ 1            A              Null 2            B                 1 3            C                 2 My Requirement is      EName         Mangr Of Mngr                                        ----------         ----------------------                                           C                        A Any one Help me .....?????

  • Anonymous
    July 13, 2016
    with EMP_MNG (Emp_Id, Emp_Name, Manager_Id, Manager_Name, Lvl) as(select Emp_Id, Emp_Name, Manager_Id, convert(varchar(20),'NULL') as Manager_Name, 0 as Lvlfrom Employeewhere Manager_Id is nullunion allselect E.Emp_Id, E.Emp_Name, E.Manager_Id, M.Emp_Name as Manager_Name, M.Lvl + 1from EMP_MNG M inner join Employee E on M.Emp_Id = E.Manager_Id)select * from EMP_MNG