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
Show the ids and names of festivals that have at least two nominations for artworks.
assistant
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
human
In which year are there festivals both inside the 'United States' and outside the 'United States'?
assistant
SELECT YEAR FROM festival_detail WHERE LOCATION = 'United States' INTERSECT SELECT YEAR FROM festival_detail WHERE LOCATION != 'United States'
human
What are the names of festivals held in year 2007?
assistant
SELECT Festival_Name FROM festival_detail WHERE YEAR = 2007
human
What are the names and locations of festivals?
assistant
SELECT Festival_Name , LOCATION FROM festival_detail
human
Show the names of the three most recent festivals.
assistant
SELECT Festival_Name FROM festival_detail ORDER BY YEAR DESC LIMIT 3
human
List the year in which there are more than one festivals.
assistant
SELECT YEAR FROM festival_detail GROUP BY YEAR HAVING COUNT( * ) > 1 ;