human
Schema:
swimmer (ID, name, Nationality, meter_100, meter_200, meter_300, meter_400, meter_500, meter_600, meter_700, Time)
stadium (ID, name, Capacity, City, Country, Opening_year)
event (ID, Name, Stadium_ID, Year)
record (ID, Result, Swimmer_ID, Event_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
How many stadiums does each country have?
assistant
SELECT country , count(*) FROM stadium GROUP BY country
human
What is the name of the stadium which held the most events?
assistant
SELECT t1.name FROM stadium AS t1 JOIN event AS t2 ON t1.id = t2.stadium_id GROUP BY t2.stadium_id ORDER BY count(*) DESC LIMIT 1
human
How many stadiums are there?
assistant
SELECT count(*) FROM stadium
human
What is the average capacity of the stadiums that were opened in year 2005?
assistant
SELECT avg(capacity) FROM stadium WHERE opening_year = 2005
human
Which countries do not have a stadium that was opened after 2006?
assistant
SELECT country FROM stadium EXCEPT SELECT country FROM stadium WHERE opening_year > 2006
human
How many cities have a stadium that was opened before the year of 2006?
assistant
SELECT COUNT( DISTINCT city ) FROM stadium WHERE opening_year < 2006 ;