﻿ SQL: List managers on lower salaries than their employees

# SQL Exercise: List managers on lower salaries than their employees

## SQL subqueries on employee Database: Exercise-46 with Solution

[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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿