SQL subqueries on employee Database: List those managers who are getting salary to less than the salary of his 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)

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 managers who handle maximum number of employees. Return managers name, number of employees.
Next: From the following table, write a SQL query to find those employees who are sub-ordinates of BLAZE. Return complete information about the employees.