## Solutions for HW #5

-- 5.2.1 pnames of parts for which there is some supplier select distinct p.pid, pname from parts p, catalog c where p.pid=c.pid; -- 5.2.2 sname who supply every part select s.sname from suppliers s where not exists ((select p.pid from parts p) minus -- Oracle uses minus, not except (select c.pid from catalog c where c.sid=s.sid)); -- 5.2.3 sname of suppliers that supply every red part select s.sname from suppliers s where not exists ((select p.pid from parts p where color='Red') minus -- Oracle uses minus, not except (select c.pid from catalog c where c.sid=s.sid)); -- 5.2.4 find pname of parts supplied only by Acme select p.pid,p.pname from parts p, suppliers s, catalog c where p.pid=c.pid and s.sid=c.sid and s.sname like 'Acme%' minus (select p.pid,p.pname from parts p, suppliers s, catalog c where p.pid=c.pid and s.sid=c.sid and s.sname not like 'Acme%'); -- alternate for 5.2.4 SELECT P.pname FROM Parts P, Catalog C, Suppliers S WHERE P.pid = C.pid AND C.sid = S.sid AND S.sname = 'Acme Widget Suppliers' AND NOT EXISTS ( SELECT * FROM Catalog C1, Suppliers S1 WHERE P.pid = C1.pid AND C1.sid = S1.sid AND S1.sname <> 'Acme Widget Suppliers' ); -- 5.2.5 Find sid who charge more than average for a part select c.sid,c.pid from catalog c where c.cost > (select avg(cost) from catalog c2 where c.pid=c2.pid); -- 5.2.6 For each part, find sname that charges most for part select s.sname, p.pname, c.sid, c.pid from catalog c, suppliers s, parts p where s.sid=c.sid and p.pid=c.pid and c.cost = (select max(cost) from catalog c2 where c.pid=c2.pid); -- 5.2.7 find sid that supply only red parts select c.sid from catalog c minus (select c.sid from catalog c where c.pid in ( select p.pid from parts p where color <> 'Red')); -- 5.2.8 sid of suppliers who supply a red part and a green part select s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and p.color='Red' intersect select s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and p.color='Green'; -- 5.2.9 sid of suppliers who supply a red part or a green part select s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and p.color='Red' union select s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and p.color='Green'; -- alternate solution select distinct s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and (p.color='Red' or p.color='Green'); -- 5.2.10 for every supplier who supplies only green parts, print -- sname and number of parts supplied. select s.sname, count(*) as NumParts from suppliers s, catalog c where s.sid=c.sid and s.sid in (select c.sid from catalog c minus (select c.sid from catalog c where c.pid in ( select p.pid from parts p where color <> 'Green'))) group by s.sname; -- This is an alternate solution for 5.2.10. -- HOWEVER, Oracle does not have the EVERY keyword, so this solution -- will NOT work with Oracle. SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid HAVING EVERY (P.color='Green'); -- 5.2.11 for every supplier who supplies a green part and a red part, -- print pname and cost of most expensive part that she supplies. select s.sname, p.pname, c.cost from catalog c, suppliers s, parts p where c.sid=s.sid and c.pid=p.pid and c.cost= (select max(c2.cost) from catalog c2 where c2.sid=c.sid) and c.sid in -- See 5.2.8: sid of suppliers who supply red parts and green parts. (select s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and p.color='Red' intersect select s.sid from suppliers s, catalog c, parts p where s.sid=c.sid and c.pid=p.pid and p.color='Green'); -- 5.3.1 names of aircraft such that all pilots certified earn -- more than $80k select distinct aname from aircraft a, certified c, employees e where a.aid=c.aid and e.eid=c.eid group by a.aid, a.aname having min(e.salary) > 80000; -- alternate solution for 5.3.1 select distinct a.aname from aircraft a, certified cx where a.aid=cx.aid and a.aid not in ( select distinct c.aid from certified c, employees e where c.eid=e.eid and e.salary <= 80000); -- 5.3.2 for each pilot certified for > 3 aircraft, find eid and max -- cruisingrange for aircraft on which he is certified. select e.eid, e.ename, max(a.cruisingrange) from aircraft a, certified c, employees e where a.aid=c.aid and e.eid=c.eid group by e.eid, e.ename having count(*) > 3; -- 5.3.5 name of pilots certified for some Boeing aircraft select distinct ename from aircraft a, certified c, employees e where a.aid=c.aid and e.eid=c.eid and a.aname like 'Boeing%'; -- 5.3.7 identify routes that can be piloted by every pilot who earns > $100k. -- I think this is really: for all pilots that earn > $100k, find their -- max range. Then find the min of that set. Now find routes < that min. select distinct f.origin, f.destination, distance from flights f where f.distance < (select min(max(cruisingrange)) from aircraft a, certified c, employees e where a.aid=c.aid and e.eid=c.eid and e.salary>100000 group by c.eid); -- 5.3.10 find difference between average salary of pilots and average -- salary of all employees (including pilots) select avgsalary1 - avgsalary2 from (select avg(salary) as avgsalary1 from employees e, (select distinct eid as certeid from certified) where e.eid=certeid), (select avg(salary) as avgsalary2 from employees);