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 (http://www.programminginterviews.info/2013/03/sql-self-join-or-sub-query-interview-question-employee-manager-salary.html).

Comments (10)

  1. vinayak sharma says:

    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;

  2. Palle Technologies says:

    thanks for nice info . You can find more questions in <a href="skillgun.com/…/interview-questions-and-answers">sql interview questions and answers</a>

  3. usha says:

    select x.sal,x.ename,y.ename from emp x,emp y where x.mgr =y.empno and x.sal > y.sal;

  4. SriRam says:

    Writing using JOIN:

    SELECT E.name FROM Employee E

    JOIN Employee M ON E.manager_id = M.id

    WHERE E.salary > M.salary

  5. Pramod says:

    Hi, Can anybody help in getting Managers list from my level and up to Head of company in SQL server 2012.

    For Example: Employee ID-> Manager ID –> Manager's Manager ID–>…–>Up to Super Boss.

    1. ankireddy says:

      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 Lvl
      from Employee
      where Manager_Id is null
      union all
      select E.Emp_Id, E.Emp_Name, E.Manager_Id, M.Emp_Name as Manager_Name, M.Lvl + 1
      from EMP_MNG M inner join Employee E on M.Emp_Id = E.Manager_Id
      )
      select * from EMP_MNG

  6. Ambarish S N says:

    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!

  7. Satish says:

    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 …..?????

    1. ankireddy says:

      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 Lvl
      from Employee
      where Manager_Id is null
      union all
      select E.Emp_Id, E.Emp_Name, E.Manager_Id, M.Emp_Name as Manager_Name, M.Lvl + 1
      from EMP_MNG M inner join Employee E on M.Emp_Id = E.Manager_Id
      )
      select * from EMP_MNG