1、返回驾驶所有飞行员的工资都高于80000的飞机
SELECT aname FROM aircraft
WHERE aid != ALL(SELECT DISTINCT aid FROM certified
WHERE eid = ANY(SELECT eid FROM employees
WHERE salary<=80000 ) ) ;
2、返回能驾驶3种以上飞机的飞行员名字，和他们座驾的最大航程。
SELECT eid,MAX(crusingrange) FROM
( SELECT * FROM certified GROUP BY eid HAVING COUNT(eid)>3 )AS A
INNER JOIN
aircraft
ON A.aid=aircraft.aid
GROUP BY eid;
3.返回工资比从洛杉矶到火奴鲁鲁的最低票价还低的飞行员名字。
SELECT ename FROM employees
WHERE eid= ANY(SELECT DISTINCT eid FROM certified ) &&
salary<( SELECT MIN(price) FROM flights
WHERE origin='Los Angeles' && destination='Honolulu' ) ;
4、对于所有航程大于1000的飞机，找出飞机名字和平均薪水。
SELECT A.aname, ROUND(B.AVGsalary,2) AS AverageSalary FROM
( SELECT aid,aname FROM aircraft WHERE crusingrange > 1000 ) AS A
INNER JOIN
( SELECT certified.aid,AVG(employees.salary) AS AVGsalary FROM
certified INNER JOIN employees
ON certified.eid = employees.eid GROUP BY certified.aid) AS B
ON A.aid=B.aid ;
5、返回能驾驶Boeing飞机的飞行员的名字
SELECT DISTINCT ename
FROM employees
WHERE eid=ANY (SELECT eid FROM certified
WHERE aid= ANY( SELECT aid FROM aircraft
WHERE aname LIKE 'Boeing%') ) ;
6、找出适用于洛杉矶到芝加哥航线的所有飞机的id。
SELECT aid FROM aircraft
WHERE crusingrange > ANY(SELECT distance FROM flights
WHERE origin='Los Angeles' && destination='Chicago') ;
7、返回工资大于100000的飞行员飞行的航线（不知道是每个飞行员都要找航线，还是把所有有飞行员经过的航线都找出来）
SELECT origin,destination FROM flights
WHERE distance > ANY ( SELECT crusingrange FROM aircraft
WHERE aid = ANY(SELECT aid FROM certified
WHERE eid= ANY( SELECT eid FROM employees
WHERE salary>100000 ))) ;
8、返回能驾驶3000航程以上的飞机但不能开波音飞机的飞行员。
SELECT ename FROM employees WHERE
eid != ANY( SELECT eid FROM certified
WHERE aid= ANY(SELECT aid FROM aircraft WHERE aname LIKE 'Boeing%' )) &&
eid = ANY( SELECT eid FROM certified
WHERE aid= ANY(SELECT aid FROM aircraft WHERE crusingrange>3000)) ;
9、导入数据库的时候，时间没被导入进来····
10、查找飞行员和所有人的平均工资差距。
SELECT ROUND(AVG(salary),2)-(SELECT ROUND(AVG(salary),2) FROM employees )
AS salaryDifference FROM employees WHERE eid = ANY( SELECT eid FROM certified ) ;
11、查找工资比飞行员平均工资高 的非飞行员的名字和工资
SELECT ename,salary FROM employees
WHERE eid != ANY( SELECT eid FROM certified ) &&
salary > (SELECT AVG(salary) FROM employees
WHERE eid= ANY( SELECT eid FROM certified) ) ;
12、查找只能驾驶航程在1000以上的飞行员名字。
SELECT ename FROM employees WHERE
eid != ALL(SELECT eid FROM certified WHERE
aid= ANY(SELECT aid FROM aircraft WHERE crusingrange<1000 ) ) &&
eid = ANY(SELECT eid FROM certified) ;
13、只能驾驶航程在1000以上，且能驾驶两辆以上飞机的飞行员的名字。
SELECT ename FROM employees WHERE
eid != ALL(SELECT eid FROM certified WHERE
aid= ANY(SELECT aid FROM aircraft WHERE crusingrange<1000 ) ) &&
eid = ANY(SELECT eid FROM certified GROUP BY eid HAVING COUNT(aid)>=2 ) ;
14、只能驾驶航程在1000以上的波音飞机的飞行员名字。
SELECT ename FROM employees WHERE
eid != ALL(SELECT eid FROM certified
WHERE aid= ANY(SELECT aid FROM aircraft WHERE crusingrange<1000 ) ) &&
eid = ANY(SELECT eid FROM certified
WHERE aid= ANY(SELECT aid FROM aircraft WHERE aname LIKE 'Boeing%' )) ;
