human
Schema:
musical (Musical_ID, Name, Year, Award, Category, Nominee, Result)
actor (Actor_ID, Name, Musical_ID, Character, Duration, age)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the names of musicals with nominee 'Bob Fosse'?
assistant
SELECT Name FROM musical WHERE Nominee = 'Bob Fosse'
human
List the nominees that have been nominated more than two musicals.
assistant
SELECT Nominee FROM musical GROUP BY Nominee HAVING COUNT(*) > 2
human
List the name of musicals that do not have actors.
assistant
SELECT Name FROM musical WHERE Musical_ID NOT IN (SELECT Musical_ID FROM actor)
human
Show names of musicals and the number of actors who have appeared in the musicals.
assistant
SELECT T2.Name , COUNT(*) FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID = T2.Musical_ID GROUP BY T1.Musical_ID
human
Show names of actors and names of musicals they are in.
assistant
SELECT T1.Name , T2.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID = T2.Musical_ID
human
What are the names of musicals who have at 3 or more actors?
assistant
SELECT T2.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID = T2.Musical_ID GROUP BY T1.Musical_ID HAVING COUNT( * ) >= 3 ;