Yurttas/PL/DBL/oracle/F/02/SPJ/q09.sp

 1/*
 2REM
 3REM q09.sp
 4REM
 5REM Delete all shipments less than the average shipments
 6REM for 'Dallas' Projects
 7REM
 8REM
 9*/
10
11DECLARE
12
13  del_shp  NUMBER;
14
15BEGIN
16
17  DBMS_OUTPUT.PUT_LINE('Delete all shipments less than the average');
18  DBMS_OUTPUT.PUT_LINE('shipments for Dallas projects');
19  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
20
21  DELETE FROM Shipments
22  WHERE jn IN (SELECT jn
23               FROM Projects
24               WHERE city = 'Dallas')
25    AND quantity < (SELECT AVG(quantity) 
26                    FROM Shipments);
27
28  del_shp := SQL%ROWCOUNT;
29
30  DBMS_OUTPUT.PUT_LINE(del_shp||''||' rows deleted from Shipments');
31
32END;
33/