Database Model: KimTay
The management of KimTay Pet Supplies (a supplier of pet supplies, food, and accessories located in Cody, Wyoming) has determined that the company’s recent growth no longer makes it feasible to maintain customer, invoice, and inventory data using its manual systems. In addition, KimTay Pet Supplies wants to build an Internet presence. With the data stored in a database, management will be able to ensure that the data is up-to-date and more accurate than in the current manual systems. In addition, managers will be able to obtain answers to their questions concerning the data in the database easily and quickly, with the option of producing a variety of useful reports.
The CUSTOMER table maintains information about each customer, such as their ID, first and last name, address, balance, and credit limit.
CUSTOMER table
In the INVOICES table contains information about each invoice, such as the invoice number, date, and the customer being invoiced.
INVOICES table
The INVOICE_LINE table has the itemized information for each invoice. This includes the item ids, quantity, and price for each invoice.
INVOICE_LINE table
The ITEM table has a information pertaining to each item for sale by KimTay’s Pet Supplies. This includes a description, the number in stock, location, and price.
ITEM table
The SALES_REP table includes the information for each sales representative for KimTay’s Pet Supplies. This includes first and last name, address, cell-phone, commission, and commission rate.
SALES_REP table
Task 1: Create 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.
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.
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.
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.
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.
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.
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.
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.
Task 9: Repeat Task 8 without using the INVOICE_TOTAL VIEW. Use the LIMIT clause to select a limited number of records to display.
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.
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.
Task 12: List all the views contained within the system catalog, but only display the first 12 records.
