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
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 do not have certificate of Boeing 737-800.
assistant
SELECT name FROM Employee EXCEPT 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
How many employees have certificate.
assistant
SELECT count(DISTINCT eid) FROM Certificate
human
What are the ids of all employees that don't have certificates?
assistant
SELECT eid FROM Employee EXCEPT SELECT eid FROM Certificate ;