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 the nominees that have nominated musicals for both 'Tony Award' and 'Drama Desk Award'.
assistant
SELECT Nominee FROM musical WHERE Award = 'Tony Award' INTERSECT SELECT Nominee FROM musical WHERE Award = 'Drama Desk Award'
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 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 different nominees and the number of musicals they have been nominated.
assistant
SELECT Nominee , COUNT(*) FROM musical GROUP BY Nominee
human
What are the names of musicals with nominee 'Bob Fosse'?
assistant
SELECT Name FROM musical WHERE Nominee = 'Bob Fosse'
human
Show the musical nominee with award 'Bob Fosse' or 'Cleavant Derricks'.
assistant
SELECT Nominee FROM musical WHERE Award = "Tony Award" OR Award = "Cleavant Derricks" ;