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>