Managers with at Least 5 Direct Reports
Task: Find the names of managers with at least 5 direct reports.
Table: Employee
+-------------+--------------+
| Column Name | Type |
+-------------+--------------+
| id | INT |
| name | varchar(255) |
| department | varchar(255) |
| managerId | INT |
+-------------+--------------+Input: Employee table:
+-----+-------+------------+-----------+
| 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 |
+-----+-------+------------+-----------+Output:
+------+
| name |
+------+
| John |
+------+View Reference Solution & Explanation ▼
This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, and a `HAVING` clause to filter the aggregated groups.
SELECT e1.name FROM Employee e1 JOIN Employee e2 ON e1.id = e2.managerId GROUP BY e1.id HAVING COUNT(e2.id) >= 5;