SQL Exercises: Details of employees who manage a department
SQL SUBQUERY: Exercise-33 with Solution
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
Sample table: departments
Sample Solution:
SELECT *
FROM employees
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.
SQL: Tips of the Day
What's the difference between VARCHAR and CHAR?
VARCHAR is variable-length.
CHAR is fixed length.
If your content is a fixed size, you'll get better performance with CHAR.
Ref: https://bit.ly/3wl3ram
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook