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
How many records are there?
assistant
SELECT COUNT( * ) FROM pilot_record;
human
Show the pilot names where there is at lease one record.
assistant
SELECT T2.Pilot_name FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID = T2.pilot_ID GROUP BY T2.Pilot_name;
human
Which pilot has only 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
What about the ones who 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 ;