CSci 455

Solution to exercise #6.

Note:  I have looked these over and they appear to be correct, but please do let me know of any mistakes you find.

SQL> /*
DOC>solutions (I hope) for exercise #6
DOC>
DOC>Prob. 27: Get the total number of projects supplied by supplier
DOC>S1
DOC>*/
SQL> 
SQL> select count(distinct jno)
2 from spj
3 where sno = 'S1';

COUNT(DISTINCTJNO) 
------------------ 
2 

SQL> 
SQL> /*
DOC>28: Get the total quantity of part P1 supplied by supplier S1
DOC>*/
SQL> 
SQL> select sum(qty)
2 from spj
3 where pno = 'P1' and sno = 'S1';

SUM(QTY) 
---------- 
900 

SQL> 
SQL> /*
DOC>29:
DOC>For each part being supplied to a project, get the part number,
DOC>the project number, and the corresponding total quantity
DOC>*/
SQL> 
SQL> select pno, jno, sum(qty)
2 from spj
3 group by (pno, jno);

PNO JNO SUM(QTY) 
--- --- ---------- 
P1 J1 200 
P1 J4 1700 
P2 J2 200 
P2 J4 100 
P3 J1 600 
P3 J2 200 
P3 J3 200 
P3 J4 1700 
P3 J5 600 
P3 J6 400 
P3 J7 800 

PNO JNO SUM(QTY) 
--- --- ---------- 
P4 J2 500 
P4 J4 800 
P5 J2 100 
P5 J4 400 
P5 J5 500 
P5 J7 100 
P6 J2 200 
P6 J3 300 
P6 J4 500 
P6 J7 300 

21 rows selected.

SQL> 
SQL> /*
DOC>30: Get part numbers of parts supplied to some project
DOC>in an average quantity of more then 350
DOC>*/
SQL> 
SQL> select pno, jno, avg(qty)
2 from spj
3 group by pno, jno
4 having avg(qty) > 350;

PNO JNO AVG(QTY) 
--- --- ---------- 
P1 J4 850 
P3 J4 850 
P3 J5 600 
P3 J6 400 
P3 J7 800 
P4 J2 500 
P4 J4 800 
P5 J4 400 
P5 J5 500 
P6 J4 500 

10 rows selected.

SQL> 
SQL> 
SQL> 
SQL> /*
DOC>39: Get supplier numbers for suppliers supplying some project
DOC>with part P1 in a quantity greater than the average shipment
DOC>quantity of part P1 for that project.
DOC>*/
SQL> 
SQL> select sno, jno
2 from spj spj1
3 where pno = 'P1' and
4 qty >
5 (select avg(qty)
6 from spj spj2
7 where spj1.jno = spj2.jno
8 and spj2.pno = 'P1');

SNO JNO 
--- --- 
S5 J4 

SQL>