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
How many artworks are there?
assistant
SELECT count(*) FROM artwork
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)
human
Please show different types of artworks with the corresponding number of artworks of each type.
assistant
SELECT TYPE , COUNT(*) FROM artwork GROUP BY TYPE
human
List the most common type of artworks.
assistant
SELECT TYPE FROM artwork GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
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 in ascending alphabetical order.
assistant
SELECT Name FROM artwork ORDER BY Name ASC ;