w3resource

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:

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

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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

Ref: https://bit.ly/3cayyhJ