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 the swimmers who have no record.
assistant
SELECT name FROM swimmer WHERE id NOT IN (SELECT swimmer_id FROM record)
human
Find the name of the swimmer who has the most records.
assistant
SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id = t2.swimmer_id GROUP BY t2.swimmer_id ORDER BY count(*) DESC LIMIT 1
human
Find all details for each swimmer.
assistant
SELECT * FROM swimmer
human
Find the name of the swimmer who has at least 2 records.
assistant
SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id = t2.swimmer_id GROUP BY t2.swimmer_id HAVING count(*) >= 2
human
How many different countries are all the swimmers from?
assistant
SELECT count(DISTINCT nationality) FROM swimmer
human
List countries that have more than one swimmer.
assistant
SELECT nationality , COUNT( * ) FROM swimmer GROUP BY nationality HAVING COUNT( * ) > 1 ;