Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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.

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
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
See similar textbooks
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

SEE SOLUTIONCheck out a sample Q&A here
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
SEE MORE TEXTBOOKS