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 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
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
List the nominees that have been nominated more than two musicals.
assistant
SELECT Nominee FROM musical GROUP BY Nominee HAVING COUNT(*) > 2
human
How many musicals has each nominee been nominated for?
assistant
SELECT Nominee , COUNT( * ) FROM musical GROUP BY Nominee ;