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
List the most common type of artworks.
assistant
SELECT TYPE FROM artwork GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
human
List the name of artworks in ascending alphabetical order.
assistant
SELECT Name FROM artwork ORDER BY Name ASC
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
Show the names of artworks in ascending order of the year they are nominated in.
assistant
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
human
List the name of artworks whose type is not 'Program Talent Show'.
assistant
SELECT Name FROM artwork WHERE TYPE != 'Program Talent Show'
human
List the name of artworks that are not nominated.
assistant
SELECT Name FROM Artwork WHERE Artwork_ID NOT IN ( SELECT Artwork_ID FROM nomination ) ;