SQL Exercise: Display the unique department of the employees

SQL subqueries on employee Database: Exercise-59 with Solution

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

59. From the following table, write a SQL query to find the unique department of the employees. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT *
FROM department
WHERE dep_id IN
    (SELECT DISTINCT dep_id
     FROM employees);

Sample Output:

 dep_id | dep_name  | dep_location
--------+-----------+--------------
   1001 | FINANCE   | SYDNEY
   2001 | AUDIT     | MELBOURNE
   3001 | MARKETING | PERTH
(3 rows)

Explanation:

The said query in SQL that retrieves all columns from the 'department' table for departments that have at least one employee associated with them.

The WHERE clause filters the results to include only those departments where the department ID is found in the list of department IDs associated with the 'employees' table obtained from a subquery. The subquery that selects distinct department IDs from the 'employees' table.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Department average salaries less than averages of all.
Next Exercise: List the details of the employees working at PERTH.