CREATE TABLE pilot (
    Pilot_Id NUMBER PRIMARY KEY,
    Name TEXT,
    Age NUMBER
);
CREATE TABLE aircraft (
    Aircraft_ID NUMBER PRIMARY KEY,
    Aircraft TEXT,
    Description TEXT,
    Max_Gross_Weight TEXT,
    Total_disk_area TEXT,
    Max_disk_Loading TEXT
);
CREATE TABLE match (
    Round NUMBER PRIMARY KEY,
    Location TEXT,
    Country TEXT,
    Date TEXT,
    Fastest_Qualifying TEXT,
    Winning_Pilot TEXT,
    Winning_Aircraft TEXT,
    FOREIGN KEY ("Winning_Pilot") REFERENCES pilot("Pilot_Id"),
    FOREIGN KEY ("Winning_Aircraft") REFERENCES aircraft("Aircraft_ID")
);
CREATE TABLE airport (
    Airport_ID NUMBER PRIMARY KEY,
    Airport_Name TEXT,
    Total_Passengers NUMBER,
    %_Change_2007 TEXT,
    International_Passengers NUMBER,
    Domestic_Passengers NUMBER,
    Transit_Passengers NUMBER,
    Aircraft_Movements NUMBER,
    Freight_Metric_Tonnes NUMBER
);
CREATE TABLE airport_aircraft (
    ID NUMBER,
    Airport_ID NUMBER PRIMARY KEY,
    Aircraft_ID NUMBER,
    FOREIGN KEY ("Aircraft_ID") REFERENCES aircraft("Aircraft_ID"),
    FOREIGN KEY ("Airport_ID") REFERENCES airport("Airport_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)