--
-- 22.  Show all status with active suppliers 
-- (a supplier is active if it supplies at least one part).
--

-- [1] snum of active supplier: S1, S2, S3, S4, S5.
SELECT DISTINCT u.snum
FROM supply AS u;

-- [2] solution.
SELECT DISTINCT s.snum, s.status
FROM supplier AS s INNER JOIN supply AS u 
    ON (u.snum = s.snum);
    
-- 26.  Show all cities that contain inactive suppliers (S6): 'Dallas'.

-- [1] snum of active supplier: S1, S2, S3, S4, S5.
SELECT DISTINCT u.snum
FROM supply AS u;

-- [2] snum of inactive supplier: S6
SELECT DISTINCT s.snum
FROM supplier AS s   -- all snum
WHERE s.snum NOT IN   -- [1]
    (SELECT DISTINCT u.snum
     FROM supply AS u);
     
-- [3] finallly:
SELECT DISTINCT s.scity  -- step [2]
FROM supplier AS s   -- all snum
WHERE s.snum NOT IN   -- [1]
    (SELECT DISTINCT u.snum  -- subquery: step [1]
     FROM supply AS u);
     
WITH -- CTE
    temp AS 
    (SELECT DISTINCT u.snum  -- subquery: step [1]
     FROM supply AS u)
SELECT DISTINCT s.scity  -- step [2]
FROM supplier AS s   -- all snum
WHERE s.snum NOT IN 
    (SELECT snum FROM temp);
    
-- 28.  Show the supplier numbers of all suppliers that supply red parts 
-- but not green parts.

-- [1] Show the supplier numbers of all suppliers that supply red parts: S2, S3, S5
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Red';

-- [2] Show the supplier numbers of all suppliers that supply green parts: S1, S2, S4, S5
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Green';

-- [3] final result = [1] - [2]: S3
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Red'
AND u.snum NOT IN
    (SELECT DISTINCT u2.snum
     FROM part AS p2 INNER JOIN supply AS u2 ON (p2.pnum = u2.pnum)
     WHERE p2.color = 'Green');
     
  --  28b.  Show the supplier numbers of all suppliers that supply 
  --  a red part and also a green part: S2, S5.
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Green' AND p.color = 'Red';  -- empty set

SELECT DISTINCT u.snum
FROM part AS p1 INNER JOIN supply AS u ON (p1.pnum = u.pnum)
    INNER JOIN part AS p2 ON (p2.pnum = u.pnum)
WHERE p1.color = 'Green' AND p2.color = 'Red'; -- empty set

SELECT DISTINCT u1.snum
FROM part AS p1 INNER JOIN supply AS u1 ON (p1.pnum = u1.pnum)
    INNER JOIN supply AS u2 ON (u1.snum = u2.snum)
    INNER JOIN part AS p2 ON (p2.pnum = u2.pnum)
WHERE p1.color = 'Green' AND p2.color = 'Red';  

Alternatively:


-- [1] Show the supplier numbers of all suppliers that supply red parts: S2, S3, S5
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Red';

-- [2] Show the supplier numbers of all suppliers that supply green parts: S1, S2, S4, S5
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Green';

-- [3] final result = [1] intersect [2]
SELECT DISTINCT u.snum
FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
WHERE p.color = 'Red'
AND u.snum IN
    (SELECT DISTINCT u2.snum
     FROM part AS p2 INNER JOIN supply AS u2 ON (p2.pnum = u2.pnum)
     WHERE p2.color = 'Green');
     
     
WITH 
    red AS
    (SELECT DISTINCT u.snum
     FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
     WHERE p.color = 'Red'),
    green AS
    (SELECT DISTINCT u.snum
     FROM part AS p INNER JOIN supply AS u ON (p.pnum = u.pnum)
     WHERE p.color = 'green')
SELECT *
FROM red NATURAL JOIN green;

-- 30.  Show the supplier numbers of all suppliers that
-- supplies all parts supplied by supplier S1: S1 and S2 (snum)

-- [1] pnum supplied by supplier S1: P1 and P2.
SELECT DISTINCT u1.PNum
FROM supply AS u1
WHERE u1.SNum = 'S1';

-- [2]  Show the supplier numbers of all suppliers: S1 to S6
SELECT DISTINCT s.snum
FROM supplier AS s
WHERE NOT EXISTS -- empty set test
    (SELECT DISTINCT u1.PNum  -- not exists any pnum supplied by S1.
     FROM supply AS u1
     WHERE u1.SNum = 'S1'
     AND NOT EXISTS
        (SELECT DISTINCT *
         FROM supply AS u2
         WHERE u2.SNum = s.snum
         AND u2.pnum = u1.pnum));