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
Find the names of stadiums whose capacity is smaller than the average capacity.
assistant
SELECT name FROM stadium WHERE capacity < (SELECT avg(capacity) FROM stadium)
human
How many stadiums are there?
assistant
SELECT count(*) FROM stadium
human
Which country has both stadiums with capacity greater than 60000 and stadiums with capacity less than 50000?
assistant
SELECT country FROM stadium WHERE capacity > 60000 INTERSECT SELECT country FROM stadium WHERE capacity < 50000
human
How many stadiums does each country have?
assistant
SELECT country , count(*) FROM stadium GROUP BY country
human
Which country has at most 3 stadiums listed?
assistant
SELECT country FROM stadium GROUP BY country HAVING count(*) <= 3
human
Find the country that has the most stadiums.
assistant
SELECT country FROM stadium GROUP BY country ORDER BY COUNT( * ) DESC LIMIT 1 ;