Lab 8-1: KimTay Pet Supplies Task 6: Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the customer, and the invoice date for the invoice whose number currently is stored in I_INVOICE_NUM. Place these values in the variables I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE respectively. When the procedure is called it should output the contents of I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE. IN SQL CODE. CUSTOMER CUST_ID FIRST_NAME LAST_NAME ADDRESS CITY STATE POSTAL EMAIL BALANCE CREDIT_LIMIT REP_ID 125 Joey Smith 17 Fourth St Cody WY 82414 jsmith17@example.com 80.68 500.00 05 182 Billy Rufton 21 Simple Cir Garland WY 82435 billyruff@example.com 43.13 750.00 10 227 Sandra Pincher 53 Verde Ln Powell WY 82440 spinch2@example.com 156.38 500.00 15 294 Samantha Smith 14 Rock Ln Ralston WY 82440 ssmith5@example.com 58.60 500.00 10 314 Tom Rascal 1 Rascal Farm Rd Cody WY 82414 trascal3@example.com 17.25 250.00 15 375 Melanie Jackson 42 Blackwater Way Elk Butte WY 82433 mjackson5@example.com 252.25 250.00 05 435 James Gonzalez 16 Rockway Rd Wapiti WY 82450 jgonzo@example.com 230.40 1000.00 15 492 Elmer Jackson 22 Jackson Farm Rd Garland WY 82435 ejackson4@example.com 45.20 500.00 10 543 Angie Hendricks 27 Locklear Ln Powell WY 82440 ahendricks7@example.com 315.00 750.00 05 616 Sally Cruz 199 18th Ave Ralston WY 82440 scruz5@example.com 8.33 500.00 15 721 Leslie Smith 123 Sheepland Rd Elk Butte WY 82433 lsmith12@example.com 166.65 1000.00 10 795 Randy Blacksmith 75 Stream Rd Cody WY 82414 rblacksmith6@example.com 61.50 500.00 05 INVOICES INVOICE_NUM INVOICE_DATE CUST_ID 14216 2021-11-15 125 14219 2021-11-15 227 14222 2021-11-16 294 14224 2021-11-16 182 14228 2021-11-18 435 14231 2021-11-18 125 14233 2021-11-18 435 14237 2021-11-19 616 Test Query CALL GET_INVOICE_DATE('14219') Expected Results I_CUST_ID I_CUST_NAME I_INVOICE_DATE 227 Sandra Pincher 2021-11-15

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Lab 8-1: KimTay Pet Supplies

Task 6: Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the customer, and the invoice date for the invoice whose number currently is stored in I_INVOICE_NUM. Place these values in the variables I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE respectively. When the procedure is called it should output the contents of I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE.

IN SQL CODE.

CUSTOMER

CUST_ID	FIRST_NAME	LAST_NAME	ADDRESS	CITY	STATE	POSTAL	EMAIL	BALANCE	CREDIT_LIMIT	REP_ID
125	Joey	Smith	17 Fourth St	Cody	WY	82414	jsmith17@example.com	80.68	500.00	05
182	Billy	Rufton	21 Simple Cir	Garland	WY	82435	billyruff@example.com	43.13	750.00	10
227	Sandra	Pincher	53 Verde Ln	Powell	WY	82440	spinch2@example.com	156.38	500.00	15
294	Samantha	Smith	14 Rock Ln	Ralston	WY	82440	ssmith5@example.com	58.60	500.00	10
314	Tom	Rascal	1 Rascal Farm Rd	Cody	WY	82414	trascal3@example.com	17.25	250.00	15
375	Melanie	Jackson	42 Blackwater Way	Elk Butte	WY	82433	mjackson5@example.com	252.25	250.00	05
435	James	Gonzalez	16 Rockway Rd	Wapiti	WY	82450	jgonzo@example.com	230.40	1000.00	15
492	Elmer	Jackson	22 Jackson Farm Rd	Garland	WY	82435	ejackson4@example.com	45.20	500.00	10
543	Angie	Hendricks	27 Locklear Ln	Powell	WY	82440	ahendricks7@example.com	315.00	750.00	05
616	Sally	Cruz	199 18th Ave	Ralston	WY	82440	scruz5@example.com	8.33	500.00	15
721	Leslie	Smith	123 Sheepland Rd	Elk Butte	WY	82433	lsmith12@example.com	166.65	1000.00	10
795	Randy	Blacksmith	75 Stream Rd	Cody	WY	82414	rblacksmith6@example.com	61.50	500.00	05

INVOICES

INVOICE_NUM	INVOICE_DATE	CUST_ID
14216	2021-11-15	125
14219	2021-11-15	227
14222	2021-11-16	294
14224	2021-11-16	182
14228	2021-11-18	435
14231	2021-11-18	125
14233	2021-11-18	435
14237	2021-11-19	616

Test Query

CALL GET_INVOICE_DATE('14219')

Expected Results

I_CUST_ID	I_CUST_NAME	I_INVOICE_DATE
227	Sandra Pincher	2021-11-15

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

This kind of code gives me errors i have more info below.

My code
DELIMITER //
CREATE PROCEDURE GET_INVOICE_DATE
(in I_INVOICE_NUM CHAR(5))
DECLARE I_CUST_ID CHAR(5),
DECLARE I_CUST_NAME VARCHAR(41),
DECLARE I_INVOICE_DATE DATE

BEGIN

SELECT c.CUST_ID, CONCAT(c.FIRST_NAME, ' ', c.LAST_NAME), i.INVOICE_DATE

INTO I_CUST_ID, I_CUST_NAME, I_INVOICE_DATE

FROM INVOICES i

INNER JOIN CUSTOMER c

ON i.CUST_ID = c.CUST_ID

WHERE i.INVOICE_NUM = I_INVOICE_NUM;

END //

Delimiter ;

Helpfull info

Task #6 – same process as Task #5
• Procedure name is GET_INVOICE_DATE with I_INVOICE_NUM in parentheses with 5 characters
• There should be 3 declare statements for I_CUST_ID CHAR(5); I_CUST_NAME VARCHAR(41); and
I_INVOICE_DATE DATE is datatype
• SELECT statement should include the CUSTOMER.CUST_ID, CONCAT for first_name and
last_name AS customer_name, invoice_date
• INTO the 3 “I” fields in the declare statement
• From should include the CUSTOMER and INVOICES tables
• Where clause to join CUSTOMER.CUST_ID = INVOICES.CUST_ID AND INVOICES.INVOICE_NUM,
I_INVOICE_NUM
• SELECT statement should be the 3 “I” fields
• Make sure to include the last 2 lines (END // and Delimiter ; )

Solution

by Bartleby Expert

SEE SOLUTION

Follow-up Question

Code like this gets me an error

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 '@INVOICE_NUM nvarchar(10) AS SELECT c.I_CUST_ID, c.I_CUST_NAME, i.I_INVOICE_DA' at line 1

The code in my study material is

DELIMITER //

CREATE PROCEDURE GET_INVOICE_DATE

Begin

END

DELIMITER ;

The call code is CALL GET_INVOICE_DATE('14219')

Solution

by Bartleby Expert

SEE SOLUTION