Chapter $8($A Guide to SQL$)$ Use the KimTay Pet Supplies database only. You must use Oracle.

Case Exercises: Odd numbers, pg$.284-285.$

KimTay Pet Supplies

Use the KimTay Pet Supplies database $($see Figure $1-2$ in Module $1)$ to complete the following

exercises. If directed to do so by your instructor, use the information provided with the Module $3$

Exercises to print your output or save it to a file.

$1.$ List the item ID and description for all items. The descriptions should appear in uppercase letters.

$2.$ List the customer ID and first and last names for all customers located in the city of Cody. Your query should ignore case. For example, a customer with the city Cody should be included, as should customers whose city is CODY, cody, cOdY, and so on$.$

$3.$ List the customer ID$,$ first and last names, and balance for all customers. The balance should be rounded to the nearest dollar.

$4.$ KimTay Pet Supplies is running a promotion that is valid for up to $20$ days after an order is placed. List the invoice number, customer ID$,$ customer first and last names, and the pro$-$motion date for each invoice. The promotion date is $20$ days after the invoice was placed.

$5.$ Write MySQL$,$ PL$/$SQL$,$ or T$-$SQL procedures to accomplish the following tasks:

a$.$ Obtain the name and credit limit of the customer whose ID currently is stored in I$\_$CUST$\_$ID$.$ Place these values in the variables I$\_$CUSTOMER$\_$NAME and I$\_$CREDIT$\_$LIMIT, respectively. Output the contents of I$\_$CUSTOMER$\_$NAME and I$\_$CREDIT$\_$LIMIT.

b$.$ Obtain the invoice date, customer ID$,$ and first and last names for the invoice whose number currently is stored in I$\_$INVOICE$\_$NUM. Place these values in the variables I$\_$INVOICE$\_$DATE, I$\_$CUST$\_$ID$,$ and I$\_$CUST$\_$NAME, respectively. Output the contents of I$\_$INVOICE$\_$DATE, I$\_$CUST$\_$ID$,$ and I$\_$CUST$\_$NAME.

c$.$ Add a row to the INVOICE table.

d$.$ Change the date of the invoice whose number is stored in I$\_$INVOICE$\_$NUM to the date currently found in I$\_$INVOICE$\_$DATE.

e$.$ Delete the invoice whose number is stored in I$\_$INVOICE$\_$NUM.

$6.$ Write MySQL$,$ PL$/$SQL$,$ or T$-$SQL procedures to retrieve and output the item ID$,$ description, location, and price of every item in the category stored in I$\_$CATEGORY.

$7.$ Write a stored procedure in MySQL$,$ PL$/$SQL$,$ or T$-$SQL that changes the price of an item with a given item ID$.$ How would you use this stored procedure to change the price of item

AD$72$ to $$84\mathrm{.}99?$

$8.$ Write the code for the following triggers in MySQL$,$ PL$/$SQL$,$ or T$-$SQL following the style shown in the text.

a$.$ When adding a customer, add the customer$$s balance multiplied by the sales rep$$s commission rate to the commission for the corresponding sales rep.

b$.$ When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep$$s commission rate to the commission for the corresponding sales rep.

c$.$ When deleting a customer, subtract the balance multiplied by the sales rep$$s commission rate from the commission for the corresponding sales rep.