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

-- 2. Find the snames of suppliers who supply every 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 not exists 
       (select c1.pid from catalog c1 where c1.sid=c.sid and c1.pid=p.pid)
        );
-- alternatively (see 
-- https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/
select sname from catalog c, parts p, suppliers s
  where c.pid= p.pid and s.sid = c.sid
  group by c.sid
  having count(p.pid) = (select count(*) from parts);

-- 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')
        );
-- alternatively (
select sname from catalog c, parts p, suppliers s
  where c.pid= p.pid and s.sid = c.sid and p.color='Red'
  group by c.sid
  having count(p.pid) = (select count(*) from parts where 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
  );

-- 6. For each part, find the sname of the supplier who charges the most
--  for that part.
select p.pid, s.sname from parts p, suppliers s, catalog c
  where p.pid=c.pid and s.sid=c.sid and c.cost=(
        select max(c1.cost) from catalog c1 where c1.pid=p.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' and c1.sid=c.sid);

-- 8. Find the sids of suppliers who supply a red part and a green part.
select distinct c.sid from parts p, catalog c
where p.pid=c.pid and p.color='Red' and c.sid in
   (select c1.sid
   from parts p1, catalog c1
     where p1.pid=c1.pid and p1.color='Green');

-- 9. Find the sids of suppliers who supply a red parts or a green parts.
-- Note that UNION removes dups in its result
select c.sid from parts p, catalog c
  where p.pid=c.pid and p.color='Red'
union
select c1.sid from parts p1, catalog c1
  where p1.pid=c1.pid and p1.color='Green';

-- 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(*) from suppliers s, catalog c
  where s.sid=c.sid and s.sid in(
        select c.sid from catalog c where not exists(
             select c1.sid from catalog c1 where
 		c1.sid=c.sid and c1.pid in(                                     		select p.pid from parts p where p.color <> 'Green')
             )
        )
group by s.sname, s.sid;

-- 11. For every supplier that supplies a green part and a red part, print
-- the name and price of the most expensive part that she supplies.
select distinct s.sname, p.pname, c.cost
  from parts p, suppliers s, catalog c
  where p.pid=c.pid and s.sid=c.sid and c.cost=
      (select max(c1.cost) from catalog c1 where c1.sid=c.sid)
        and c.sid in(
           select c2.sid from catalog c2, parts p2
              where c2.pid=p2.pid and p2.color='Red' 
              and c2.sid in (
                  select c3.sid from catalog c3, parts p3
                      where c3.pid=p3.pid and p3.color='Green'));

-- Without distinct, there are duplicate rows in the output because a 
-- supplier has two tied highest-priced parts of the same part name but
-- different colors.
