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 names for all aircraft with at least two flights.
assistant
SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid = T2.aid GROUP BY T1.aid HAVING count(*) >= 2
human
How many aircrafts do we have?
assistant
SELECT count(*) FROM Aircraft
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
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
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
What is the salaray and name of the employee that is certified to fly the most planes?
assistant
SELECT T1.name , T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid = T2.eid GROUP BY T1.eid ORDER BY COUNT( * ) DESC LIMIT 1 ;