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
Show different nominees and the number of musicals they have been nominated.
assistant
SELECT Nominee , COUNT(*) FROM musical GROUP BY Nominee
human
List the nominees that have been nominated more than two musicals.
assistant
SELECT Nominee FROM musical GROUP BY Nominee HAVING COUNT(*) > 2
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 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 actors ordered descending by the year in which their musical was 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 ;