数据库操作题

select aname from (select aname,ename,salary from (select aname,eid from aircraft inner join certified on aircraft.aid = certified.aid) as table1 inner join employees on table1.eid = employees.eid) group by aname having min(salary)>80000;

image.png

select eid,max(crusingrange) from (select certified.eid,aid from (select eid,count(eid) from certified group by eid having count(eid)>3) as table1 inner join certified on table1.eid = certified.eid) as table2 inner join aircraft on table2.aid = aircraft.aid group by eid having max(crusingrange)

image.png

select ename from employees where salary < (select min(price) from flights where origin = 'Los Angeles' and destination = 'Honolulu')

image.png

select aname,avg(salary) from (select table1.aid,aname,eid from (select * from aircraft where crusingrange>1000) as table1 inner join certified on table1.aid = certified.aid) as table2 inner join employees on table2.eid = employees.eid group by aname having avg(salary)

image.png

select ename,aname from (select ename,aid from employees inner join certified on employees.eid = certified.eid) as table1 inner join aircraft on table1.aid = aircraft.aid where aname like 'Boeing%'

image.png

select aid from aircraft,(select * from flights where origin = 'Los Angeles' and destination = 'Chicago') as table1 group by aid,crusingrange having crusingrange<avg(distance)

image.png

7.这道题显示的数据有点多，就不放截图了。

select eid,flno from (select eid,max(crusingrange) as column1 from (select table1.eid,aid from (select eid from employees where salary >100000) as table1 inner join certified on table1.eid = certified.eid) as table2 inner join aircraft on table2.aid = aircraft.aid group by eid having max(crusingrange)) as table3 inner join flights on distance < column1

select ename,aname from (select ename,aid from employees inner join certified on employees.eid = certified.eid) as table1 inner join aircraft on table1.aid = aircraft.aid where crusingrange>3000 and aname not like 'Boeing%'

image.png

9.这道题我并没有写两次转机的情况，因为发现再转一次机的情况下，第一次乘机的出发地都是Madison，不存在再多转一次机的情况（除非有人从Madison飞到另一个地方然后又飞回Madison，之后再去New York）

select departs from flights where origin = 'Madison' and destination = 'New York' and arrives < '18:00:00' union all select flights.departs from flights inner join (select origin,departs from flights where origin != 'Madison' and destination = 'New York' and arrives < '18:00:00') as table1 where flights.destination = table1.origin and flights.arrives < table1.departs and flights.origin = 'Madison'

image.png

select column1-column2 as column3 from (select avg(salary) as column1 from (select employees.eid,salary from employees inner join certified on employees.eid = certified.eid group by eid) as table1 having avg(salary)) as table2,(select avg(salary) as column2 from employees) as table3

image.png

select ename,salary from (select ename,salary from employees where eid not in (select eid from certified)) as table2 inner join (select avg(salary) as column1 from (select ename,salary from employees inner join certified on employees.eid = certified.eid group by ename) as table1 having avg(salary)) as table3 on table2.salary>table3.column1

image.png

select eid,ename,crusingrange from (select employees.eid,ename,aid from employees inner join certified on employees.eid = certified.eid) as table1 inner join aircraft on table1.aid = aircraft.aid group by eid having min(crusingrange)>1000

image.png

select eid,ename,crusingrange from (select employees.eid,ename,aid from employees inner join certified on employees.eid = certified.eid) as table1 inner join aircraft on table1.aid = aircraft.aid group by eid having min(crusingrange)>1000 and count(eid)>1

image.png

14.这道题应该有更简单的方法

select ename from (select eid,ename,table3.aid,aname from (select table2.eid,ename,aid from (select eid,ename,min(crusingrange) from (select employees.eid,ename,aid from employees inner join certified on employees.eid = certified.eid) as table1 inner join aircraft on table1.aid = aircraft.aid group by eid having min(crusingrange)>1000) as table2 inner join certified on table2.eid = certified.eid) as table3 inner join aircraft on table3.aid = aircraft.aid) as table4 where aname like 'Boeing%' group by eid

image.png