SQL Exercise: List the managers, not working under the PRESIDENT

SQL subqueries on employee Database: Exercise-39 with Solution

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

39. From the following table, write a SQL query to find those managers who are not working under the PRESIDENT. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE emp_id IN
    (SELECT manager_id
     FROM employees)
  AND manager_id NOT IN
    (SELECT emp_id
     FROM employees
     WHERE job_name = 'PRESIDENT');

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
(2 rows)

Explanation:

The said query in SQL that includes only those employees from the 'employees' table who are managers and whose manager is not the company's president.

The subquery in the WHERE clause return the emp_id values of all employees who are managers that comes to select all manager_id values from the 'employees' table.

The second subquery selects all emp_id values from the 'employees' table where the job_name is 'PRESIDENT'.

The "NOT IN" operator in the second subquery excludes all employees who have the job_name of 'PRESIDENT' from the final results.

Practice Online

Structure of employee Structure:

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

Previous SQL Exercise: Find out the least 5 earners of the company.
Next SQL Exercise: List employees whose netpay is more than others.