Find the manager details where all the employees under the manager should have salary more than 1000

Im having the table with records like below

EMPNO   ENAME   JOB         MGR HIREDATE        SAL     COMM    DEPTNO
7369    SMITH   CLERK       7902    17-DEC-80   800  -  20
7499    ALLEN   SALESMAN    7698    20-FEB-81   1600    300     30
7521    WARD    SALESMAN    7698    22-FEB-81   1250    500     30
7566    JONES   MANAGER     7839    02-APR-81   2975     -      20
7654    MARTIN  SALESMAN    7698    28-SEP-81   1250    1400    30
7698    BLAKE   MANAGER     7839    01-MAY-81   2850     -      30
7782    CLARK   MANAGER     7839    09-JUN-81   2450     -      10
7788    SCOTT   ANALYST     7566    19-APR-87   3000     -      20
7839    KING    PRESIDENT    -      17-NOV-81   5000     -      10
7844    TURNER  SALESMAN    7698    08-SEP-81   1500     0      30
7876    ADAMS   CLERK       7788    23-MAY-87   1100     -      20
7900    JAMES   CLERK       7698    03-DEC-81   950  -   30 
7902    FORD    ANALYST     7566    03-DEC-81   3000     -      20
7934    MILLER  CLERK       7782    23-JAN-82   1300     -      10

and i need to get the manager details where the all the employees under the manager should have salary more than 1000

Answer

Here is a solution that uses a correlated subquery:

select e.*
from emp e
where (select min(e1.sal) from emp e1 where e1.mgr = e.empno) >= 1000

This ensures that:

• the selected employee is a manager (ie they manage at least one employee)
• all of the managees of the manager have a salary above (or equal to) 1000