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

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

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

I get the error below when I try to create this procedure. It should be in MYSQL.   Is my syntax for MYSQL wrong?  

 

CREATE PROCEDURE PRC_INV_AMOUNTS (W_IN IN NUMBER) 

W_CHK NUMBER = 0;

W_SUBT NUMBER = 0;

W_TAX NUMBER = 0;

SELECT COUNT(*) INTO W_CHK FROM INVOICE

WHERE INV_NUMBER = W_IN;

IF W_CHK = 1 THEN

 

SELECT SUM(LINE_TOTAL) INTO W_SUBT FROM LINE

WHERE LINE.INV_NUMBER = W_IN;

 

W_TAX := W_SUBT * 0.08;

 

UPDATE INVOICE

SET INV_SUBTOTAL = W_SUBT,

INV_TAX = W_TAX,

INV_TOTAL = W_SUBT + W_TAX

WHERE INV_NUMBER = W_IN;

END IF;

END;

 

 

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN NUMBER) W_CHK NUMBER = "0", W_SUBT NUMBER = "0", W_TAX NUMBER = "0" SELECT C' at line 1

 

The original problem asks for MYSQL code 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 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.

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

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 2 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Public key encryption
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