SQL subqueries on employee Database: Find out the employees whose salaries are greater than the salaries of 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)

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: From the following table, write a SQL query to find those employees whose net pay are higher than or equal to the salary of any other employee in the company. Return employee name, salary, and commission.
Next: From the following table, write a SQL query to find the maximum average salary drawn for each job except for PRESIDENT.