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 which have never had any event.
assistant
SELECT name FROM stadium WHERE id NOT IN (SELECT stadium_id FROM event)
human
How many stadiums are there?
assistant
SELECT count(*) FROM stadium
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
List all the event names by year from the most recent to the oldest.
assistant
SELECT name FROM event ORDER BY YEAR DESC
human
What is the name of the event that happened in the most recent year?
assistant
SELECT name FROM event ORDER BY YEAR DESC LIMIT 1
human
How many events are there?
assistant
SELECT COUNT( * ) FROM event ;