Interview Questions on Self Join

We used to get below questions frequently on SELF JOIN concept.

1) Find out emp id who are managers.
2) Find out managers with count of sub ordinates.
3) Find out managers who are having more than one sub ordinate



we are going to take EMP table to show examples.

SQL> select * from emp;




EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT          11/17/1981    5000.00                  10
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00                   30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00                   10
 7566 JONES      MANAGER    7839 4/2/1981      2975.00                   20
 7788 SCOTT      ANALYST     7566 4/19/1987     3000.00                  20
 7902 FORD       ANALYST      7566 12/3/1981     3000.00                  20
 7369 SMITH      CLERK         7902 12/17/1980     800.00                   20
 7499 ALLEN      SALESMAN   7698 2/20/1981     1600.00    300.00     30
 7521 WARD       SALESMAN  7698 2/22/1981     1250.00    500.00     30
 7654 MARTIN     SALESMAN  7698 9/28/1981     1250.00   1400.00    30
 7844 TURNER     SALESMAN  7698 9/8/1981      1500.00      0.00       30
 7876 ADAMS      CLERK        7788 5/23/1987     1100.00                   20
 7900 JAMES      CLERK         7698 12/3/1981      950.00                    30
 7934 MILLER     CLERK         7782 1/23/1982     1300.00                   10



Case 1: Find out emp id who are managers.



SQL> select e1.empno, e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr;

EMPNO   MGR
----- -----
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782

If we observe empno in  above ouput , we didnt get "7839" as its President and He does not need to report to anyone.

If we want president ID also then we can use left join to get it as below.

SQL> select e1.empno, e1.mgr from emp e1 left join emp e2 on e2.empno= e1.mgr;

EMPNO   MGR
----- -----
 7839
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782
 
 Case 2: Find out managers with count of sub ordinates.

As we required count the sub ordinates so we will use aggregate  function ie. COUNT and we also know that whenever we use aggregate function its mandatory to use Group by clause for remaining  columns of select query.
 
SQL> select count(e1.empno), e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr group by e1.mgr;
COUNT(E1.EMPNO)   MGR
--------------- -----
              2  7566
              5  7698
              1  7782
              1  7788
              3  7839
              1  7902 

Case 3: Find out managers who are having more than one sub ordinate


 Since we required to find more than one sub ordinate so we need to use having clause as below.
SQL> select count(e1.empno), e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr group by e1.mgr having count(e1.empno) >1;
 
COUNT(E1.EMPNO)   MGR
--------------- -----
              2  7566
              5  7698
              3  7839