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 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
How many artists do we have?
assistant
SELECT count(*) FROM artist
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
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
Show names for artists without any exhibition.
assistant
SELECT name FROM artist WHERE artist_id NOT IN (SELECT artist_id FROM exhibition)
human
What are the names of artists who did not 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 ;