Display those employees whose salary is less than his manger but more than the salary of any other manager using ORACLE SQL query

Question

Display those employees whose salary is less than his manager but more than the salary of any other manager using ORACLE SQL query

Answer 1

WITH mgr_salaries ( empno, ename, esal, mgr, mgr_salary ) AS (
  SELECT empno,
         ename,
         salary,
         mgr,
         PRIOR salary
  FROM   Employees
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr
)
SELECT *
FROM   mgr_salaries
WHERE  salary < mgr_salary
AND    NOT EXISTS (
         SELECT 1
         FROM   mgr_salaries x
         WHERE  x.mgr <> m.mgr
         AND    x.mgr_salary > m.salary
       );