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 theme for exhibitions with both records of an attendance below 100 and above 500.
assistant
SELECT T2.theme FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id = T2.exhibition_id WHERE T1.attendance < 100 INTERSECT SELECT T2.theme FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id = T2.exhibition_id WHERE T1.attendance > 500
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
What is the name and country for the artist with most number of exhibitions?
assistant
SELECT T2.name , T2.country FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id = T2.artist_id GROUP BY T1.artist_id ORDER BY count(*) DESC LIMIT 1
human
How many exhibitions have a attendance more than 100 or have a ticket price below 10?
assistant
SELECT count(*) FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id = T2.exhibition_id WHERE T1.attendance > 100 OR T2.ticket_price < 10
human
What is the theme, date, and attendance for the exhibition in year 2004?
assistant
SELECT T2.theme , T1.date , T1.attendance FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id = T2.exhibition_id WHERE T2.year = 2004
human
How many exhibition are there in year 2005 or after?
assistant
SELECT COUNT( * ) FROM exhibition WHERE YEAR >= 2005 ;