--
-- SQL Assignment Solutions
-- CSCI 6441 Spring 2011
-- Matthew Burke <mmburke@gwu.edu>
--
-- Note: these queries have been tested in PostgreSQL 9.0.3
-- they should work in other RDBMSes as well.
--
-- Note 2: there is some degree of ambiguity in how
-- the questions were posed.  Also, in SQL there is often
-- (many) more than one way to solve a particular problem.
-- These queries might look different from yours---that
-- doesn't necessarily mean your queries are incorrect.

-- 1
SELECT DISTINCT p.pname
FROM parts p left outer join catalog c on (p.id = c.pid)
WHERE cost IS NULL;

-- 2
SELECT s.sname
FROM   suppliers s
WHERE NOT EXISTS ((SELECT p.id FROM parts p)
                   EXCEPT
                  (SELECT c.pid FROM catalog c WHERE c.sid = s.id));

-- 3
SELECT s.sname
FROM suppliers s
WHERE NOT EXISTS ((SELECT p.id FROM parts p WHERE p.color = 'Red')
                   EXCEPT
                  (SELECT c.pid FROM catalog c, parts p
                    WHERE c.sid = s.id AND
                    c.pid = p.id AND p.color = 'Red'));

-- 4
SELECT p.pname
FROM parts p, catalog c, suppliers s
WHERE p.id = c.pid and c.sid = s.id
AND s.sname = 'Acme Widget Suppliers'
AND NOT EXISTS (SELECT *
                FROM catalog c1, suppliers s1
                WHERE p.id = c1.pid AND c1.sid = s1.id AND
                s1.sname <> 'Acme Widget Suppliers');

-- 5
SELECT DISTINCT c.sid
FROM catalog c
WHERE c.cost > (SELECT AVG(c1.cost)
                FROM catalog c1
                WHERE c1.pid = c.pid);

-- 6
SELECT p.id as "Part Id", s.sname
FROM parts p, suppliers s, catalog c
WHERE c.pid = p.id AND c.sid = s.id
AND c.cost = (SELECT MAX(c1.cost)
              FROM catalog c1
              WHERE c1.pid = p.id);

-- 7
SELECT DISTINCT c.sid
FROM catalog c
WHERE NOT EXISTS (SELECT *
                  FROM parts p
                  WHERE p.id = c.pid and p.color <> 'Red');

-- 8
SELECT DISTINCT c.sid
FROM catalog c, parts p
WHERE c.pid = p.id AND p.color = 'Red'
INTERSECT
SELECT DISTINCT c1.sid
FROM catalog c1, parts p1
WHERE c1.pid = p1.id and p1.color = 'Green';

-- 9
SELECT DISTINCT c.sid
FROM catalog c, parts p
WHERE c.pid = p.id AND p.color = 'Red'
UNION
SELECT DISTINCT c1.sid
FROM catalog c1, parts p1
WHERE c1.pid = p1.id AND p1.color = 'Green';

-- 10
SELECT s.sname, COUNT(*) as PartCount
FROM suppliers s, parts p, catalog c
WHERE p.id = c.pid and c.sid = s.id
GROUP BY s.sname, s.id
HAVING EVERY(p.color = 'Green');

-- 11
SELECT distinct * 
FROM (SELECT s.sname as Supplier, p.pname as MaxName, cost as MaxCost
      FROM catalog c, parts p, suppliers s
      WHERE c.sid = s.id AND c.pid = p.id
      AND sid IN (SELECT DISTINCT sid 
                  FROM catalog JOIN parts ON (id = pid) 
                  WHERE color = 'Red'
                  INTERSECT
                  SELECT DISTINCT sid 
                  FROM catalog JOIN parts ON (id = pid) 
                  WHERE color = 'Green')
      AND cost = (SELECT MAX(cost) FROM catalog WHERE sid = c.sid)) TMAX
 NATURAL JOIN
      (SELECT s.sname as Supplier , p.pname as MinName, cost as MinCost
      FROM catalog c, parts p, suppliers s
      WHERE c.sid = s.id AND c.pid = p.id
      AND
      sid IN (SELECT DISTINCT sid 
              FROM catalog JOIN parts ON (id = pid) 
              WHERE color = 'Red'
              INTERSECT
              SELECT DISTINCT sid 
              FROM catalog JOIN parts ON (id = pid) 
              WHERE color = 'Green')
      AND cost = (SELECT MIN(cost) FROM catalog WHERE sid = c.sid)) TMIN;