SQL Exercise: List employees not in the department 1001 but in 3001

SQL subqueries on employee Database: Exercise-55 with Solution

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

55. From the following table, write a SQL query to find those employees who do not work in the department 1001 but work in the same designation and salary as the employees in department 3001. Return employee name, job name and salary.

Sample table: employees

Sample Solution:

SELECT emp_name,
       job_name,
       salary
FROM employees
WHERE dep_id != 1001
  AND job_name IN
    (SELECT job_name
     FROM employees
     WHERE dep_id = 3001)
  AND salary IN
    (SELECT salary
     FROM employees
     WHERE dep_id = 3001);

Sample Output:

 emp_name | job_name | salary
----------+----------+---------
 BLAZE    | MANAGER  | 2750.00
 ADELYN   | SALESMAN | 1700.00
 WADE     | SALESMAN | 1350.00
 MADDEN   | SALESMAN | 1350.00
 TUCKER   | SALESMAN | 1600.00
 JULIUS   | CLERK    | 1050.00
(6 rows)

Explanation:

The said query in SQL that selects the name of employees, their job titles, and salaries if they are not in department 1001 and their job title and salary match those of employees in department 3001.

The WHERE clause filters the results based on certain conditions. 1). it selects only those rows where the dep_id is not 1001, 2). the job_name is in a subquery that selects job_names from employees in department 3001, 3). and the salary is in a subquery that selects salaries from employees in department 3001.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees who earn a commission and maximum salary.
Next SQL Exercise: List SALESMAN who are earning maximum net salary.