-- 1. Find the pnames of parts for which there is some supplier.
select distinct p.pname from parts p, catalog c
  where p.pid=c.pid;

-- 3. Find the snames of suppliers who supply every red part. 
-- Requires relational division, so can skip solution.

select distinct s.sname from catalog c, suppliers s
where c.sid=s.sid and not exists
  (select p.pid from parts p where p.color='Red' and not exists
    (select c1.pid from catalog c1, parts p1 
       where c1.sid=c.sid and c1.pid=p1.pid and c1.pid=p.pid and
          p1.color='Red')
        );

-- 4. Find the pnames of parts supplied by Acme Widget Suppliers and
--   no one else.
-- i.e., parts supplied by AWS EXCEPT parts supplied supplied by others
-- Can't use EXCEPT on mysql (not in Entry SQL-92)
-- so use NOT IN

select p.pname
from parts p, suppliers s, catalog c
where p.pid=c.pid and s.sid=c.sid and s.sname='Acme Widget Suppliers' 
      and p.pid not in (
        select c1.pid from suppliers s1, catalog c1
        where c1.sid=s1.sid and s1.sname<>'Acme Widget Suppliers')
;


-- 5. Find the sids of suppliers who charge more for some part than 
-- the average cost of that part
--  (averaged over all the suppliers who supply that parts).

select distinct c.sid from catalog c
  where c.cost>(
        select avg(c1.cost) from catalog c1 where c1.pid=c.pid
  );

-- 7. Find the sids of suppliers who supply only red parts.
-- i.e., all suppliers except suppliers who supply non-red parts
-- Can't use EXCEPT on mysql (not in Entry SQL-92)
-- so use NOT IN
select distinct c.sid from catalog c, parts p
  where c.pid=p.pid and sid not in
        (select c1.sid from catalog c1, parts p1
            where p1.pid=c1.pid and p1.color<>'Red');

-- 10. For every supplier that only supplies green parts, print the name 
-- of the supplier and the total number of parts that she supplies.
-- Here, clearly need to group by sid, but add sname since it
-- appears in the select list.
SELECT s.sname, COUNT(*) AS TotalpartsNumber
FROM suppliers s, catalog c
WHERE s.sid=c.sid AND s.sid IN
  (SELECT c1.sid FROM catalog c1, parts p1 WHERE c1.pid=p1.pid AND p1.color='Green' AND c1.sid NOT IN
    (SELECT c2.sid FROM catalog c2, parts p2 WHERE c2.pid=p2.pid AND p2.color<>'Green'))
GROUP BY s.sname, s.sid;

