human
Schema:
pilot (Pilot_Id, Name, Age)
aircraft (Aircraft_ID, Aircraft, Description, Max_Gross_Weight, Total_disk_area, Max_disk_Loading)
match (Round, Location, Country, Date, Fastest_Qualifying, Winning_Pilot, Winning_Aircraft)
airport (Airport_ID, Airport_Name, Total_Passengers, %_Change_2007, International_Passengers, Domestic_Passengers, Transit_Passengers, Aircraft_Movements, Freight_Metric_Tonnes)
airport_aircraft (ID, Airport_ID, Aircraft_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
find the name and age of the pilot who has won the most number of times among the pilots who are younger than 30.
assistant
SELECT t1.name , t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id = t2.winning_pilot WHERE t1.age < 30 GROUP BY t2.winning_pilot ORDER BY count(*) DESC LIMIT 1
human
what is the name and age of the youngest winning pilot?
assistant
SELECT t1.name , t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id = t2.winning_pilot ORDER BY t1.age LIMIT 1
human
List all pilot names in ascending alphabetical order.
assistant
SELECT Name FROM pilot ORDER BY Name ASC
human
What are the name of pilots aged 25 or older?
assistant
SELECT Name FROM pilot WHERE Age >= 25
human
List names of all pilot in descending order of age.
assistant
SELECT Name FROM pilot ORDER BY Age DESC
human
What are the names of all pilots 30 years old or young in descending alphabetical order?
assistant
SELECT Name FROM pilot WHERE Age <= 30 ORDER BY Name DESC ;