SQL subqueries on employee Database: List the employees whose salary is less than the salary of his manager but more than the salary of any other manager

SQL subqueries on employee Database: Exercise-36 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

36. From the following table, write a SQL query to find those employees whose salary is less than the salary of his manager but more than the salary of any other manager. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees w,
     employees m
WHERE w.manager_id = m.emp_id
  AND w.salary < m.salary
  AND w.salary > ANY
    (SELECT salary
     FROM employees
     WHERE emp_id IN
         (SELECT manager_id
          FROM employees));

OR

SELECT DISTINCT W.emp_id,
                W.emp_name,
                W.salary
FROM
  (SELECT w.emp_id,
          w.emp_name,
          w.salary
   FROM employees w,
        employees m
   WHERE w.manager_id = m.emp_id
     AND w.salary<m.salary) W,

  (SELECT *
   FROM employees
   WHERE emp_id IN
       (SELECT manager_id
        FROM employees)) A
WHERE W.salary > A.salary;

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission  | dep_id
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+-----------+------------+------------+---------+-------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |		  |   1001
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |             |   1001
(2 rows)

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those managers whose salary is more than the average salary of his employees. Return complete information about the employees.
Next: From the following table, write a SQL query to compute department wise average salary of employees. Return employee name, average salary, department ID as “Current Salary”.