CREATE TABLE aircraft (
    Aircraft_ID NUMBER PRIMARY KEY,
    Order_Year NUMBER,
    Manufacturer TEXT,
    Model TEXT,
    Fleet_Series TEXT,
    Powertrain TEXT,
    Fuel_Propulsion TEXT
);
CREATE TABLE pilot (
    Pilot_ID NUMBER PRIMARY KEY,
    Pilot_name TEXT,
    Rank NUMBER,
    Age NUMBER,
    Nationality TEXT,
    Position TEXT,
    Join_Year NUMBER,
    Team TEXT
);
CREATE TABLE pilot_record (
    Record_ID NUMBER,
    Pilot_ID NUMBER PRIMARY KEY,
    Aircraft_ID NUMBER,
    Date TEXT,
    FOREIGN KEY ("Aircraft_ID") REFERENCES aircraft("Aircraft_ID"),
    FOREIGN KEY ("Pilot_ID") REFERENCES pilot("Pilot_ID")
);

Question: How many pilots are there?
SQL: SELECT count(*) FROM pilot

Question: List the names of pilots in ascending order of rank.
SQL: SELECT Pilot_name FROM pilot ORDER BY Rank ASC

Question: What are the positions and teams of pilots?
SQL: SELECT POSITION ,  Team FROM pilot

Question: List the distinct positions of pilots older than 30.
SQL: SELECT DISTINCT POSITION FROM pilot WHERE Age  >  30

Question: Show the names of pilots from team "Bradley" or "Fordham".
SQL: SELECT Pilot_name FROM pilot WHERE Team  =  "Bradley" OR Team  =  "Fordham"

Question: What is the joined year of the pilot of the highest rank?
SQL: SELECT Join_Year FROM pilot ORDER BY Rank ASC LIMIT 1

Question: What are the different nationalities of pilots? Show each nationality and the number of pilots of each nationality.
SQL: SELECT Nationality ,  COUNT(*) FROM pilot GROUP BY Nationality

Question: Show the most common nationality of pilots.
SQL: SELECT Nationality FROM pilot GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the pilot positions that have both pilots joining after year 2005 and pilots joining before 2000.
SQL: SELECT POSITION FROM pilot WHERE Join_Year	 <  2000 INTERSECT SELECT POSITION FROM pilot WHERE Join_Year	 >  2005

Question: Show the names of pilots and models of aircrafts they have flied with.
SQL: SELECT T3.Pilot_name ,  T2.Model FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID

Question: Show the names of pilots and fleet series of the aircrafts they have flied with in ascending order of the rank of the pilot.
SQL: SELECT T3.Pilot_name ,  T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID ORDER BY T3.Rank

Question: Show the fleet series of the aircrafts flied by pilots younger than 34
SQL: SELECT T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID WHERE T3.Age  <  34

Question: Show the names of pilots and the number of records they have.
SQL: SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name

Question: Show names of pilots that have more than one record.
SQL: SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name HAVING COUNT(*)  >  1

Question: List the names of pilots that do not have any record.
SQL: SELECT Pilot_name FROM pilot WHERE Pilot_ID NOT IN (SELECT Pilot_ID FROM pilot_record)