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 me the ids of the festivals.
assistant
SELECT festival_id FROM festival_detail;
human
Show me the names as well.
assistant
SELECT festival_id , festival_name FROM festival_detail;
human
Add in the artworks nominated as well.
assistant
SELECT t3.festival_id , t3.festival_name , t1.name FROM artwork AS t1 JOIN nomination AS t2 ON t1.artwork_id = t2.artwork_id JOIN festival_detail AS t3 ON t2.festival_id = t3.festival_id;
human
Instead of showing the artworks nominated, show me the number of nominated artworks for each of these festivals.
assistant
SELECT T1.Festival_ID , T3.Festival_Name , COUNT( * ) 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 ;