SQL Queries Solved

SQL QUERIES 

33- Get project numbers for projects not supplied with any red part by any London supplier. (discussion)

     

.

-- DISCUSSION: in relational algebra the problem is to
-- find the badguys who are supplied with red parts by
-- London suppliers and remove the badguys from the list
-- of all projects.
-- In SQL we want to identify projects for whom no such
-- London-supplied red part exists. Use NOT EXISTS or
-- NOT IN for this purpose.

. .

33- Get project numbers for projects not supplied with any red part by any London supplier.

     

.

SELECT jnum FROM   J 
WHERE  NOT EXISTS (SELECT *
           FROM   SPJ, P, S
           WHERE  j.jnum = spj.jnum
           AND    spj.snum = s.snum
           AND    s.city = 'London'
           AND    spj.pnum = p.pnum
           AND    p.color = 'Red')  ;         

. .

34- (Another Method) Get project numbers for projects not supplied with any red part by any London supplier.

     

.

SELECT jnum
FROM   J 
WHERE  NOT EXISTS ( SELECT * 
                    FROM SPJ
                    WHERE jnum=j.jnum
                    AND (snum,pnum) IN (
                               SELECT snum, pnum
                               FROM   S, P
                               WHERE  s.city = 'London'
                               AND    p.color = 'Red'));

. .

35- (Another Method) Get project numbers for projects not supplied with any red part by any London supplier.

     

.

SELECT jnum FROM J
           MINUS
SELECT jnum
FROM SPJ, S, P 
WHERE spj.snum = s.snum
AND   spj.pnum = p.pnum
AND   s.city = 'London'
AND   p.color = 'Red';

. .

36- Get project numbers for projects supplied entirely by supplier S1.

     

.

SELECT jnum
FROM   J
WHERE  NOT EXISTS (
              SELECT *
              FROM   SPJ
              WHERE  j.jnum = spj.jnum
              AND    snum <> 'S1');

. .

37- Get part numbers for each part which is supplied to all projects in London. (discussion)

     

-- DISCUSSION: this uses divides in relational algebra.
-- In SQL, a DOUBLE NEGATIVE is required:
-- Get every part for which
-- there is no London project
-- to which it is not supplied

. . . .

38- Get part numbers for each part which is

     

SELECT pnum  FROM   P
WHERE  NOT EXISTS (
           SELECT *  FROM   J
           WHERE  city = 'London'
           AND  NOT EXISTS (
                    SELECT *  FROM   SPJ
                    WHERE  spj.pnum = p.pnum
                    AND    spj.jnum= j.jnum));

Previous Page                All Queries                       Next page

. . .