SQL Exercise: Salary is less than manager but more than colleagues

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)

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table who have a manager, and whose salary is less than their manager's salary, but greater than any salary of their peers who are managed by the same manager.

The 'employees' table joins with itself based on the "manager_id" and the "emp_id" columns.

The WHERE clause ensures that the employee's salary is less than their manager's salary.

The query is also checks whether the employee's salary is greater than any salary of employees whose "emp_id" is in the list of "manager_id"s in the 'employees' table.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Managers salary exceeds the average of their employees.
Next SQL Exercise: List average salary of employees in department wise.