Database: musical

CREATE TABLE "musical" (
"Musical_ID" int,
"Name" text,
"Year" int,
"Award" text,
"Category" text,
"Nominee" text,
"Result" text,
PRIMARY KEY ("Musical_ID")
);
CREATE TABLE "actor" (
"Actor_ID" int,
"Name" text,
"Musical_ID" int,
"Character" text,
"Duration" text,
"age" int,
PRIMARY KEY ("Actor_ID"),
FOREIGN KEY ("Musical_ID") REFERENCES "actor"("Actor_ID")
);

Question: How many actors are there?
SQL: SELECT count(*) FROM actor

Question: Count the number of actors.
SQL: SELECT count(*) FROM actor

Question: List the name of actors in ascending alphabetical order.
SQL: SELECT Name FROM actor ORDER BY Name ASC

Question: What are the names of actors, ordered alphabetically?
SQL: SELECT Name FROM actor ORDER BY Name ASC

Question: What are the characters and duration of actors?
SQL: SELECT Character ,  Duration FROM actor

Question: Return the characters and durations for each actor.
SQL: SELECT Character ,  Duration FROM actor

Question: List the name of actors whose age is not 20.
SQL: SELECT Name FROM actor WHERE Age != 20

Question: What are the names of actors who are not 20 years old?
SQL: SELECT Name FROM actor WHERE Age != 20

Question: What are the characters of actors in descending order of age?
SQL: SELECT Character FROM actor ORDER BY age DESC

Question: Return the characters for actors, ordered by age descending.
SQL: SELECT Character FROM actor ORDER BY age DESC

Question: What is the duration of the oldest actor?
SQL: SELECT Duration FROM actor ORDER BY Age DESC LIMIT 1

Question: Return the duration of the actor with the greatest age.
SQL: SELECT Duration FROM actor ORDER BY Age DESC LIMIT 1

Question: What are the names of musicals with nominee "Bob Fosse"?
SQL: SELECT Name FROM musical WHERE Nominee  =  "Bob Fosse"

Question: Return the names of musicals who have the nominee Bob Fosse.
SQL: SELECT Name FROM musical WHERE Nominee  =  "Bob Fosse"

Question: What are the distinct nominees of the musicals with the award that is not "Tony Award"?
SQL: SELECT DISTINCT Nominee FROM musical WHERE Award != "Tony Award"

Question: Return the different nominees of musicals that have an award that is not the Tony Award.
SQL: SELECT DISTINCT Nominee FROM musical WHERE Award != "Tony Award"

Question: Show names of actors and names of musicals they are in.
SQL: SELECT T1.Name ,  T2.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID

Question: What are the names of actors and the musicals that they are in?
SQL: SELECT T1.Name ,  T2.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID

Question: Show names of actors that have appeared in musical with name "The Phantom of the Opera".
SQL: 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"

Question: What are the names of actors who have been in the musical titled The Phantom of the Opera?
SQL: 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"

Question: Show names of actors in descending order of the year their musical is awarded.
SQL: SELECT T1.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID ORDER BY T2.Year DESC

Question: What are the names of actors ordered descending by the year in which their musical was awarded?
SQL: SELECT T1.Name FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID ORDER BY T2.Year DESC

Question: Show names of musicals and the number of actors who have appeared in the musicals.
SQL: SELECT T2.Name ,  COUNT(*) FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID GROUP BY T1.Musical_ID

Question: How many actors have appeared in each musical?
SQL: SELECT T2.Name ,  COUNT(*) FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID GROUP BY T1.Musical_ID

Question: Show names of musicals which have at least three actors.
SQL: 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

Question: What are the names of musicals who have at 3 or more actors?
SQL: 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

Question: Show different nominees and the number of musicals they have been nominated.
SQL: SELECT Nominee ,  COUNT(*) FROM musical GROUP BY Nominee

Question: How many musicals has each nominee been nominated for?
SQL: SELECT Nominee ,  COUNT(*) FROM musical GROUP BY Nominee

Question: Please show the nominee who has been nominated the greatest number of times.
SQL: SELECT Nominee FROM musical GROUP BY Nominee ORDER BY COUNT(*) DESC LIMIT 1

Question: Who is the nominee who has been nominated for the most musicals?
SQL: SELECT Nominee FROM musical GROUP BY Nominee ORDER BY COUNT(*) DESC LIMIT 1

Question: List the most common result of the musicals.
SQL: SELECT RESULT FROM musical GROUP BY RESULT ORDER BY COUNT(*) DESC LIMIT 1

Question: Return the most frequent result across all musicals.
SQL: SELECT RESULT FROM musical GROUP BY RESULT ORDER BY COUNT(*) DESC LIMIT 1

Question: List the nominees that have been nominated more than two musicals.
SQL: SELECT Nominee FROM musical GROUP BY Nominee HAVING COUNT(*)  >  2

Question: Who are the nominees who have been nominated more than two times?
SQL: SELECT Nominee FROM musical GROUP BY Nominee HAVING COUNT(*)  >  2

Question: List the name of musicals that do not have actors.
SQL: SELECT Name FROM musical WHERE Musical_ID NOT IN (SELECT Musical_ID FROM actor)

Question: What are the names of musicals who have no actors?
SQL: SELECT Name FROM musical WHERE Musical_ID NOT IN (SELECT Musical_ID FROM actor)

Question: Show the nominees that have nominated musicals for both "Tony Award" and "Drama Desk Award".
SQL: SELECT Nominee FROM musical WHERE Award  =  "Tony Award" INTERSECT SELECT Nominee FROM musical WHERE Award  =  "Drama Desk Award"

Question: Who are the nominees who have been nominated for both a Tony Award and a Drama Desk Award?
SQL: SELECT Nominee FROM musical WHERE Award  =  "Tony Award" INTERSECT SELECT Nominee FROM musical WHERE Award  =  "Drama Desk Award"

Question: Show the musical nominee with award "Bob Fosse" or "Cleavant Derricks".
SQL: SELECT Nominee FROM musical WHERE Award  =  "Tony Award" OR Award  =  "Cleavant Derricks"

Question: Who are the nominees who were nominated for either of the Bob Fosse or Cleavant Derricks awards?
SQL: SELECT Nominee FROM musical WHERE Award  =  "Tony Award" OR Award  =  "Cleavant Derricks"