human
Schema:
artist (Artist_ID, Name, Country, Year_Join, Age)
exhibition (Exhibition_ID, Year, Theme, Artist_ID, Ticket_Price)
exhibition_record (Exhibition_ID, Date, Attendance)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the average, minimum, and maximum ticket prices for exhibitions for all years before 2009.
assistant
SELECT avg(ticket_price) , min(ticket_price) , max(ticket_price) FROM exhibition WHERE YEAR < 2009
human
Show all artist names with an average exhibition attendance over 200.
assistant
SELECT T3.name FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id = T2.exhibition_id JOIN artist AS T3 ON T3.artist_id = T2.artist_id GROUP BY T3.artist_id HAVING avg(T1.attendance) > 200
human
Show all artist names and the number of exhibitions for each artist.
assistant
SELECT T2.name , count(*) FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id = T2.artist_id GROUP BY T1.artist_id
human
Show theme and year for all exhibitions in an descending order of ticket price.
assistant
SELECT theme , YEAR FROM exhibition ORDER BY ticket_price DESC
human
Show theme and year for all exhibitions with ticket prices lower than 15.
assistant
SELECT theme , YEAR FROM exhibition WHERE ticket_price < 15
human
Return the names of artists and the themes of their exhibitions that had a ticket price higher than average.
assistant
SELECT T1.theme , T2.name FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id = T2.artist_id WHERE T1.ticket_price > ( SELECT avg ( ticket_price ) FROM exhibition ) ;