﻿ SQL - Details of employees who manage a department

# 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;
``````

## 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

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