Create a Stored Procedure, for the SaleCo2 database, that will:

• Delete an Invoice from the Invoice table by accepting the Invoice # as a procedure parameter when you CALL the procedure to be executed in the SQL window/tab within phpMyAdmin.
• Delete all associated rows for this Invoice in the Line table.
• Reduce the Customer balance in the Customer table by the amount of the deleted Invoice.

INCLUDE the following in your report: a. the stored procedure script/code b. SQL Print View screen evidence that the procedure exists c. SQL Print View screen evidence showing that what was expected to happen did happen (i.e. use the select command to show before/after raw data as appropriate based on the respective situations).

Trigger and Procedure creation/editing reminders:

• When initially creating a trigger or procedure in the SQL window the slash '/' must be placed in the delimiter textbox replacing the semi-colon ';'.
• When editing an existing trigger or procedure the edit window interface does not require you to add the slash '/', as it does this in the background for us.

Database Script File:

/* CH08_SaleCo2.SQL */ /* Script file for ORACLE RDBMS */ /* This script file creates the following tables: */ /* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */ /* and loads the default data rows */ Create Database SalesCo2; Use SalesCo2; CREATE TABLE VENDOR ( V_CODE INTEGER, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE)); CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) PRIMARY KEY, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATE NOT NULL, P_QOH Decimal NOT NULL, P_MIN Decimal NOT NULL, P_PRICE Decimal(8,2) NOT NULL, P_DISCOUNT Decimal(4,2) NOT NULL, V_CODE Decimal); CREATE TABLE CUSTOMER ( CUS_CODE Decimal PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3), CUS_PHONE CHAR(8) NOT NULL, CUS_BALANCE Decimal(9,2)); CREATE TABLE INVOICE ( INV_NUMBER Decimal PRIMARY KEY, CUS_CODE Decimal NOT NULL, INV_DATE DATE, INV_SUBTOTAL Decimal(9,2), INV_TAX Decimal(9,2), INV_TOTAL Decimal(9,2)); CREATE TABLE LINE ( INV_NUMBER Decimal NOT NULL, LINE_NUMBER Decimal(2,0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS Decimal(9,2) NOT NULL, LINE_PRICE Decimal(9,2) NOT NULL, LINE_TOTAL Decimal(9,2)); /* VENDOR rows */ INSERT INTO VENDOR VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y'); INSERT INTO VENDOR VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N'); INSERT INTO VENDOR VALUES(21231,'D\&E Supply' ,'Singh' ,'615','228-3245','TN','Y'); INSERT INTO VENDOR VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N'); INSERT INTO VENDOR VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N'); INSERT INTO VENDOR VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y'); INSERT INTO VENDOR VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N'); INSERT INTO VENDOR VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN','Y'); INSERT INTO VENDOR VALUES(25443,'B\&K, Inc.' ,'Smith' ,'904','227-0093','FL','N'); INSERT INTO VENDOR VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN','N'); INSERT INTO VENDOR VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL','Y'); /* PRODUCT rows */ INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'03-NOV-2011', 8, 5,109.99,0.00,25595); INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'13-DEC-2011', 32, 15, 14.99,0.05,21344); INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'13-NOV-2011', 18, 12, 17.49,0.00,21344); INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'15-JAN-2012', 15, 8, 39.95,0.00,23119); INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'15-JAN-2012', 23, 5, 43.99,0.00,23119); INSERT INTO PRODUCT VALUES('2232/QTY','B\&D jigsaw, 12-in. blade' ,'30-DEC-2011', 8, 5,109.92,0.05,24288); INSERT INTO PRODUCT VALUES('2232/QWE','B\&D jigsaw, 8-in. blade' ,'24-DEC-2011', 6, 5, 99.87,0.05,24288); INSERT INTO PRODUCT VALUES('2238/QPD','B\&D cordless drill, 1/2-in.' ,'20-JAN-2012', 12, 5, 38.95,0.05,25595); INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer' ,'20-JAN-2012', 23, 10, 9.95,0.10,21225); INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.' ,'02-JAN-2012', 8, 5, 14.40,0.05,NULL ); INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'15-DEC-2011', 43, 20, 4.99,0.00,21344); INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'07-FEB-2012', 11, 5,256.99,0.05,24288); INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'20-FEB-2011',188, 75, 5.87,0.00,NULL ); INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25' ,'01-MAR-2012',172, 75, 6.99,0.00,21225); INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50' ,'24-FEB-2012',237,100, 8.45,0.00,21231); INSERT INTO PRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2012', 18, 5,119.95,0.10,25595); /* CUSTOMER rows */ INSERT INTO CUSTOMER VALUES(10010,'Ramas' ,'Alfred','A' ,'615','844-2573',0); INSERT INTO CUSTOMER VALUES(10011,'Dunne' ,'Leona' ,'K' ,'713','894-1238',0); INSERT INTO CUSTOMER VALUES(10012,'Smith' ,'Kathy' ,'W' ,'615','894-2285',345.86); INSERT INTO CUSTOMER VALUES(10013,'Olowski' ,'Paul' ,'F' ,'615','894-2180',536.75); INSERT INTO CUSTOMER VALUES(10014,'Orlando' ,'Myron' ,NULL,'615','222-1672',0); INSERT INTO CUSTOMER VALUES(10015,'O''Brian','Amy' ,'B' ,'713','442-3381',0); INSERT INTO CUSTOMER VALUES(10016,'Brown' ,'James' ,'G' ,'615','297-1228',221.19); INSERT INTO CUSTOMER VALUES(10017,'Williams','George',NULL,'615','290-2556',768.93); INSERT INTO CUSTOMER VALUES(10018,'Farriss' ,'Anne' ,'G' ,'713','382-7185',216.55); INSERT INTO CUSTOMER VALUES(10019,'Smith' ,'Olette','K' ,'615','297-3809',0); /* INVOICE rows */ INSERT INTO INVOICE VALUES(1001,10014,'16-JAN-2012', 24.90, 1.99, 26.89); INSERT INTO INVOICE VALUES(1002,10011,'16-JAN-2012', 9.98, 0.80, 10.78); INSERT INTO INVOICE VALUES(1003,10012,'16-JAN-2012', 153.85, 12.31, 166.16); INSERT INTO INVOICE VALUES(1004,10011,'17-JAN-2012', 34.97, 2.80, 37.77); INSERT INTO INVOICE VALUES(1005,10018,'17-JAN-2012', 70.44, 5.64, 76.08); INSERT INTO INVOICE VALUES(1006,10014,'17-JAN-2012', 397.83, 31.83, 429.66); INSERT INTO INVOICE VALUES(1007,10015,'17-JAN-2012', 34.97, 2.80, 37.77); INSERT INTO INVOICE VALUES(1008,10011,'17-JAN-2012', 399.15, 31.93, 431.08); /* LINE rows */ INSERT INTO LINE VALUES(1001,1,'13-Q2/P2',1, 14.99, 14.99); INSERT INTO LINE VALUES(1001,2,'23109-HB',1, 9.95, 9.95); INSERT INTO LINE VALUES(1002,1,'54778-2T',2, 4.99, 9.98); INSERT INTO LINE VALUES(1003,1,'2238/QPD',1, 38.95, 38.95); INSERT INTO LINE VALUES(1003,2,'1546-QQ2',1, 39.95, 39.95); INSERT INTO LINE VALUES(1003,3,'13-Q2/P2',5, 14.99, 74.95); INSERT INTO LINE VALUES(1004,1,'54778-2T',3, 4.99, 14.97); INSERT INTO LINE VALUES(1004,2,'23109-HB',2, 9.95, 19.90); INSERT INTO LINE VALUES(1005,1,'PVC23DRT',12, 5.87, 70.44); INSERT INTO LINE VALUES(1006,1,'SM-18277',3, 6.99, 20.97); INSERT INTO LINE VALUES(1006,2,'2232/QTY',1, 109.92, 109.92); INSERT INTO LINE VALUES(1006,3,'23109-HB',1, 9.95, 9.95); INSERT INTO LINE VALUES(1006,4,'89-WRE-Q',1, 256.99, 256.99); INSERT INTO LINE VALUES(1007,1,'13-Q2/P2',2, 14.99, 29.98); INSERT INTO LINE VALUES(1007,2,'54778-2T',1, 4.99, 4.99); INSERT INTO LINE VALUES(1008,1,'PVC23DRT',5, 5.87, 29.35); INSERT INTO LINE VALUES(1008,2,'WR3/TT3' ,3, 119.95, 359.85); INSERT INTO LINE VALUES(1008,3,'23109-HB',1, 9.95, 9.95);