Database: entertainment_awards

CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
);
CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
);
CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
);

Question: How many artworks are there?
SQL: SELECT count(*) FROM artwork

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

Question: List the name of artworks whose type is not "Program Talent Show".
SQL: SELECT Name FROM artwork WHERE TYPE != "Program Talent Show"

Question: What are the names and locations of festivals?
SQL: SELECT Festival_Name ,  LOCATION FROM festival_detail

Question: What are the names of the chairs of festivals, sorted in ascending order of the year held?
SQL: SELECT Chair_Name FROM festival_detail ORDER BY YEAR ASC

Question: What is the location of the festival with the largest number of audience?
SQL: SELECT LOCATION FROM festival_detail ORDER BY Num_of_Audience DESC LIMIT 1

Question: What are the names of festivals held in year 2007?
SQL: SELECT Festival_Name FROM festival_detail WHERE YEAR  =  2007

Question: What is the average number of audience for festivals?
SQL: SELECT avg(Num_of_Audience) FROM festival_detail

Question: Show the names of the three most recent festivals.
SQL: SELECT Festival_Name FROM festival_detail ORDER BY YEAR DESC LIMIT 3

Question: For each nomination, show the name of the artwork and name of the festival where it is nominated.
SQL: SELECT T2.Name ,  T3.Festival_Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID

Question: Show distinct types of artworks that are nominated in festivals in 2007.
SQL: SELECT DISTINCT T2.Type FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID WHERE T3.Year  =  2007

Question: Show the names of artworks in ascending order of the year they are nominated in.
SQL: SELECT T2.Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID ORDER BY T3.Year

Question: Show the names of festivals that have nominated artworks of type "Program Talent Show".
SQL: SELECT T3.Festival_Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID WHERE T2.Type  =  "Program Talent Show"

Question: Show the ids and names of festivals that have at least two nominations for artworks.
SQL: SELECT T1.Festival_ID ,  T3.Festival_Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID GROUP BY T1.Festival_ID HAVING COUNT(*)  >=  2

Question: Show the id, name of each festival and the number of artworks it has nominated.
SQL: SELECT T1.Festival_ID ,  T3.Festival_Name ,  COUNT(*) FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID GROUP BY T1.Festival_ID

Question: Please show different types of artworks with the corresponding number of artworks of each type.
SQL: SELECT TYPE ,  COUNT(*) FROM artwork GROUP BY TYPE

Question: List the most common type of artworks.
SQL: SELECT TYPE FROM artwork GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1

Question: List the year in which there are more than one festivals.
SQL: SELECT YEAR FROM festival_detail GROUP BY YEAR HAVING COUNT(*)  >  1

Question: List the name of artworks that are not nominated.
SQL: SELECT Name FROM Artwork WHERE Artwork_ID NOT IN (SELECT Artwork_ID FROM nomination)

Question: Show the number of audience in year 2008 or 2010.
SQL: SELECT Num_of_Audience FROM festival_detail WHERE YEAR  =  2008 OR YEAR  =  2010

Question: What are the total number of the audiences who visited any of the festivals?
SQL: SELECT sum(Num_of_Audience) FROM festival_detail

Question: In which year are there festivals both inside the 'United States' and outside the 'United States'?
SQL: SELECT YEAR FROM festival_detail WHERE LOCATION  =  'United States' INTERSECT SELECT YEAR FROM festival_detail WHERE LOCATION != 'United States'