Database: swimming

CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
);
CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
);
CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
);
CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
);

Question: How many events are there?
SQL: SELECT count(*) FROM event

Question: List all the event names by year from the most recent to the oldest.
SQL: SELECT name FROM event ORDER BY YEAR DESC

Question: What is the name of the event that happened in the most recent year?
SQL: SELECT name FROM event ORDER BY YEAR DESC LIMIT 1

Question: How many stadiums are there?
SQL: SELECT count(*) FROM stadium

Question: Find the name of the stadium that has the maximum capacity.
SQL: SELECT name FROM stadium ORDER BY capacity DESC LIMIT 1

Question: Find the names of stadiums whose capacity is smaller than the average capacity.
SQL: SELECT name FROM stadium WHERE capacity  <  (SELECT avg(capacity) FROM stadium)

Question: Find the country that has the most stadiums.
SQL: SELECT country FROM stadium GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: Which country has at most 3 stadiums listed?
SQL: SELECT country FROM stadium GROUP BY country HAVING count(*)  <=  3

Question: Which country has both stadiums with capacity greater than 60000 and stadiums with capacity less than 50000?
SQL: SELECT country FROM stadium WHERE capacity  >  60000 INTERSECT SELECT country FROM stadium WHERE capacity  <  50000

Question: How many cities have a stadium that was opened before the year of 2006?
SQL: SELECT count(DISTINCT city) FROM stadium WHERE opening_year  <  2006

Question: How many stadiums does each country have?
SQL: SELECT country ,  count(*) FROM stadium GROUP BY country

Question: Which countries do not have a stadium that was opened after 2006?
SQL: SELECT country FROM stadium EXCEPT SELECT country FROM stadium WHERE opening_year  >  2006

Question: How many stadiums are not in country "Russia"?
SQL: SELECT count(*) FROM stadium WHERE country != 'Russia'

Question: Find the names of all swimmers, sorted by their 100 meter scores in ascending order.
SQL: SELECT name FROM swimmer ORDER BY meter_100

Question: How many different countries are all the swimmers from?
SQL: SELECT count(DISTINCT nationality) FROM swimmer

Question: List countries that have more than one swimmer.
SQL: SELECT nationality ,  count(*) FROM swimmer GROUP BY nationality HAVING count(*)  >  1

Question: Find all 200 meter and 300 meter results of swimmers with nationality "Australia".
SQL: SELECT meter_200 ,  meter_300 FROM swimmer WHERE nationality  =  'Australia'

Question: Find the names of swimmers who has a result of "win".
SQL: SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win'

Question: What is the name of the stadium which held the most events?
SQL: 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

Question: Find the name and capacity of the stadium where the event named "World Junior" happened.
SQL: SELECT t1.name ,  t1.capacity FROM stadium AS t1 JOIN event AS t2 ON t1.id  =  t2.stadium_id WHERE t2.name  =  'World Junior'

Question: Find the names of stadiums which have never had any event.
SQL: SELECT name FROM stadium WHERE id NOT IN (SELECT stadium_id FROM event)

Question: Find the name of the swimmer who has the most records.
SQL: 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

Question: Find the name of the swimmer who has at least 2 records.
SQL: 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

Question: Find the name and nationality of the swimmer who has won (i.e., has a result of "win") more than 1 time.
SQL: SELECT t1.name ,  t1.nationality FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win' GROUP BY t2.swimmer_id HAVING count(*)  >  1

Question: Find the names of the swimmers who have no record.
SQL: SELECT name FROM swimmer WHERE id NOT IN (SELECT swimmer_id FROM record)

Question: Find the names of the swimmers who have both "win" and "loss" results in the record.
SQL: SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win' INTERSECT SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Loss'

Question: Find the names of stadiums that some Australian swimmers have been to.
SQL: SELECT t4.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id JOIN event AS t3 ON t2.event_id  =  t3.id JOIN stadium AS t4 ON t4.id  =  t3.stadium_id WHERE t1.nationality  =  'Australia'

Question: Find the names of stadiums that the most swimmers have been to.
SQL: 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

Question: Find all details for each swimmer.
SQL: SELECT * FROM swimmer

Question: What is the average capacity of the stadiums that were opened in year 2005?
SQL: SELECT avg(capacity) FROM stadium WHERE opening_year  =  2005