SQL Exercise: List any job of department ID 1001 not in ID 2001

SQL subqueries on employee Database: Exercise-17 with Solution

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

17. From the following table, write a SQL query to list any job of department ID 1001 which are not found in department ID 2001. Return job name.

Sample table: employees

Sample Solution:

SELECT e.job_name
FROM employees e
WHERE e.dep_id = 1001
  AND e.job_name NOT IN
    (SELECT job_name
     FROM employees
     WHERE dep_id =2001);

Sample Output:

 job_name
-----------
 PRESIDENT
(1 row)

Explanation:

The given query in SQL that selects all the job names of employees who work in the department with dep_id 1001, but not in the department with dep_id 2001.

The subquery in the WHERE clause that selects the job_name column from the employees table where the dep_id is equal to 2001. The subquery is then excludes all the job names from the result set returned by the subquery with the NOT IN operator.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees with common designation as SANDRINE or ADELYN.
Next SQL Exercise: Find the details of highest paid employee.