Yurttas/PL/DBL/oracle/F/02/SPJ/q09.sp
Revision as of 23:32, 4 November 2013 by MassBot1 (talk | contribs) (Created page with "<syntaxhighlight lang="text" line start="1" enclose="div"> →REM REM q09.sp REM REM Delete all shipments less than the average shipments REM for 'Dallas' Projects REM REM: ...")
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/