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

  • Article

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