ZuoTi.Pro
Question

Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL...

Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL,
INV_TAX, and INV_TOTAL. The procedure takes the invoice number
as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts
for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax
rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the
INV_TAX

/* Database Systems, Coronel/Morris */

/* Type of SQL : SQL Server */

/* WARNING: */

/* WE HIGHLY RECOMEND TO USE THE SQL SCRIPTS PROVIDED IN THE SQL FOLDER */

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);

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Below is the stored procedure prc_inv_amounts that will update the INV_SUBTOTAL, INV_TAX and INV_TOTAL based on given description.

Stored Procedure

CREATE PRODECURE prc_inv_amounts
@inv_num DECIMAL
BEGIN
UPDATE INVOICE SET
INV_SUBTOTAL = (SELECT SUM(LINE_TOTAL) FROM LINE WHERE INV_NUMBER = @inv_num),
INV_TAX = INV_SUBTOTAL * 0.8,
INV_TOTAL = INV_SUBTOTAL + INV_TAX
WHERE INV_NUMBER = @inv_num
END

0 1
Add a comment
Know the answer?
Add Answer to:
Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Using the ch08_saleco database write a T-SQL query to display the customer code, invoice number invoice...

    Using the ch08_saleco database write a T-SQL query to display the customer code, invoice number invoice date, and invoice subtotal from invoice conditioned on the invoice subtotal is greater than $100 and from only customer codes 10011 and 10012. (hint: in) /* Database Systems, 8th Ed., Rob/Coronel */ /* Type of SQL : SQL Server */ CREATE TABLE CUSTOMER ( CUS_CODE int, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT...

  • * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...

    * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...

  • Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB...

    Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB4DB; USE LAB4DB; CREATE TABLE CUSTOMER ( CUS_CODE int primary key, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); 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','','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','','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'); /*...

  • SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT...

    SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT NULL, PRIMARY KEY (vid) ); CREATE TABLE category ( catid CHAR(2) NOT NULL, catname VARCHAR(25) NOT NULL, PRIMARY KEY (catid) ); CREATE TABLE product ( pid CHAR(3) NOT NULL, pname VARCHAR(25) NOT NULL, price NUMERIC (7,2) NOT NULL, vid CHAR(2) NOT NULL, categoryid CHAR(2) NOT NULL, PRIMARY KEY (pid)); CREATE TABLE region ( rid CHAR NOT NULL, rname VARCHAR(25) NOT NULL, PRIMARY KEY (rid)...

  • We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and...

    We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR. This database model is shown in Figure 1. The database model in Figure 1 reflects the following business rules: - A customer may generate many invoices. Each invoice is generated by one customer. - An invoice contains one or more invoice lines. Each invoice line is associated with one invoice. - Each invoice line references one product. A product may be found in...

  • CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER,...

    CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER, LINE NUMBER Create SQL queries for displaying the following results 5. List the customers who have ordered product bearing product code "23109-HB. Display first names, last names of customers, invoice numbers and product codes 6. Display Vendor details (V_CODE, V NAME) and product details (P CODE, P DESCRIPT) and product quantity on hand in excess of product min quantity (give alias of Quantity above...

  • All the required files are attached to this question here in the form of images. Please refer the attached images and answer the questions. List the names and codes of vendors and the number of produ...

    All the required files are attached to this question here in the form of images. Please refer the attached images and answer the questions. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor AA has supplied xxx products, and vendor BB has supplied yyy products etc. Show the total value of invoices for each customer with the customer the Last Name, and a column heading showing ‘Total_invoice_value’ in the results. List...

  • CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT...

    CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK'); INSERT INTO DEPT VALUES (20,'HOME','DALLAS'); INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO'); INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, SAL FLOAT, COMM FLOAT, DEPTNO INTEGER NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), FOREIGN KEY (MGR) REFERENCES EMP(EMPNO), PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, 5000,NULL,10); INSERT INTO...

  • Question 1. For each table, identify the primary key and the foreign key(s). If a table...

    Question 1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. Question 2. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer. Question 3. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. Question 4. Describe the type(s) of relationship(s) between CUSTOMER...

  • Please provide SQL command to provide the product names that customer number C0954327 ordered in January...

    Please provide SQL command to provide the product names that customer number C0954327 ordered in January 2013 here is the database: CREATE DATABASE OrderEntryBMIS325 GO USE OrderEntryBMIS325 GO CREATE TABLE Customer ( CustNo CHAR(8), CustFirstName VARCHAR(20) CONSTRAINT CustFirstNameRequired NOT NULL, CustLastName VARCHAR(30) CONSTRAINT CustLastNameRequired NOT NULL, CustStreet VARCHAR(50), CustCity VARCHAR(30), CustState CHAR(2), CustZip CHAR(10), CustBal Decimal(12,2) DEFAULT 0, CONSTRAINT PKCustomer PRIMARY KEY (CustNo) ) INSERT INTO Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal) VALUES ( 'C0954327','Sheri','Gordon','336 Hill St.','Littleton','CO','80129-5543',$230.00...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT