SQL Queries Solved

SQL QUERIES 

7- Get all snum/pnum/jnum triples such that the supplier, part and project are NOT co-located.

     

 SELECT snum, pnum, jnum
FROM   S, P, J
WHERE  NOT(s.city = p.city
           AND p.city = j.city);

-- or:

 SELECT snum, pnum, jnum
FROM   S, P, J
WHERE s.city <> p.city 
OR p.city <> j.city;

8- Get all snum/pnum/jnum triples such that no two of the supplier, part and project are co-located.

     

-- an exercise for the viewer

9- Get part numbers for parts supplied by a supplier in London.

     

.

SELECT DISTINCT pnum
FROM   SPJ, S
WHERE  spj.snum = s.snum
AND      s.city = 'London';

-- a subquery version next

10- (Another Method) Get part numbers for parts supplied by a supplier in London.

     

SELECT DISTINCT pnum
FROM   SPJ
WHERE  snum in (SELECT snum
              FROM   S
              WHERE s.city = 'London');

11- (Another Method) Get part numbers for parts supplied by a supplier in London.

     

WITH LondonSuppliers as 
            (SELECT snum
             FROM   S
             WHERE s.city = 'London')
SELECT DISTINCT pnum
FROM   SPJ, LondonSuppliers
WHERE  SPJ.snum = LondonSuppliers.snum;

--Called Subquery Factoring
--Can only be done with uncorrelated subqueries

Back                            All Queries                       Next page