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 all 200 meter and 300 meter results of swimmers with nationality 'Australia'.
assistant
SELECT meter_200 , meter_300 FROM swimmer WHERE nationality = 'Australia'
human
Find the names of stadiums that the most swimmers have been to.
assistant
SELECT t3.name FROM record AS t1 JOIN event AS t2 ON t1.event_id = t2.id JOIN stadium AS t3 ON t3.id = t2.stadium_id GROUP BY t2.stadium_id ORDER BY count(*) DESC LIMIT 1
human
Find all details for each swimmer.
assistant
SELECT * FROM swimmer
human
Find the names of the swimmers who have no record.
assistant
SELECT name FROM swimmer WHERE id NOT IN (SELECT swimmer_id FROM record)
human
List countries that have more than one swimmer.
assistant
SELECT nationality , count(*) FROM swimmer GROUP BY nationality HAVING count(*) > 1
human
How many different countries are all the swimmers from?
assistant
SELECT COUNT( DISTINCT nationality ) FROM swimmer ;