Write a query to show ename who don’t have reporting manager in deptno 10 or 30?

SQL> SELECT * FROM emp;  


  EMPNO  ENAME  JOB       MGR   HIREDATE   SAL   COMM  DEPTNO  
  7369   SMITH   CLERK     7902  17-DEC-80  2900        20
  7499   ALLEN   SALESMAN  7698  20-FEB-81  3600  300   30
  7521   WARD    SALESMAN  7698  22-FEB-81  3250  500   30
  7566   JONES   MANAGER   7839  02-APR-81  4975        20
  7654   MARTIN  SALESMAN  7698  28-SEP-81  3250  1400  30
  7698   BLAKE   MANAGER   7839  01-MAY-81  4850        30
  7782   CLARK   MANAGER   7839  09-JUN-81  4450        10
  7788   SCOTT   ANALYST   7566  19-APR-87  5000        20
  7839   KING    PRESIDENT       17-NOV-81  7000        10
  7844   TURNER  SALESMAN  7698  08-SEP-81  3500  0     30
  7876   ADAMS   CLERK     7788  23-MAY-87  3100        20
  7900   JAMES   CLERK     7698  03-DEC-81  2950        30
  7902   FORD    ANALYST   7566  03-DEC-81  5000        20
  7934   MILLER  CLERK     7782  23-JAN-82  3300        10

This is the emp table given in Oracle SQL.

And I need to display the employees who don’t have reporting manager in department 10 or 30.

Answer

I would use not exists:

select e.*
from emp e
where not exists (select 1
                  from emp em
                  where em.empno = e.mgr and em.deptno in (10, 30)
                 );

Advertisement

Answer