Database: aircraft

CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
);
CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
);
CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
);
CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
);
CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
);

Question: How many aircrafts are there?
SQL: SELECT count(*) FROM aircraft

Question: What is the number of aircraft?
SQL: SELECT count(*) FROM aircraft

Question: List the description of all aircrafts.
SQL: SELECT Description FROM aircraft

Question: What are the descriptions for the aircrafts?
SQL: SELECT Description FROM aircraft

Question: What is the average number of international passengers of all airports?
SQL: SELECT avg(International_Passengers) FROM airport

Question: What is the average number of international passengers for an airport?
SQL: SELECT avg(International_Passengers) FROM airport

Question: What are the number of international and domestic passengers of the airport named London "Heathrow"?
SQL: SELECT International_Passengers ,  Domestic_Passengers FROM airport WHERE Airport_Name  =  "London Heathrow"

Question: How many international and domestic passengers are there in the airport London Heathrow?
SQL: SELECT International_Passengers ,  Domestic_Passengers FROM airport WHERE Airport_Name  =  "London Heathrow"

Question: What are the total number of Domestic Passengers of airports that contain the word "London".
SQL: SELECT sum(Domestic_Passengers) FROM airport WHERE Airport_Name LIKE "%London%"

Question: What are the total number of domestic passengers at all London airports?
SQL: SELECT sum(Domestic_Passengers) FROM airport WHERE Airport_Name LIKE "%London%"

Question: What are the maximum and minimum number of transit passengers of all aiports.
SQL: SELECT max(Transit_Passengers) ,  min(Transit_Passengers) FROM airport

Question: What is the maximum and mininum number of transit passengers for all airports?
SQL: SELECT max(Transit_Passengers) ,  min(Transit_Passengers) FROM airport

Question: What are the name of pilots aged 25 or older?
SQL: SELECT Name FROM pilot WHERE Age  >=  25

Question: what is the name of every pilot who is at least 25 years old?
SQL: SELECT Name FROM pilot WHERE Age  >=  25

Question: List all pilot names in ascending alphabetical order.
SQL: SELECT Name FROM pilot ORDER BY Name ASC

Question: What are the names of the pilots in alphabetical order?
SQL: SELECT Name FROM pilot ORDER BY Name ASC

Question: List names of all pilot aged 30 or younger in descending alphabetical order.
SQL: SELECT Name FROM pilot WHERE Age  <=  30 ORDER BY Name DESC

Question: What are the names of all pilots 30 years old or young in descending alphabetical order?
SQL: SELECT Name FROM pilot WHERE Age  <=  30 ORDER BY Name DESC

Question: Please show the names of aircrafts associated with airport with name "London Gatwick".
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Gatwick"

Question: What are the names of all the aircrafts associated with London Gatwick airport?
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Gatwick"

Question: Please show the names and descriptions of aircrafts associated with airports that have a total number of passengers bigger than 10000000.
SQL: SELECT T1.Aircraft ,  T1.Description FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Total_Passengers  >  10000000

Question: What are the names and descriptions of aircrafts associated with an airport that has more total passengers than 10000000?
SQL: SELECT T1.Aircraft ,  T1.Description FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Total_Passengers  >  10000000

Question: What is the average total number of passengers of airports that are associated with aircraft "Robinson R-22"?
SQL: SELECT avg(T3.Total_Passengers) FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T1.Aircraft  =  "Robinson R-22"

Question: What is the average total number of passengers for all airports that the aircraft "Robinson R-22" visits?
SQL: SELECT avg(T3.Total_Passengers) FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T1.Aircraft  =  "Robinson R-22"

Question: Please list the location and the winning aircraft name.
SQL: SELECT T2.Location ,  T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft

Question: What is the location and name of the winning aircraft?
SQL: SELECT T2.Location ,  T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft

Question: List the name of the aircraft that has been named winning aircraft the most number of times.
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the name of the aircraft that has won an award the most?
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft ORDER BY COUNT(*) DESC LIMIT 1

Question: List the names of aircrafts and the number of times it won matches.
SQL: SELECT T1.Aircraft ,  COUNT(*) FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft

Question: For each aircraft that has won an award, what is its name and how many time has it won?
SQL: SELECT T1.Aircraft ,  COUNT(*) FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft

Question: List names of all pilot in descending order of age.
SQL: SELECT Name FROM pilot ORDER BY Age DESC

Question: What are the names of all pilots listed by descending age?
SQL: SELECT Name FROM pilot ORDER BY Age DESC

Question: List the names of aircrafts and that won matches at least twice.
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft HAVING COUNT(*)  >=  2

Question: What are the names of all aircrafts that have won a match at least twice?
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID  =  T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft HAVING COUNT(*)  >=  2

Question: List the names of aircrafts and that did not win any match.
SQL: SELECT Aircraft FROM aircraft WHERE Aircraft_ID NOT IN (SELECT Winning_Aircraft FROM MATCH)

Question: What are the names of all aicrafts that have never won any match?
SQL: SELECT Aircraft FROM aircraft WHERE Aircraft_ID NOT IN (SELECT Winning_Aircraft FROM MATCH)

Question: Show the names of aircrafts that are associated with both an airport named "London Heathrow" and an airport named "London Gatwick"
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Heathrow" INTERSECT SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Gatwick"

Question: What are the names of all aircrafts that are associated with both London Heathrow and Gatwick airports?
SQL: SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Heathrow" INTERSECT SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Gatwick"

Question: Show all information on the airport that has the largest number of international passengers.
SQL: SELECT * FROM airport ORDER BY International_Passengers DESC LIMIT 1

Question: What is all the information on the airport with the largest number of international passengers?
SQL: SELECT * FROM airport ORDER BY International_Passengers DESC LIMIT 1

Question: find the name and age of the pilot who has won the most number of times among the pilots who are younger than 30.
SQL: SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot WHERE t1.age  <  30 GROUP BY t2.winning_pilot ORDER BY count(*) DESC LIMIT 1

Question: What is the name and age of the pilot younger than 30 who has won the most number of times?
SQL: SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot WHERE t1.age  <  30 GROUP BY t2.winning_pilot ORDER BY count(*) DESC LIMIT 1

Question: what is the name and age of the youngest winning pilot?
SQL: SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot ORDER BY t1.age LIMIT 1

Question: How old is the youngest winning pilot and what is their name?
SQL: SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot ORDER BY t1.age LIMIT 1

Question: find the name of pilots who did not win the matches held in the country of Australia.
SQL: SELECT name FROM pilot WHERE pilot_id NOT IN (SELECT Winning_Pilot  FROM MATCH WHERE country  =  'Australia')

Question: What are the names of the pilots that have not won any matches in Australia?
SQL: SELECT name FROM pilot WHERE pilot_id NOT IN (SELECT Winning_Pilot  FROM MATCH WHERE country  =  'Australia')