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);