human
Schema:
festival_detail (Festival_ID, Festival_Name, Chair_Name, Location, Year, Num_of_Audience)
artwork (Artwork_ID, Type, Name)
nomination (Artwork_ID, Festival_ID, Result)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the names of festivals held in year 2007?
assistant
SELECT Festival_Name FROM festival_detail WHERE YEAR = 2007
human
Show the id, name of each festival and the number of artworks it has nominated.
assistant
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
human
What is the location of the festival with the largest number of audience?
assistant
SELECT LOCATION FROM festival_detail ORDER BY Num_of_Audience DESC LIMIT 1
human
What are the total number of the audiences who visited any of the festivals?
assistant
SELECT sum(Num_of_Audience) FROM festival_detail
human
What is the average number of audience for festivals?
assistant
SELECT avg(Num_of_Audience) FROM festival_detail
human
Show the number of audience in year 2008 or 2010.
assistant
SELECT Num_of_Audience FROM festival_detail WHERE YEAR = 2008 OR YEAR = 2010 ;