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

CodePudding user response：

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

CodePudding user response：

Looks like self join to me.

SQL> select e.ename employee, e.sal sal_employee,
  2         m.ename manager , m.sal sal_manager
  3  from emp e join emp m on e.mgr = m.empno
  4  where e.sal < m.sal;

EMPLOYEE   SAL_EMPLOYEE MANAGER    SAL_MANAGER
---------- ------------ ---------- -----------
JAMES               950 BLAKE             2850
TURNER             1500 BLAKE             2850
MARTIN             1250 BLAKE             2850
WARD               1250 BLAKE             2850
ALLEN              1600 BLAKE             2850
MILLER             1300 CLARK             2450
ADAMS              1100 SCOTT             3000
CLARK              2450 KING              5000
BLAKE              2850 KING              5000
JONES              2975 KING              5000
SMITH               800 FORD              3000

11 rows selected.

SQL>