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 most common nationality of pilots.
assistant
SELECT Nationality FROM pilot GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
human
Show the names of pilots from team 'Bradley' or 'Fordham'.
assistant
SELECT Pilot_name FROM pilot WHERE Team = 'Bradley' OR Team = 'Fordham'
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 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
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 ;