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

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿