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: 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')