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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics