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
Please show the nominee who has been nominated the greatest number of times.
assistant
SELECT Nominee FROM musical GROUP BY Nominee ORDER BY COUNT(*) DESC LIMIT 1
human
List the nominees that have been nominated more than two musicals.
assistant
SELECT Nominee FROM musical GROUP BY Nominee HAVING COUNT(*) > 2
human
What are the distinct nominees of the musicals with the award that is not 'Tony Award'?
assistant
SELECT DISTINCT Nominee FROM musical WHERE Award != 'Tony Award'
human
What are the names of musicals with nominee 'Bob Fosse'?
assistant
SELECT Name FROM musical WHERE Nominee = 'Bob Fosse'
human
Who are the nominees who were nominated for either of the Bob Fosse or Cleavant Derricks awards?
assistant
SELECT Nominee FROM musical WHERE Award = "Tony Award" OR Award = "Cleavant Derricks" ;