SQL Queries Solved

SQL QUERIES 

12- Get part numbers for parts supplied by a supplier in London to a project in London.

     

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

-- subquery version next

. . .

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

     

 SELECT pnum
FROM   SPJ
 WHERE  snum in (SELECT snum
              FROM   S
              WHERE  city = 'London')
 AND   jnum in (SELECT jnum
             FROM   J
             WHERE  city = 'London') ;             

-- alternative using EXISTS next

. . .

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

     

 SELECT pnum
FROM   SPJ
 WHERE  EXISTS (SELECT * FROM   S
               WHERE  city = 'London'
               AND    s.snum = spj.snum)
 AND    EXISTS (SELECT * FROM   J
               WHERE  city = 'London'
              AND    j.jnum = spj.jnum);

-- study the parts in red

--When Subqueries are NOT possible:
--Get part numbers for parts their supplier's
--number and status when they are supplied by a
--London supplier

SELECT pnum, snum, status 
FROM   SPJ
WHERE  snum in (SELECT snum
              FROM  S 
              WHERE  city = 'London');

-- illegal: status's table (S) must appear
-- on the FROM line below it.
-- Illegal!

. . .

15- Get all pairs of city names such that a supplier in the first city supplies a project in the second.

     

.

SELECT DISTINCT s.city, j.city
FROM   S, J
WHERE  EXISTS (SELECT *
               FROM   SPJ
               WHERE  spj.snum = s.snum
               AND      spj.jnum = j.jnum  );

-- non-nested alternative next

. .

16- (Another Method) Get all pairs of city names such that a supplier in the first city supplies a project in the second.

     

.

SELECT DISTINCT s.city, j.city
FROM   S, J, SPJ
WHERE  spj.snum = s.snum
AND    spj.jnum = j.jnum;

. .

17- Get part numbers for parts supplied to any project by a supplier in the same city as that project.

     

.

SELECT DISTINCT pnum
FROM   S, J, SPJ
WHERE  spj.snum = s.snum
AND      spj.jnum = j.jnum
AND      s.city = j.city;

--nested alternative next

. .

18- (Another Method) Get part numbers for parts supplied to any project by a supplier in the same city as that project.

     

.

SELECT DISTINCT pnum   FROM   SPJ
WHERE  EXISTS (
          SELECT *  FROM   S
          WHERE s.snum = spj.snum
          AND   s.city IN (
                SELECT city FROM  J
                WHERE j.jnum = spj.jnum
                )
          );

. .

19- (Another Method) Get part numbers for parts supplied to any project by a supplier in the same city as that project.

. .

     

Previous Page                All Queries                       Next page

.

-- another version

SELECT DISTINCT pnum
FROM   SPJ
WHERE  (snum,jnum) IN (
             SELECT  snum, jnum
             FROM    S, J
             WHERE   s.city = j.city);

. .