lab 7-1 KimTay Pet Supplies. Need answers for all tasks not 1 or 2.

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.

2

Task 2: 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.

1

Task 3: Without using the MAJOR_CUSTOMER VIEW, retrieve the customer ID, first name, and last name for every customer whose credit limit is $500 or less.

1

Task 4: Create a VIEW named ITEM_INVOICE. It consists of the item ID, description, price, invoice number, invoice date, quantity, and quoted price for all invoice lines currently on file.

1

Task 5: 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 a quoted prices that exceed $100.

1

Task 6: Repeat Task 5 without using the ITEM_INVOICE VIEW to retrieve the item ID, description, price, invoice number, invoice date, quantity, and quoted price for all items with a quoted price that exceed $100.

1

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

2

Task 8: Using the INVOICE_TOTAL VIEW write and execute the command to retrieve the invoice number and invoice total for only those orders totaling more than $250.

1

Task 9: Repeat Task 8 without using the INVOICE_TOTAL VIEW.

1

Task 10: List all the tables contained within the system catalog, but only display the first 10 records with a TABLE_TYPE of SYSTEM VIEW.

1

Task 11: List all the columns contained within the system catalog, but only display the first 11 records that are in the KimTay TABLE_SCHEMA.

1

Task 12: List all the views contained within the system catalog, but only display the first 12 records.

1

Task 13: Write the command to display only tables within the system catalog that have the KimTay TABLE_SCHEMA and are of the type BASE TABLE. Display only the following columns: TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE.

1

Task 14: Create an index named ITEM_INDEX1 on the ITEM_ID column in the INVOICE_LINE table.

1

Task 15: Create an index named ITEM_INDEX2 on the CATEGORY column in the ITEM table.

1

Task 16: Create an index named ITEM_INDEX3 on the CATEGORY and LOCATION columns in the ITEM table.

1

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

1

Task 18: Delete the index named ITEM_INDEX3.

1

Task 19: List every table that you have created thus far. Display the TABLE_SCHEMA and TABLE_NAME columns.

1

Task 20: List every column in the ITEM table and its associated data type.

1

Task 21: Alter the INVOICE_LINE table by adding the INVOICE_NUM column as a foreign key referencing the INVOICE_NUM column in the INVOICES table.