CIS-336: Lab7: Working with Views

LAB OVERVIEW

Lab 7 will introduce the concept of database views.

Deliverables

Lab Report (Answer Sheet) containing both the student-created SQL command(s) for each exercise, and the output showing the results obtained. Some of the exercises will not have an output. I have adjusted the answer sheet to conform to the questions being asked below.

Be sure your LAST NAME is at the beginning of your filename.

Complete each of the exercises below using the Beaches database in MySQL.

5.Display the data from the ORDERS_NOT_SHIPPED_DAYS_WAITING_V view for orders that have not shipped between 30 and 60 days.

You will not be working with the group by clause and aggregate functions using MySQL. Remember that MySQL does not find all errors when using the group by function. You will need to double check your output to make sure it is correct.

6. Create a view called SALES_DATA_V that displays the customer ID, order id, item ID, Order date, product ID, Product description, and Item total (in the item table) for the order. This is a join using the ORD, ITEM and PRODUCT tables. Do NOT use an aggregate function yet. Watch for ambiguous columns.

7. Using the SALES_DATA_V view, display the data in the view sorted by customer ID.

8. Using the SALES_DATA_V view to display Customer id and the grand total sales for each customer (using an aggregate function). Give the grand total a column name Grand_Total.

Beaches Database Emp PK EmpNo Deptno LName Dname Fname Loc Job Customer HireDate PK Cust ID Sal LNa me Comm FName FK Deptno Street Ord City PK Ordid StateZip OrderDate Phone FK CustID FK ReplD ShipDa te Total Item PK FK Ordid Product PK temID PK Prodid FK ProdID Descrip QTY ProdPrice ItemTot