w3resource

SQL Exercise: Employees whose salary is greater than their managers


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

54. From the following table, write a SQL query to identify employees whose salaries are higher than their managers' salaries. Return employee name, job name, manager ID, salary, manager name, manager's salary.

Sample table: employees


Pictorial Presentation:

SQL exercises on employee Database: List the name, job name, manager id, salary, manager name, manager's salary for those employees whose salary is greater than the salary of their managers

Sample Solution:

SELECT w.emp_name,
       w.job_name,
       w.manager_id,
       w.salary,
       m.emp_name "Manager",
       m.emp_id,
       m.salary "Manager_Salary"
FROM employees w,
     employees m
WHERE w.manager_id = m.emp_id
  AND w.salary > m.salary;

Sample Output:

 emp_name | job_name | manager_id | salary  | Manager | emp_id | Manager_Salary
----------+----------+------------+---------+---------+--------+----------------
 SCARLET  | ANALYST  |      65646 | 3100.00 | JONAS   |  65646 |        2957.00
 FRANK    | ANALYST  |      65646 | 3100.00 | JONAS   |  65646 |        2957.00
(2 rows)

Explanation:

The given query in SQL that selects the employee name, job name, manager ID, salary, manager name aliased as "Manager", manager ID, and manager salary aliased as "Manager_Salary" for all employees from the employees table.

The query is using a self-join to combine data from the employees table with itself based on the manager_id column.

The WHERE clause filters those employees whose salary is greater than their manager's salary.

Go to:


PREV : Employees earning 60000 or not working as analysts.
NEXT : Employees whose salary is in a range and location PERTH.


Practice Online



Sample Database: employee

employee database structure

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.