Description
Write a SQL query to find managers who have at least 5 direct reports. Return their names. **Output columns (in order):** name
Table:
employees| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
| manager_id | INT |
Examples
Input:
CREATE TABLE employees (id INT, name TEXT, manager_id INT); INSERT INTO employees VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 1), (5, 'Eve', 1), (6, 'Frank', 1);Output:
AliceExplanation:
Alice manages 5 people (Bob, Charlie, David, Eve, Frank), meeting the 5+ requirement.
Input:
CREATE TABLE employees (id INT, name TEXT, manager_id INT); INSERT INTO employees VALUES (1, 'Sarah', NULL), (2, 'Mike', NULL), (3, 'Tom', 1), (4, 'Lisa', 1), (5, 'John', 1), (6, 'Emma', 1), (7, 'Alex', 1), (8, 'Nina', 1), (9, 'Paul', 2), (10, 'Kate', 2), (11, 'Jim', 2);Output:
SarahExplanation:
Sarah manages 6 people (Tom, Lisa, John, Emma, Alex, Nina) which is 5 or more, so she qualifies. Mike only manages 3 people (Paul, Kate, Jim) which is less than 5, so he doesn't qualify.
Input:
CREATE TABLE employees (id INT, name TEXT, manager_id INT); INSERT INTO employees VALUES (1, 'Director', NULL), (2, 'Manager1', 1), (3, 'Manager2', 1), (4, 'Employee1', 2), (5, 'Employee2', 2), (6, 'Employee3', 2), (7, 'Employee4', 2), (8, 'Employee5', 2);Output:
Manager1Explanation:
Manager1 has exactly 5 direct reports (Employee1 through Employee5), meeting the minimum requirement. Director only manages 2 people (Manager1, Manager2) and Manager2 has no direct reports, so neither qualifies.
Constraints
- •
Use GROUP BY and HAVING