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 all artist names who didn't have an exhibition in 2004.
assistant
SELECT name FROM artist EXCEPT SELECT T2.name FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id = T2.artist_id WHERE T1.year = 2004
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
How many exhibition are there in year 2005 or after?
assistant
SELECT count(*) FROM exhibition WHERE YEAR >= 2005
human
How many artists do we have?
assistant
SELECT count(*) FROM artist
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 has each artist had?
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 ;