Database: pilot_record

CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
);
CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
);
CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_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)