Yurttas/PL/DBL/oracle/F/02/SPJ/q09.sp
Jump to navigation
Jump to search
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/