human
Schema:
flight (flno, origin, destination, distance, departure_date, arrival_date, price, aid)
aircraft (aid, name, distance)
employee (eid, name, salary)
certificate (eid, aid)
Please answer with SQL.
assistant
Sure, I understand.
human
Show ids for all aircrafts with more than 1000 distance.
assistant
SELECT aid FROM Aircraft WHERE distance > 1000
human
What is the salary and name of the employee who has the most number of certificates on aircrafts with distance more than 5000?
assistant
SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid = T2.eid JOIN Aircraft AS T3 ON T3.aid = T2.aid WHERE T3.distance > 5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
human
Show the id and name of the aircraft with the maximum distance.
assistant
SELECT aid , name FROM Aircraft ORDER BY distance DESC LIMIT 1
human
Show name and distance for all aircrafts.
assistant
SELECT name , distance FROM Aircraft
human
Show the name of aircraft which fewest people have its certificate.
assistant
SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid = T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1
human
Show the name and distance of the aircrafts with more than 5000 distance and which at least 5 people have its certificate.
assistant
SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid = T1.aid WHERE T2.distance > 5000 GROUP BY T1.aid ORDER BY COUNT( * ) >= 5 ;