SQL Exercise: List managers on lower salaries than their employees
[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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics