SQL Exercises: Manager who is supervising 4 or more employees

Last update on March 12 2025 12:11:49 (UTC/GMT +8 hours)

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

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| 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 |
....................
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Sample Solution:

-- Selecting a concatenated string of 'first_name' and 'last_name' as 'Manager_name', and the 'department_id' from the 'employees' table
SELECT first_name || ' ' || last_name AS Manager_name, department_id

-- Filtering rows based on the condition that the 'employee_id' is in the result set of a subquery
FROM employees 

-- Subquery to find 'manager_id' values where the count of occurrences is greater than or equal to 4 in the 'employees' table
WHERE employee_id IN 
	( SELECT manager_id 
      FROM employees 

      -- Grouping 'manager_id' values and filtering based on the count of occurrences
      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

Code Explanation:

The said query in SQL that selects the names of employees who are managers and have at least 4 employees reporting to them, along with the department IDs of their respective departments. The "||" symbol concatenates the first name, a space, and the last name of each employee to create the Manager_name field. The query first uses a subquery to identify the manager IDs that meet the condition of having at least 4 direct reports.

Visual Presentation:

Alternative Solutions:

Using EXISTS:

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

Using JOINs:

SELECT DISTINCT e1.first_name || ' ' || e1.last_name AS Manager_name, e1.department_id
FROM employees e1
JOIN employees e2 ON e1.employee_id = e2.manager_id
GROUP BY e1.employee_id, e1.first_name, e1.last_name, e1.department_id
HAVING COUNT(*) >= 4;

Using a Scalar Subquery in the WHERE Clause:

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

Practice Online

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

Previous SQL Exercise: Departments which starting salary is at least 8000.
Next SQL Exercise: Employees worked as a Sales Representative in the past.