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 distinct types of artworks that are nominated in festivals in 2007.
assistant
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
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
human
How many artworks are there?
assistant
SELECT count(*) FROM artwork
human
List the most common type of artworks.
assistant
SELECT TYPE FROM artwork GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
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 ;