Managers with at-least 5 Actors

      

Write an SQL query to identify all the actors who are managers for atleast 5 other actors from the table.

Input :-

Actors table :-
ID is the Primary Key column for the Actors table.

ID	Name	Category	ManagerID
1	Ramesh	A	None
2	Pruthvi	A	1
3	Satish	A	1
4	Shekar	A	1
5	Manju	A	1
6	Hardhik	B	1

Output :-

ActorName

Ramesh

Query :-

select a1.name ActorName from 
Actors a1, Actors a2
where a1.ID=a2.ManagerID
group by a2.ManagerID
having count(a2.ManagerID)>=5;

Explanation : - 

This query retrieves the names of actors from a table called "Actors". The table contains information about actors, including their names and IDs.

• The query involves a self-join on the "Actors" table, where two instances of the table are aliased as ‘a1’ and ‘a2’. The self-join is performed based on the condition that the ID of ‘a1’ matches the ManagerID of ‘a2’. This implies that ‘a1’ represents the actors, while ‘a2’ represents their managers.

• The ‘group by’ clause groups the results based on the ManagerID of ‘a2’, which means that actors are grouped according to their respective managers.

• The ‘having’ clause is used to filter the groups and include only those groups where the count of managers who have actors reporting to them (‘a2’) is greater than or equal to 5. In other words, this condition ensures that only managers who have at least five actors under their supervision are included in the result.

• Finally, the ‘select’ statement retrieves the names (represented by ‘a1.name’) of the actors who meet the specified criteria.

In summary, this query selects the names of actors whose managers have at least five actors under their supervision.