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 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 System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

DO THIS IN MYSQL PLEASE!

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

109 2
FIGURE P8.31 CH08_SALECO2 DATABASE TABLES
Database name: Ch08_SaleCo2
Table name: CUSTOMER
Table name: INVOICE
CUS CODE CUS_LNAME CUS_FNAME CUS_INTIAL CUS_AREACODE CUS_PHONE CUS_BALANCE
INV_NUMBER CUS_CODE INV_DATE INV_SUBTOTAL
16-Jan-18
INV_TAX INV_TOTAL
26.89
10010 Ramas
10011 Dunne
Alfred
A
K
615
713
844-2573
894-1238
894-2285
0.00
0.00
1001
10014
24.90
1.99
Leona
1002
10011
16-Jan-18
9.98
0.80
10.78
10012 Smith
Kathy
615
345.86
1003
10012
16-Jan-18
153.85
12.31
166.16
10013 Olowski
Paul
F
615
894-2180
536 75
1004
10011
17-Jan-18
34.97
2.80
37.77
Myron
Amy
10014 Orlando
615
222-1672
0.00
0.00
221. 19
768.93
1005
10018
17-Jan-18
70.44
5.64
76.08
442-3381
297-1228
10015 O'Brian
в
713
10016 Brown
James
G
615
1006
10014
17-Jan-18
397.83
31.83
429.66
George
10017 Williams
10018 Farrics
10019 Smith
615
713
290-2556
382-7185
1007
10015
17-Jan-18
34.97
2.80
37.77
Anno
G
216.65
1008
10011
17-Jan-18
399.15
31.93
431.08
Olette
K
615
297-3809
0.00
Table name: LINE
Table name: PRODUCT
INV_NUMBER LINE_NUMBER P_CODE
1 13-Q2/P2
2 23109 HB
1 54778-2T
LINE UNITS
LINE_PRICE
14.99
9.95
LINE TOTAL
P_DESCRIPT
P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE
1001
1001
14.99
9.95
P_CODE
11QER/31 Power painter, 15 psi., 3-nozzle
13-02P2 7.25-in. pwr. saw blade
14-01A3 9.00-in, pwr, saw blade
1
03-Nov-17
5
109.99
0.00
25595
1002
4.99
9.98
13-Dec-17
32
15
14.99
0.05
21344
1003
1 2238/OPD
38.95
38.95
13-Nov-17
18
12
17.49
0.00
21344
39.95
14.99
1003
2 1546-QQ2
3 13-02/P2
1 54778-2T
2 23109-HE
1 EYC23DRT
1 SM-18277
2 2232/OTY
3 23109-HE
4 89 WRE-Q
39.95
1546-QQ2 Hrd. cloth, 1/4-in., 2x50
1558-QW1 Hrd. cloth, 1/2-in., 3x50
23119
23119
24288
15-Jan-18
15
8
39.95
0.00
1003
74.96
15-Jan-18
23
5
43.99
0.00
1004
3
4.99
14.97
2232/QTY B&D jigsaw, 12-in. blade
30-Dec-17
8
5
109.92
0.05
1004
2
9 95
19 90
2232/GME B&D jigsaw, 8-in. blade
24-Dec-17
6
99.87
0.05
24288
1005
12
5.67
70.44
2238/QPD B&D cordless drill, 1/2-in.
20-Jan-18
12
5
38.95
0.05
25595
1005
3
20.97
23109-HB Claw hammer
109 92
20-Jan-18
23
10
9.95
0.10
21225
106
23114-AA Sledge hammer, 12 lb
02-Jan-18
8
14.40
0.05
100s
9.95
9.95
1005
1007
1007
54778-2T Rat-tail file, 1/8-in. fine
15-Dec-17
0.00
21344
256.99
256.99
43
4.99
1 13-02/P2
2.54778-2T
1 PVC23DRT
2 WR/IT3
3 23103-HE
14.99
29.98
89-WRE-Q Hicut chain saw, 16 in.
07-Feb-18
11
256.99
0.05
24288
1
4 99
4 99
PVC23DRT PVC pipe, 3.5-in., 8-ft
20-Feb-18
188
75
5.87
0.00
1003
5
5.67
29.35
75
21225
SM-18277 1.25-in. metal screw, 25
SW-23116 2.5-in. wd. screw, 50
01-Mar-18
172
6.99
0.00
1008
3
119.96
359.85
24-Feb-18
237
100
8.45
0.00
21231
9 95
9 95
1008
1
VWR3/TT3 Steel matting, 4x8'x1/6", 5" mesh
17-Jan-18
18
119.95
0.10
25595
Table name: VENDOR
V CODE
21225 Bryson, Inc.
21226 SuperLoo, Inc.
21231 D&E Supply
V_NAME
V_CONTACT V AREACODE V PHONE | V_STATE V_ORDER
Smithson
615
223-3234
TN
Flushing
Singh
Ortega
Smith
904
215-8995
FL
N
228-3245
889-2546
615
TN
21344 Gomez Bros.
615
KY
N
901
901
22567 Dome Supply
23119 Randsats Lid.
24004 Brackman Bros.
24288 ORDVA, Inc.
25443 B&K, Inc.
25501 Damal Supplies
25595 Rubicon Systems Orton
678-1419
GA
N
Anderson
678-3998
GA
Browning
Hakford
615
228-1410
TN
615
898-1234
TN
Smith
904
227-0093
FL
N
Smythe
615
890-3529
TN
456-0092
FL
Transcribed Image Text:109 2 FIGURE P8.31 CH08_SALECO2 DATABASE TABLES Database name: Ch08_SaleCo2 Table name: CUSTOMER Table name: INVOICE CUS CODE CUS_LNAME CUS_FNAME CUS_INTIAL CUS_AREACODE CUS_PHONE CUS_BALANCE INV_NUMBER CUS_CODE INV_DATE INV_SUBTOTAL 16-Jan-18 INV_TAX INV_TOTAL 26.89 10010 Ramas 10011 Dunne Alfred A K 615 713 844-2573 894-1238 894-2285 0.00 0.00 1001 10014 24.90 1.99 Leona 1002 10011 16-Jan-18 9.98 0.80 10.78 10012 Smith Kathy 615 345.86 1003 10012 16-Jan-18 153.85 12.31 166.16 10013 Olowski Paul F 615 894-2180 536 75 1004 10011 17-Jan-18 34.97 2.80 37.77 Myron Amy 10014 Orlando 615 222-1672 0.00 0.00 221. 19 768.93 1005 10018 17-Jan-18 70.44 5.64 76.08 442-3381 297-1228 10015 O'Brian в 713 10016 Brown James G 615 1006 10014 17-Jan-18 397.83 31.83 429.66 George 10017 Williams 10018 Farrics 10019 Smith 615 713 290-2556 382-7185 1007 10015 17-Jan-18 34.97 2.80 37.77 Anno G 216.65 1008 10011 17-Jan-18 399.15 31.93 431.08 Olette K 615 297-3809 0.00 Table name: LINE Table name: PRODUCT INV_NUMBER LINE_NUMBER P_CODE 1 13-Q2/P2 2 23109 HB 1 54778-2T LINE UNITS LINE_PRICE 14.99 9.95 LINE TOTAL P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE 1001 1001 14.99 9.95 P_CODE 11QER/31 Power painter, 15 psi., 3-nozzle 13-02P2 7.25-in. pwr. saw blade 14-01A3 9.00-in, pwr, saw blade 1 03-Nov-17 5 109.99 0.00 25595 1002 4.99 9.98 13-Dec-17 32 15 14.99 0.05 21344 1003 1 2238/OPD 38.95 38.95 13-Nov-17 18 12 17.49 0.00 21344 39.95 14.99 1003 2 1546-QQ2 3 13-02/P2 1 54778-2T 2 23109-HE 1 EYC23DRT 1 SM-18277 2 2232/OTY 3 23109-HE 4 89 WRE-Q 39.95 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 1558-QW1 Hrd. cloth, 1/2-in., 3x50 23119 23119 24288 15-Jan-18 15 8 39.95 0.00 1003 74.96 15-Jan-18 23 5 43.99 0.00 1004 3 4.99 14.97 2232/QTY B&D jigsaw, 12-in. blade 30-Dec-17 8 5 109.92 0.05 1004 2 9 95 19 90 2232/GME B&D jigsaw, 8-in. blade 24-Dec-17 6 99.87 0.05 24288 1005 12 5.67 70.44 2238/QPD B&D cordless drill, 1/2-in. 20-Jan-18 12 5 38.95 0.05 25595 1005 3 20.97 23109-HB Claw hammer 109 92 20-Jan-18 23 10 9.95 0.10 21225 106 23114-AA Sledge hammer, 12 lb 02-Jan-18 8 14.40 0.05 100s 9.95 9.95 1005 1007 1007 54778-2T Rat-tail file, 1/8-in. fine 15-Dec-17 0.00 21344 256.99 256.99 43 4.99 1 13-02/P2 2.54778-2T 1 PVC23DRT 2 WR/IT3 3 23103-HE 14.99 29.98 89-WRE-Q Hicut chain saw, 16 in. 07-Feb-18 11 256.99 0.05 24288 1 4 99 4 99 PVC23DRT PVC pipe, 3.5-in., 8-ft 20-Feb-18 188 75 5.87 0.00 1003 5 5.67 29.35 75 21225 SM-18277 1.25-in. metal screw, 25 SW-23116 2.5-in. wd. screw, 50 01-Mar-18 172 6.99 0.00 1008 3 119.96 359.85 24-Feb-18 237 100 8.45 0.00 21231 9 95 9 95 1008 1 VWR3/TT3 Steel matting, 4x8'x1/6", 5" mesh 17-Jan-18 18 119.95 0.10 25595 Table name: VENDOR V CODE 21225 Bryson, Inc. 21226 SuperLoo, Inc. 21231 D&E Supply V_NAME V_CONTACT V AREACODE V PHONE | V_STATE V_ORDER Smithson 615 223-3234 TN Flushing Singh Ortega Smith 904 215-8995 FL N 228-3245 889-2546 615 TN 21344 Gomez Bros. 615 KY N 901 901 22567 Dome Supply 23119 Randsats Lid. 24004 Brackman Bros. 24288 ORDVA, Inc. 25443 B&K, Inc. 25501 Damal Supplies 25595 Rubicon Systems Orton 678-1419 GA N Anderson 678-3998 GA Browning Hakford 615 228-1410 TN 615 898-1234 TN Smith 904 227-0093 FL N Smythe 615 890-3529 TN 456-0092 FL
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
SQL Functions
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education