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
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 name of artworks in ascending alphabetical order.
assistant
SELECT Name FROM artwork ORDER BY Name ASC
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 festivals that have nominated artworks of type 'Program Talent Show'.
assistant
SELECT 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 WHERE T2.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)
human
List the name of artworks whose type is not 'Program Talent Show'.
assistant
SELECT Name FROM artwork WHERE TYPE != "Program Talent Show" ;