SQL Solved Queries

SQL QUERIES 

20- Get project numbers for projects supplied by at least one supplier not in the same city as the project.

     

.

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

-- note you just need to find one tuple
-- that meets the specs to include a jnum

. .

21- Get all pairs of part numbers such that some supplier supplies both the indicated parts.

     

.

-- aliasing (shown in red) required

SELECT DISTINCT a.pnum, b.pnum
FROM   SPJ a, SPJ b
WHERE  a.snum = b.snum
AND    a.pnum > b.pnum;

. .

22- Get the total number of projects supplied by supplier S1.

     

.

SELECT count(distinct jnum)
FROM   spj
WHERE  snum = 'S1';

--Why is distinct required?

. .

23- (Another Method) Get the total number of projects supplied by supplier S1.

     

.

-- Alternative: SELECT in FROM clause

SELECT count(*)
FROM   (SELECT DISTINCT jnum
        FROM SPJ
        WHERE   snum = 'S1' );

-- Note a select statement can go where
-- a table name would go in the FROM clause.
-- You will probably find this one useful in the
-- homework

. .

24 Get the total quantity of part P1 supplied by supplier S1.

     

.

SELECT sum(qty)
FROM   SPJ
WHERE  snum = 'S1'
AND    pnum = 'P1';

. .

25- For each part supplied to a project get the part number, the project number, and the corresponding total quantity.

     

-- this groups by part and project

SELECT pnum, jnum, sum(qty)
FROM   SPJ
GROUP BY pnum,jnum;

. . .

26- Get part numbers of parts supplied to some project in an average quantity of more than 320.

     

SELECT pnum FROM P
WHERE  EXISTS (
         SELECT * FROM J
         WHERE  320 < (
               SELECT AVG(qty) FROM SPJ
               WHERE  spj.pnum = p.pnum
               AND    spj.jnum = j.jnum));           

. . .

27- (Another Method) Get part numbers of parts supplied to some project in an average quantity of more than 320.

     

. .

Previous Page                All Queries                       Next page

-- alternative: using SELECT in FROM clause
-- alias in red

SELECT DISTINCT A.pnum   
FROM   (SELECT pnum,jnum,avg(qty) average 
        FROM   spj
        GROUP BY pnum, jnum) A 
WHERE  A.average > 320;

--A is the alias for the SELECT statement in
-- the FROM clause.
-- average is the alias for avg(qty)

.