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. 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.

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.

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.

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_INDEX1 on the ITEM_ID column in the INVOICE_LINE table.

b. Create an index named ITEM_INDEX2 on the CATEGORY column in the ITEM table.

c. Create an index named ITEM_INDEX3 on the CATEGORY and LOCATION columns in the ITEM table.

d. Create an index named ITEM_INDEX4 on the CATEGORY and LOCATION columns in the ITEM table. List categories in descending order.