Chapter $5($A Guide to SQL$)$ Use the KimTay Pet Supplies database only.

Case Exercises: Odd numbers, pg$.169-170.$

KimTay Pet Supplies

Use SQL and 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 document.

$1.$ For each invoice, list the invoice number and invoice date along with the ID$,$ first name, and

last name of the customer for which the invoice was created.

$2.$ For each invoice placed on November $15,2021,$ list the invoice number along with the ID$,$

first name, and last name of the customer for which the invoice was created.

$3.$ For each invoice, list the invoice number, invoice date, item ID$,$ quantity ordered, and

quoted price for each invoice line that makes up the invoice.

$4.$ Use the IN operator to find the ID$,$ first name, and last name of each customer for which as

invoice was created on November $15,2021.$

$5.$ Repeat Exercise $4,$ but this time use the EXISTS operator in your answer.

$6.$ Find the ID$,$ first name, and last name of each customer for which an invoice was not

created on November $15,2021.$

$7.$ For each invoice, list the invoice number, invoice date, item ID$,$ description, and category

for each item that makes up the invoice.

$8.$ Repeat Exercise $7,$ but this time order the rows by category and then by invoice number.

$9.$ Use a subquery to find the sales rep ID$,$ first name, and last name of each sales rep who

represents at least one customer with a credit limit of $$500.$ List each sales rep only once

in the results.

$10.$ Repeat Exercise $9,$ but this time do not use a subquery.

$11.$ Find the ID$,$ first name, and last name of each customer that currently has an invoice on

file for Wild Bird Food $(25$ lb$).$

$12.$ List the item ID$,$ description, and category for each pair of items that are in the same

category. $($For example, one such pair would be item FS$42$ and item PF$19,$ because the

category for both items is FSH$.)$

$13.$ List the invoice number and invoice date for each invoice created for the customer James

Gonzalez.

$14.$ List the invoice number and invoice date for each invoice that contains an invoice line for a

Wild Bird Food $(25$ lb$).$

$15.$ List the invoice number and invoice date for each invoice that either was created for James

Gonzalez or that contains an invoice line for Wild Bird Food $(25$ lb$).$

$16.$ List the invoice number and invoice date for each invoice that was created for James

Gonzalez and that contains an invoice line for Wild Bird Food $(25$ lb$).$

$17.$ List the invoice number and invoice date for each invoice that was created for James

Gonzalez but that does not contain an invoice line for Wild Bird Food $(25$ lb$).$

$18.$ List the item ID$,$ description, unit price, and category for each item that has a unit price

greater than the unit price of every item in category CAT. Use either the ALL or ANY

operator in your query. $($Hint: Make sure you select the correct operator.$)$

$19.$ For each item, list the item ID$,$ description, units on hand, invoice number, and quantity

ordered. All items should be included in the results. For those items that are currently not

on an invoice, the invoice number and quantity ordered should be left blank. Order the

results by item ID$.$