NUHMAN.com

📋 Problem Description:

You are provided with an Employee table containing information about employees in a company. Each employee may or may not have a manager. Your task is to find the names of managers who directly manage at least five employees.

🧾 Table Schema:

🧑‍💼 Employee Table:

sql

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+

• id is the primary key — a unique identifier for each employee.
• managerId is a foreign key referencing the id of another employee (i.e., their manager).
• If managerId is NULL, the employee has no manager.
• No employee is their own manager.

🧠 Objective:

Write a SQL query to retrieve the names of managers who have at least 5 direct reports (i.e., at least 5 employees list them as their managerId).

The result can be returned in any order.

🧪 Example Input:

Employee table:

sql

+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | NULL      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+

✅ Expected Output:

sql

+------+
| name |
+------+
| John |
+------+

Explanation:

• John (ID 101) is the manager of Dan, James, Amy, Anne, and Ron — that's 5 direct reports.
• Therefore, John is the only manager who qualifies.

🧾 SQL Solution:

sql

SELECT name
FROM Employee
WHERE id IN (
    SELECT managerId
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(id) >= 5
);

🔍 Explanation:

1. Subquery:

sql

SELECT managerId
FROM Employee
GROUP BY managerId
HAVING COUNT(id) >= 5

• This groups employees by managerId and counts how many report to each.
• Only includes managers with 5 or more direct reports.

1. Main Query:

sql

SELECT name
FROM Employee
WHERE id IN (...)

• This gets the names of the managers whose id appears in the list from the subquery.

🧠 Alternate Solution (Using JOIN):

sql

SELECT e.name
FROM Employee e
JOIN (
    SELECT managerId
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(*) >= 5
) m ON e.id = m.managerId;

This version uses a JOIN to link manager IDs back to their names.