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 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 actors in descending order of the year their musical is awarded.
assistant
SELECT T1.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID = T2.Musical_ID ORDER BY T2.Year DESC
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 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
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 actors that have appeared in musical with name 'The Phantom of the Opera'.
assistant
SELECT T1.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID = T2.Musical_ID WHERE T2.Name = "The Phantom of the Opera" ;