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
List the names of aircrafts and that won matches at least twice.
assistant
SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID = T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft HAVING COUNT(*) >= 2
human
find the name of pilots who did not win the matches held in the country of Australia.
assistant
SELECT name FROM pilot WHERE pilot_id NOT IN (SELECT Winning_Pilot FROM MATCH WHERE country = 'Australia')
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
List names of all pilot aged 30 or younger in descending alphabetical order.
assistant
SELECT Name FROM pilot WHERE Age <= 30 ORDER BY Name DESC
human
What are the names of the pilots in alphabetical order?
assistant
SELECT Name FROM pilot ORDER BY Name ASC ;