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
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
How many artists are above age 46 and joined after 1990?
assistant
SELECT count(*) FROM artist WHERE age > 46 AND year_join > 1990
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 all artist names and the year joined who are not from United States.
assistant
SELECT name , year_join FROM artist WHERE country != 'United States'
human
Show all artist name, age, and country ordered by the yeared they joined.
assistant
SELECT name , age , country FROM artist ORDER BY Year_Join ;