human
Schema:
aircraft (Aircraft_ID, Order_Year, Manufacturer, Model, Fleet_Series, Powertrain, Fuel_Propulsion)
pilot (Pilot_ID, Pilot_name, Rank, Age, Nationality, Position, Join_Year, Team)
pilot_record (Record_ID, Pilot_ID, Aircraft_ID, Date)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the fleet series of the aircrafts flied by pilots younger than 34
assistant
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
human
Show names of pilots that have more than one record.
assistant
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
human
Show the names of pilots and the number of records they have.
assistant
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
human
Show the names of pilots and models of aircrafts they have flied with.
assistant
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
human
List the names of pilots in ascending order of rank.
assistant
SELECT Pilot_name FROM pilot ORDER BY Rank ASC
human
Show the names of pilots and fleet series of the aircrafts they have flied with in ascending order of the rank of the pilot.
assistant
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 ;