How can I get employess who earn less money than their managers? – ORACLE – SCOTT database

I am trying to receive name of employess who earn less than their managers. There are three departments and all of them have got manager. Subquery in brackets returns salary of this managers correct. I also tried to receive all workers without managers but my code is always wrong. Do you know what I should correct?

SELECT sal
FROM SCOTT.emp
WHERE NOT EXISTS (SELECT sal
                  FROM SCOTT.emp
                  WHERE job LIKE 'MANAGER');

This is my table:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO


  7369 SMITH      CLERK           7902 80/12/17        800                    20
  7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  7566 JONES      MANAGER         7839 81/04/02       2975                    20
  7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  7839 KING       PRESIDENT            81/11/17       5000                    10
  7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  7900 JAMES      CLERK           7698 81/12/03        950                    30
  7902 FORD       ANALYST         7566 81/12/03       3000                    20
  7934 MILLER     CLERK           7782 82/01/23       1300                    10

>Solution :

You can use a hierarchical query:

SELECT ename,
       PRIOR ename AS mgr_ename,
       sal,
       PRIOR sal AS mgr_sal
FROM   emp
WHERE  (LEVEL = 2 AND sal < PRIOR sal) -- salary is less than manager's salary
OR     (LEVEL = 1 AND mgr IS NULL) -- has no manager
CONNECT BY PRIOR empno = mgr

Which outputs:

ENAME MGR_ENAME SAL MGR_SAL
ADAMS SCOTT 1100 3000
SMITH FORD 800 3000
MILLER CLARK 1300 2450
ALLEN BLAKE 1600 2850
JAMES BLAKE 950 2850
TURNER BLAKE 1500 2850
MARTIN BLAKE 1250 2850
WARD BLAKE 1250 2850
KING 5000
JONES KING 2975 5000
CLARK KING 2450 5000
BLAKE KING 2850 5000

db<>fiddle here

Leave a Reply