Hello everyone, so i\'m working on a SQL Stored Procedure problem in MySQL, but

Question

Hello everyone, so i'm working on a SQL Stored Procedure problem in MySQL, but can't seem to figure out the SQL. Any help?. The problem is below, as well as the database script file, Thanks

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 PhP MySQL..

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.

Database Script File:

/* CH08_SaleCo2.SQL */
/* 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);

Explanation / Answer

CREATE PROCEDURE SaleCo2 ( IN P_INV_NUMBER DECIMAL ) BEGIN SELECT CUS_CODE,INV_NUMBER,INV_TOTAL INTO #TEMP FrOM INVOICE WHERE INV_NUMBER =P_INV_NUM; DELETE FROM LINE WHERE INV_NUMBER = P_INV_NUMBER ; DELETE FROM INVOICE WHERE INV_NUMBER = P_INV_NUMBER; UPDATE TABLE CUSTOMER SET CUS_BALANCE =(SELECT c.CUS_BALANCE - T.INV_TOTAL FROM CUSTOMER C INNER JOIN #TEMP T ON C.CUS_CODE=T.CUS_CODE) WHERE CUS_CODE = (SELECT CUS_CODE FROM #TEMP) END ;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.