SQL Exercise: Personnel with department ID 2001 and ID 1001

SQL subqueries on employee Database: Exercise-10 with Solution

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

10. From the following table, write a SQL query to find those employees of department ID 2001 and whose designation is same as of the designation of department ID 1001. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT *
FROM employees e,
     department d
WHERE d.dep_id = 2001
  AND e.dep_id = d.dep_id
  AND e.job_name IN
    (SELECT e.job_name
     FROM employees e,
          department d
     WHERE e.dep_id = d.dep_id
       AND d.dep_id =1001);

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | dep_id | dep_name | dep_location
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+--------------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
(3 rows)

Explanation:

The given query in SQL that selects all employees who belong to department ID 2001 and whose job_name is also found in the job_names of employees who belong to department ID 1001 from the 'employees' and 'department' tables.

The WHERE clause in the main query filters the results to only include rows where the department ID is 2001 and the employee's department ID is also 2001 and the job_name of the employee must be found in the job_names of employees who belong to department ID 1001 which is determined by the subquery.

The subquery selects the job_name of all employees who belong to department ID 1001 by joining the 'employees' and 'department' tables.

The subquery is used in the main query as a condition for the 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: List the employees who are senior to ADELYN.
Next SQL Exercise: List the result in descending order of salary.