SQL Subquery Exercises: Display the full name of manager who is supervising 4 or more employees

SQL SUBQUERY: Exercise-50 with Solution

From the following table, write a SQL query to find those managers who supervise four or more employees. Return manager name, department ID.

Sample table: employees

Sample Solution:

SELECT first_name || ' ' || last_name AS Manager_name,department_id
	FROM employees 
		WHERE employee_id IN 
		(SELECT manager_id 
		FROM employees 
			GROUP BY manager_id 
			HAVING COUNT(*)>=4);

Sample Output:

manager_name	department_id
Steven King		90
Neena Kochhar		90
Alexander Hunold	60
Nancy Greenberg		100
Den Raphaely		30
Matthew Weiss		50
Adam Fripp		50
Payam Kaufling		50
Shanta Vollman		50
Kevin Mourgos		50
John Russell		80
Karen Partners		80
Alberto Errazuriz	80
Gerald Cambrault	80
Eleni Zlotkey		80

Pictorial Presentation:

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find those departments where starting salary is at least 8000. Return all the fields of departments.
Next: From the following table, write a SQL query to find those employees who worked as a ‘Sales Representative’ in the past. Return all the fields of jobs.