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

Case Exercises: Odd numbers, pg$.235.$

KimTay Pet Supplies

Use SQL to make the following changes to the KimTay Pet Supplies database $($see Figure $1-2$ in

Module $1).$ After each change, execute an appropriate query to show that the change was made

correctly. 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. For any exercises that use commands

not supported by your version of SQL$,$ write the command to accomplish the task.

$1.$ Create a view named MAJOR$\_$CUSTOMER. It consists of the customer ID$,$ first name,

last name, balance, credit limit$,$ and rep ID for every customer whose credit limit is $$500$ or

less.

a$.$ Write and execute the CREATE VIEW command to create the MAJOR$\_$CUSTOMER

view.

b$.$ Write and execute the command to retrieve the customer ID$,$ first name, and last name

of each customer in the MAJOR$\_$CUSTOMER view with a balance that exceeds the

credit limit$.$

c$.$ Write and execute the query that the DBMS actually executes.

d$.$ Does updating the database through this view create any problems? If so$,$ what are

they? If not, why not?

$2.$ Create a view named ITEM$\_$INVOICE. It consists of the item ID$,$ description, price, invoice

number, invoice date, number ordered, and quoted price for all invoice lines currently on

file.

a$.$ Write and execute the CREATE VIEW command to create the ITEM$\_$INVOICE view.

b$.$ Write and execute the command to retrieve the item ID$,$ description, invoice number,

and quoted price for all invoices in the ITEM$\_$INVOICE view for items with quoted

prices that exceed $$100.$

c$.$ Write and execute the query that the DBMS actually executes.

d$.$ Does updating the database through this view create any problems? If so$,$ what are

they? If not, why not?

$3.$ Create a view named INVOICE$\_$TOTAL. It consists of the invoice number and invoice

total for each invoice currently on file. $($The invoice total is the sum of the number of units

ordered multiplied by the quoted price on each invoice line for each invoice.$)$ Sort the rows

by invoice number. Use TOTAL$\_$AMOUNT as the name for the invoice total.

a$.$ Write and execute the CREATE VIEW command to create the INVOICE$\_$TOTAL view.

b$.$ Write and execute the command to retrieve the invoice number and invoice total for

only those orders totaling more than $$250.$

c$.$ Write and execute the query that the DBMS actually executes.

d$.$ Does updating the database through this view create any problems? If so$,$ what are

they? If not, why not?

$4.$ Write, but do not execute, the commands to do the following with the system catalog:

a$.$ List all the tables contained within the system catalog.

b$.$ List all the columns contained within the system catalog.

c$.$ List all the views contained within the system catalog.

$5.$ Write, but do not execute, the command to display only tables within the system catalog

that are of the type BASE TABLE.

$6.$ Perform the following tasks:

a$.$ Create an index named ITEM$\_$INDEX$1$ on the ITEM$\_$ID column in the INVOICE$\_$LINE

table.

b$.$ Create an index named ITEM$\_$INDEX$2$ on the CATEGORY column in the ITEM table.

c$.$ Create an index named ITEM$\_$INDEX$3$ on the CATEGORY and LOCATION columns

in the ITEM table.

d$.$ Create an index named ITEM$\_$INDEX$4$ on the CATEGORY and LOCATION columns

in the ITEM table. List categories in descending order.

$7.$ Delete the index named ITEM$\_$INDEX$3.$

$8.$ Write the commands to obtain the following information from the system catalog. Do not

execute these commands unless your instructor asks you to do so$.$

a$.$ List every table that you have created thus far.

b$.$ List every column in the ITEM table and its associated data type.

$9.$ Add the INVOICE$\_$NUM column as a foreign key in the INVOICE$\_$LINE table.

$10.$ Ensure that the only legal values for the CREDIT$\_$LIMIT column are $250,500,750,$

and $1000.$