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
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 the names of aircrafts and the number of times it won matches.
assistant
SELECT T1.Aircraft , COUNT(*) FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID = T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft
human
What are the name of pilots aged 25 or older?
assistant
SELECT Name FROM pilot WHERE Age >= 25
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
List the names of aircrafts and that did not win any match.
assistant
SELECT Aircraft FROM aircraft WHERE Aircraft_ID NOT IN (SELECT Winning_Aircraft FROM MATCH)
human
What are the names of the pilots that have not won any matches in Australia?
assistant
SELECT name FROM pilot WHERE pilot_id NOT IN ( SELECT Winning_Pilot FROM MATCH WHERE country = "Australia" ) ;