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 employees with at least 100000 salary.
assistant
SELECT eid FROM Employee WHERE salary > 100000
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
How many employees have salary between 100000 and 200000?
assistant
SELECT count(*) FROM Employee WHERE salary BETWEEN 100000 AND 200000
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 employee with maximum salary.
assistant
SELECT eid , name FROM Employee ORDER BY salary DESC LIMIT 1
human
What is the name and salary for employee with id 242518965?
assistant
SELECT name , salary FROM Employee WHERE eid = 242518965 ;