w3resource

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:

employee database structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/employee-database-exercise/sql-subqueries-exercise-employee-database-59.php