SQL Exercise: Employees who earn more than their managers

SQL subqueries on employee Database: Exercise-74 with Solution

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

74. From the following table, write a SQL query to find those employees whose salaries are greater than the salaries of their managers. 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;

OR

SELECT *
FROM employees e,
  (SELECT *
   FROM employees
   WHERE emp_id IN
       (SELECT manager_id
        FROM employees)) a
WHERE e.salary >a.salary
  AND e.manager_id = a.emp_id;

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
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+----------+------------+------------+---------+-----------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001 |  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |		|   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001 |  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |		|   2001
(2 rows)

Explanation:

According to first example the said query in SQL that retrieves all employees from the 'employees' table who have a higher salary than their respective manager.

The query joins the 'employees' and 'managers' tables, based on the the manager_id and the emp_id columns.

The WHERE clause includes those employees whose salary is greater than the salary of their manager.

According to second example the said query in SQL that retrieves all employees from the 'employees' table who have a higher salary than their respective manager.

The inner subquery selects the manager_id column from the 'employees' table, which represents the emp_id of employees who are managers.

The WHERE clause in the main query filters the results to include only those employees whose salary is greater than the salary of their respective manager, and whose manager_id is equal to the emp_id of the manager in the derived table which achieves by two nested subqueries containing all employees who are managers.

The query joins the 'employees' and the derived tables based on the manager_id and emp_id columns.

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: Employees, net pay is greater or equal than any other.
Next SQL Exercise: Maximum salary for each job name except for PRESIDENT.