SQL Exercises: Departments that have one or more employees
From the following table, write a SQL query to find all those departments where at least one employee is employed. Return department name.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | ...... +---------------+----------------------+------------+-------------+
Sample Solution:
-- Selecting the 'department_name' column from the 'departments' table
SELECT department_name
-- Filtering rows based on the condition that the 'department_id' is in the result set of a subquery
FROM departments
-- Subquery to find distinct 'department_id' values from the 'employees' table
WHERE department_id IN
(SELECT DISTINCT(department_id)
FROM employees);
Sample Output:
department_name Administration Marketing Purchasing Human Resources Shipping IT Public Relations Sales Executive Finance Accounting
Code Explanation:
The said query in SQL that retrieves the names of departments from the 'departments' table based on the department IDs present in the 'employees' table.
The query starts by selecting the "department_name" column from the 'departments' table. The result is then filtered using a WHERE clause that specifies that only those departments should be returned whose IDs exist in the 'employees' table.
Visual Presentation:
Alternative Statements:
Alternative 1:
SELECT DISTINCT d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;
Alternative 2:
SELECT DISTINCT d.department_name
FROM departments d, employees e
WHERE d.department_id = e.department_id;
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: SalaryStatus column with a title HIGH and LOW.
Next SQL Exercise: Employees in departments in the United Kingdom.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.