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 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 artists do we have?
assistant
SELECT count(*) FROM artist
human
How many exhibition are there in year 2005 or after?
assistant
SELECT count(*) FROM exhibition WHERE YEAR >= 2005
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
What are all distinct country for artists?
assistant
SELECT DISTINCT country 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 ;