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 employees who have certificate of Boeing 737-800.
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.name = 'Boeing 737-800'
human
Show names for all employees who have certificates on both Boeing 737-800 and Airbus A340-300.
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.name = 'Boeing 737-800' INTERSECT 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.name = 'Airbus A340-300'
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 aircraft certificates?
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
human
What is the salaray and name of the employee with the most certificates to fly planes 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 ;