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
List the most common result of the musicals.
assistant
SELECT RESULT FROM musical GROUP BY RESULT ORDER BY COUNT(*) DESC LIMIT 1
human
What are the names of musicals with nominee 'Bob Fosse'?
assistant
SELECT Name FROM musical WHERE Nominee = 'Bob Fosse'
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
Show names of musicals which have at least three 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
human
What are the names of musicals who have no actors?
assistant
SELECT Name FROM musical WHERE Musical_ID NOT IN ( SELECT Musical_ID FROM actor ) ;