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 all the pilot names and the join years.
assistant
SELECT Pilot_name , Join_Year FROM pilot;
human
How many unique years are there?
assistant
SELECT COUNT( Join_Year ) FROM pilot;
human
List the years by the pilot rank in ascending order.
assistant
SELECT Join_Year FROM pilot ORDER BY Rank ASC;
human
In which join year, the pilot has the highest rank?
assistant
SELECT Join_Year FROM pilot ORDER BY Rank ASC LIMIT 1 ;