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: What are the different plane names of planes with an average pilot age of below 35, and how many pilots have flown each of them?
SQL: SELECT count(*) ,  plane_name FROM pilotskills GROUP BY plane_name HAVING avg(age)  <  35

Question: Find all locations of planes sorted by the plane name.
SQL: SELECT LOCATION FROM hangar ORDER BY plane_name

Question: What are the names of pilots who have flown Piper Cub but not the B-52 Bomber?
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' EXCEPT SELECT pilot_name FROM pilotskills WHERE plane_name  =  'B-52 Bomber'

Question: How many pilots are older than the youngest pilot who has Piper Cub?
SQL: SELECT count(pilot_name) FROM pilotskills WHERE age  >  (SELECT min(age) FROM pilotskills WHERE plane_name  =  'Piper Cub')

Question: What are the names of pilots, ordered by age descending?
SQL: SELECT pilot_name FROM pilotskills ORDER BY age DESC

Question: What are the names of oldest pilots for each type of plane?
SQL: SELECT pilot_name ,  plane_name ,  max(age) FROM pilotskills GROUP BY plane_name

Question: Count the number of pilots who have planes in Chicago.
SQL: SELECT 	count(DISTINCT T1.pilot_name) FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T2.location  =  'Chicago'

Question: How many plane B-52 Bomber owned by the pilot who is under 35?
SQL: SELECT count(*) FROM pilotskills WHERE age  <  35 AND plane_name  =  'B-52 Bomber'

Question: What is all the information about pilots who are younger than 30 ?
SQL: select * from pilotskills where age  <  30

Question: What is the name of the oldest pilot?
SQL: SELECT pilot_name FROM PilotSkills ORDER BY age DESC LIMIT 1

Question: Give the names of pilots who have planes in Austin and Boston.
SQL: SELECT T1.pilot_name FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T2.location  =  "Austin" INTERSECT SELECT T1.pilot_name FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T2.LOCATION  =  "Boston"

Question: Count the number of planes flown by pilots older than 40.
SQL: SELECT count(plane_name) FROM pilotskills WHERE age  >  40

Question: What are the average and smallest ages of all pilots?
SQL: SELECT avg(age) ,  min(age) FROM pilotskills

Question: What is the average age of pilots for different types of planes?
SQL: SELECT avg(age) ,  plane_name FROM pilotskills GROUP BY plane_name

Question: Where is the plane F-14 Fighter located?
SQL: SELECT LOCATION FROM hangar WHERE plane_name  =  'F-14 Fighter'

Question: Who is the youngest pilot to fly the plane Piper Cub?
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' ORDER BY age LIMIT 1

Question: How many different places have some plane?
SQL: SELECT count(DISTINCT LOCATION) FROM hangar

Question: List in alphabetic order the names of pilots whose age is greater than some pilots having plane Piper Cub.
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 names of pilots whose age is below the average age, ordered by age?
SQL: SELECT pilot_name FROM PilotSkills WHERE age  <  (SELECT avg(age) FROM PilotSkills) ORDER BY age

Question: What are the names and ages of pilots who own plane Piper Cub and are older than 35, or have 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: Count the number of different plane names across all pilots.
SQL: SELECT count(DISTINCT plane_name) FROM pilotskills

Question: Find the names of all pilots who have a plane named Piper Cub and is under 35.
SQL: SELECT pilot_name FROM PilotSkills WHERE age  <  35 AND plane_name  =  'Piper Cub'

Question: Count the number of planes Smith owns.
SQL: SELECT count(plane_name) FROM pilotskills WHERE pilot_name  =  'Smith'

Question: List all pilot names sorted by their ages in the descending order.
SQL: SELECT pilot_name FROM pilotskills ORDER BY age DESC

Question: Return the names of planes owned by the pilot whose name is Smith and is 41 years old.
SQL: SELECT plane_name FROM pilotskills WHERE pilot_name  =  'Smith' AND age  =  41

Question: How many pilots whose planes are in Chicago?
SQL: SELECT 	count(DISTINCT T1.pilot_name) FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name WHERE T2.location  =  'Chicago'

Question: What are the names of pilots who have either the Piper Cub or the F-14 Fighter?
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' OR plane_name  =  'F-14 Fighter'

Question: Find the name and age of pilots who have a plane 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: What are the names of pilots who own both Piper Cub and the 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: Find the number of all pilots whose age is older than some pilot who has plane Piper Cub.
SQL: SELECT count(pilot_name) FROM pilotskills WHERE age  >  (SELECT min(age) FROM pilotskills WHERE plane_name  =  'Piper Cub')

Question: Find pilots who own plane Piper Cub but not B-52 Bomber.
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' EXCEPT SELECT pilot_name FROM pilotskills WHERE plane_name  =  'B-52 Bomber'

Question: Count the number of different locations of hangars.
SQL: SELECT count(DISTINCT LOCATION) FROM hangar

Question: Return the names of pilots who are younger than average, ordered by age ascending.
SQL: SELECT pilot_name FROM PilotSkills WHERE age  <  (SELECT avg(age) FROM PilotSkills) ORDER BY age

Question: Find all information of on pilots whose age is less than 30.
SQL: SELECT * FROM PilotSkills WHERE age  <  30

Question: Count the number of B-52 Bombers owned by pilots under 35.
SQL: SELECT count(*) FROM pilotskills WHERE age  <  35 AND plane_name  =  'B-52 Bomber'

Question: Return the name of the youngest pilot to fly Piper Cub.
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' ORDER BY age LIMIT 1

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

Question: For each city, find the number and average age of pilots who have a plane.
SQL: SELECT count(T1.pilot_name) ,  avg(T1.age) ,  T2.location FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name GROUP BY T2.location

Question: Return the average age of pilots for each plane name.
SQL: SELECT avg(age) ,  plane_name FROM pilotskills GROUP BY plane_name

Question: What are the different plane names, ordered alphabetically?
SQL: SELECT DISTINCT plane_name FROM pilotskills ORDER BY plane_name

Question: Count the number of entries for each plane name.
SQL: SELECT count(*) ,  plane_name FROM pilotskills GROUP BY plane_name

Question: Count the number of pilots with age greater than 40 or less than 30.
SQL: SELECT count(pilot_name) FROM pilotskills ORDER BY age  >  40 OR age  <  30

Question: Return the location of the hangar in which F-14 Fighter is located.
SQL: SELECT LOCATION FROM hangar WHERE plane_name  =  'F-14 Fighter'