Database schema:
CREATE TABLE PilotSkills (
  pilot_name TEXT PRIMARY KEY,
  plane_name TEXT,
  age NUMBER,
  FOREIGN KEY (plane_name) REFERENCES Hangar(plane_name)
);
CREATE TABLE Hangar (
  plane_name TEXT PRIMARY KEY,
  location TEXT
);

Question: Find the name of the oldest pilot for each type of plane, and order the results by plane name.
SQL: SELECT pilot_name ,  plane_name ,  max(age) FROM pilotskills GROUP BY plane_name ORDER BY plane_name

Question: Find the max age for each group of pilots with the same name.
SQL: SELECT max(age) ,  pilot_name FROM pilotskills GROUP BY pilot_name

Question: Return the names and ages of pilors who have flown Piper Cub and are older than 35, or have flown the F-14 Fighter and are younger than 30.
SQL: SELECT pilot_name ,  age FROM pilotskills WHERE plane_name  =  'Piper Cub' AND age  >  35 UNION SELECT pilot_name ,  age FROM pilotskills WHERE plane_name  =  'F-14 Fighter' AND age  <  30

Question: Find the number of pilots for the plane types with average pilot age below 35.
SQL: SELECT count(*) ,  plane_name FROM pilotskills GROUP BY plane_name HAVING avg(age)  <  35

Question: What are the locations of the different planes, ordered by plane name?
SQL: SELECT LOCATION FROM hangar ORDER BY plane_name

Question: Find pilots who own planes Piper Cub and B-52 Bomber.
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' INTERSECT SELECT pilot_name FROM pilotskills WHERE plane_name  =  'B-52 Bomber'

Question: What is the name of the least popular plane?
SQL: SELECT plane_name FROM pilotskills GROUP BY plane_name ORDER BY count(*) LIMIT 1

Question: How many distinct planes are owned across all pilots?
SQL: SELECT count(DISTINCT plane_name) FROM pilotskills

Question: How many pilots who are older than 40?
SQL: SELECT count(*) FROM pilotskills WHERE age  >  40

Question: Find the location of the plane that is owned by the youngest pilot.
SQL: SELECT T2.location FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T1.age  =  (SELECT min(age) FROM pilotskills)

Question: What are the different pilot names, and what are the maximum ages of pilots for each?
SQL: SELECT max(age) ,  pilot_name FROM pilotskills GROUP BY pilot_name

Question: How many planes are owned by the pilot whose name is Smith?
SQL: SELECT count(plane_name) FROM pilotskills WHERE pilot_name  =  'Smith'

Question: Find the number of pilots whose age is younger than all pilots whose plane is F-14 Fighter.
SQL: SELECT count(*) FROM pilotskills WHERE age  <  (SELECT min(age) FROM pilotskills WHERE plane_name  =  'F-14 Fighter')

Question: What are the planes owned by pilot Smith with age 41?
SQL: SELECT plane_name FROM pilotskills WHERE pilot_name  =  'Smith' AND age  =  41

Question: What is the location of the plane that was flown by the pilot with the lowest age?
SQL: SELECT T2.location FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T1.age  =  (SELECT min(age) FROM pilotskills)

Question: What are the names and ages of pilots who have planes located in Austin?
SQL: SELECT T1.pilot_name ,  T1.age FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T2.location  =  "Austin"

Question: List all distinct types of planes owned by all pilots in alphabetic order?
SQL: SELECT DISTINCT plane_name FROM pilotskills ORDER BY plane_name

Question: Find the names of all pilots with age between 30 and 40 sorted by their ages in ascending order.
SQL: SELECT pilot_name FROM pilotskills WHERE age BETWEEN 30 AND 40 ORDER BY age

Question: Return the names of pilots who are older than any pilot who has flown Piper Cub, ordered alphabetically.
SQL: SELECT pilot_name FROM pilotskills WHERE age  >  (SELECT min(age) FROM pilotskills WHERE plane_name  =  'Piper Cub') ORDER BY pilot_name

Question: What are the different plane names, and what are the names of the oldest pilot who has each, ordered by plane name?
SQL: SELECT pilot_name ,  plane_name ,  max(age) FROM pilotskills GROUP BY plane_name ORDER BY plane_name

Question: What are the different plane names that contain the word Bomber?
SQL: SELECT DISTINCT plane_name FROM pilotskills WHERE plane_name LIKE '%Bomber%'

Question: Return the names of the different planes, as well as the names of the oldest pilots who flew each.
SQL: SELECT pilot_name ,  plane_name ,  max(age) FROM pilotskills GROUP BY plane_name

Question: Return the average and minimum ages across all pilots.
SQL: SELECT avg(age) ,  min(age) FROM pilotskills

Question: How many pilots who are older than 40 or younger than 30?
SQL: SELECT count(pilot_name) FROM pilotskills ORDER BY age  >  40 OR age  <  30

Question: Find the number of planes for each type.
SQL: SELECT count(*) ,  plane_name FROM pilotskills GROUP BY plane_name

Question: How many planes are controlled by the pilots whose age is older than 40?
SQL: SELECT count(plane_name) FROM pilotskills WHERE age  >  40

Question: What is the name of the plane that is flown the most often?
SQL: SELECT plane_name FROM pilotskills GROUP BY plane_name ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the plane that is flown the least often?
SQL: SELECT plane_name FROM pilotskills GROUP BY plane_name ORDER BY count(*) LIMIT 1

Question: What are the names of pilots between the ages of 30 and 40, ordered by age ascending?
SQL: SELECT pilot_name FROM pilotskills WHERE age BETWEEN 30 AND 40 ORDER BY age