SQL Exercise: List managers on lower salaries than their employees

Last update on December 20 2024 11:41:11 (UTC/GMT +8 hours)

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

46. From the following table, write a SQL query to find those managers who receive less salary then the employees work under them. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees w
WHERE salary < ANY
    (SELECT salary
     FROM employees
     WHERE w.emp_id=manager_id);

OR

SELECT DISTINCT m.emp_name,
                m.salary
FROM employees w,
     employees m
WHERE w.manager_id = m.emp_id
  AND w.salary>m.salary;

OR

SELECT *
FROM employees w
WHERE emp_id IN
    (SELECT manager_id
     FROM employees
     WHERE w.salary<salary);

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(1 row)

Explanation:

The said query in SQL that retrieves all employees from the employees table who have a salary lower than at least one of their managers.

The WHERE clause compares the salary of each employee with the salaries of their managers obtained from a subquery. The subquery that retrieves the salaries of all employees who have a manager with an emp_id equal to the manager_id of the employee.

The ANY operator in the subquery compares the salary of the employee with each of the salaries in the subquery.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List the name of the managers with the most employees.
Next SQL Exercise: List all employees who are subordinates to Blaze.