SQL Exercises: Details of employees who manage a department
From the following table, write a SQL query to find those employees who manage a department. Return all the fields of employees table.
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 all columns (*) from the 'employees' table
SELECT *
-- Filtering rows based on the condition that the 'employee_id' is equal to any 'manager_id' in the result set of a subquery
FROM employees
-- Subquery to find 'manager_id' values from the 'departments' table
WHERE employee_id = ANY
(SELECT manager_id FROM departments );
Sample Output:
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 103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 IT_PROG 9000.00 0.00 102 60 108 Nancy Greenberg NGREENBE515.124.4569 2002-08-17 FI_MGR 12000.00 0.00 101 100 114 Den Raphaely DRAPHEAL515.127.4561 2002-12-07 PU_MAN 11000.00 0.00 100 30 121 Adam Fripp AFRIPP 650.123.2234 2005-04-10 ST_MAN 8200.00 0.00 100 50 145 John Russell JRUSSEL 011.44.1344.4292682004-10-01 SA_MAN 14000.00 0.40 100 80 200 Jennifer Whalen JWHALEN 515.123.4444 2003-09-17 AD_ASST 4400.00 0.00 101 10 201 Michael Hartstein MHARTSTE515.123.5555 2004-02-17 MK_MAN 13000.00 0.00 100 20 203 Susan Mavris SMAVRIS 515.123.7777 2002-06-07 HR_REP 6500.00 0.00 101 40 204 Hermann Baer HBAER 515.123.8888 2002-06-07 PR_REP 10000.00 0.00 101 70 205 Shelley Higgins SHIGGINS515.123.8080 2002-06-07 AC_MGR 12000.00 0.00 101 110
Code Explanation:
The said query in SQL that retrieves all the data from the 'employees' table where the "employee_id" matches any of the "manager_id" values from the 'departments' table.
The subquery "( SELECT manager_id FROM departments )" retrieves the "manager_id" values from the 'departments' table, and the "ANY" operator checks if any of those values match the "employee_id" in the 'employees' table.
Visual Presentation:
Alternative Statements:
Using Subquery with IN:
SELECT *
FROM employees
WHERE employee_id IN
(
SELECT manager_id
FROM departments
WHERE manager_id IS NOT NULL
);
Using Subquery with EXISTS:
SELECT e.*
FROM employees e
WHERE EXISTS
(
SELECT 1
FROM departments d
WHERE d.manager_id = e.employee_id
);
Using a Join:
SELECT DISTINCT e.*
FROM employees e
JOIN departments d ON e.employee_id = d.manager_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: Find out the details of employees who are managers.
Next SQL Exercise: Salary earn by the employee which is maximum.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.