--
-- 2/27/2020
--
-- 16.  Show all information of parts supplied by supplier S2.

SELECT DISTINCT -- output columns
FROM -- sources
WHERE -- condition.

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2';

-- 17.  Show all information of parts supplied by supplier S2 or S3.
-- Expected: P1, P2 and P4.
SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2' OR 'S3';

-- 17.  Show all information of parts supplied by supplier S2 or S3.
-- Expected: P1, P2 and P4.
-- Check type: 'S3' is not Boolean
SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2' OR 'S3';

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2' OR u.SNUM = 'S3';

-- 19.  Show all information of parts supplied by supplier S2 and S3.

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2' AND u.SNUM = 'S3';  -- empty

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u1 ON (p.PNUM = u1.PNUM)
INNER JOIN supply AS u2 ON (p.PNUM = u2.PNUM)
WHERE u1.SNUM = 'S2' AND u2.SNUM = 'S3';  


19b. Show all information of parts supplied by supplier S2 but not S3: P1 and P2.

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2' AND u.SNUM  <> 'S3';  

-- same as

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2';

-- u.SNUM = 'S2' subsumes u.SNUM  <> 'S3'


SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2';

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S3';

(SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2')
MINUS
(SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S3');

SELECT DISTINCT p.* -- output columns
FROM part AS p INNER JOIN supply AS u ON (p.PNUM = u.PNUM)
WHERE u.SNUM = 'S2'
AND p.PNUM NOT IN (SELECT DISTINCT PNUM FROM supply WHERE SNUM = 'S3');