Sql Database Queries With Solution

SQL QUERIES 

39 Get supplier numbers of suppliers who supply the same part to all projects. (Discussion)

     

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

. . .

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

. .

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

. .

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

     

Previous page                            All Queries                First Page.

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

. .