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 name and capacity of the stadium where the event named 'World Junior' happened.
assistant
SELECT t1.name , t1.capacity FROM stadium AS t1 JOIN event AS t2 ON t1.id = t2.stadium_id WHERE t2.name = 'World Junior'
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
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 events are there?
assistant
SELECT count(*) FROM event
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
List all the event names by year from the most recent to the oldest.
assistant
SELECT name FROM event ORDER BY YEAR DESC ;