SQL Queries 31 to 36

-- 31 (discussion) Get supplier numbers of suppliers
-- who supply the same part to all projects.

-- DISCUSSION: For each supplier, we have to see if any
-- part exists which has thisquality:
-- there is no project to which this supplier
-- does not supply the part.

-- 31 Get supplier numbers of suppliers who supply
-- the same part to all projects.

SELECT snum  FROM   S  WHERE EXISTS (
     SELECT *  FROM   P  
     WHERE NOT EXISTS (
           SELECT *  FROM   J
           WHERE NOT EXISTS (
                 SELECT *  FROM   SPJ
                 WHERE spj.jnum = j.jnum
                 AND   spj.pnum = p.pnum
                 AND   spj.snum = s.snum)));

-- 32 Get project numbers for projects supplied with
-- at least all parts available from supplier S1.

-- an exercise for the viewer.
-- hint:
-- find projects for whom
-- there is no part on the S1 list
-- which they do not use

-- 33 Get all cities in which at least one supplier, part
-- or project is located.

-- an exercise for the viewer.
-- hint: make sure the selections
-- you UNION are union compatible

-- 34 Get part numbers for parts that are supplied
-- either by a London supplier or to a London project.

-- an exercise for the viewer.

-- 35 Get supplier-number/part-number pairs such
-- that the indicated supplier does not supply the
-- indicated part.

SELECT snum, pnum
FROM   S, P
WHERE NOT EXISTS (SELECT *
                  FROM   SPJ
                  WHERE  spj.snum = s.snum
                  AND    spj.pnum = p.pnum)
ORDER BY snum,pnum;

-- 36 Get all pairs of supplier numbers such that the
-- indicated suppliers both supply exactly the same
-- set of parts.

SELECT A.SNUM, B.SNUM
FROM S A, S B
WHERE A.snum > B.snum
AND NOT EXISTS (SELECT PNUM FROM P
                  WHERE EXISTS (SELECT * FROM SPJ
                                WHERE spj.pnum = p.pnum
                                AND spj.snum = a.snum)
                  AND NOT EXISTS (SELECT * FROM SPJ
                                WHERE spj.pnum = p.pnum
                                AND spj.snum = b.snum))
AND NOT EXISTS (SELECT PNUM FROM P
                  WHERE EXISTS (SELECT * FROM SPJ
                                WHERE spj.pnum = p.pnum
                                AND spj.snum = b.snum)
                  AND NOT EXISTS (SELECT * FROM SPJ
                                WHERE spj.pnum = p.pnum
                                AND spj.snum = a.snum));