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 name of the aircraft that has been named winning aircraft the most number of times.
assistant
SELECT T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID = T2.Winning_Aircraft GROUP BY T2.Winning_Aircraft ORDER BY COUNT(*) DESC LIMIT 1
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
Please list the location and the winning aircraft name.
assistant
SELECT T2.Location , T1.Aircraft FROM aircraft AS T1 JOIN MATCH AS T2 ON T1.Aircraft_ID = T2.Winning_Aircraft
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
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 ) ;