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
What are the names of the pilots with IDs 1 and 2?
assistant
SELECT pilot_name FROM pilot WHERE pilot_ID = 1 OR pilot_ID = 2;
human
How many records do they have?
assistant
SELECT T2.Pilot_id , COUNT( * ) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID = T2.pilot_ID WHERE T2.pilot_ID = 1 OR T2.pilot_id = 2 GROUP BY T2.Pilot_id;
human
Which year did they join?
assistant
SELECT pilot_ID , join_year FROM pilot WHERE pilot_ID = 1 OR pilot_id = 2;
human
Which are their teams?
assistant
SELECT pilot_ID , team FROM pilot WHERE pilot_ID = 1 OR pilot_id = 2 ;