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.

1. Create a view named WEST_COAST_CUSTOMERS_V that consists of all the data for customers that live in Los Angeles and San Diego. Display all the customers using this view.

2. Construct a view called WEST_COAST_EMPLOYEES_V that consists of the employees first name, last name, job and hire date and the West Coast Sales department number and name they work for. (there are two sales departments; one for the East Coast and one for the West Coast). This view will only reference the West Coast sales department. You will need to use a Join. Display the data using the WEST_COAST_EMPLOYEES_V view to show only the employee names and the West Coast Sales department name.

3.Create a view named ORDERS_SHIPPED_V that lists all the columns in the ORD table for orders that have shipped. Use the WITH CHECK OPTION. Then attempt to add a record to this view that has shipped. Display your results. Then attempt to add a record to this view that has NOT shipped. Show your results. You want to see a Check Option Failed message.

4. Create a view named ORDERS_NOT_SHIPPED_DAYS_WAITING_V that lists the customer id, order date, order id and the number of days it has been waiting to be shipped in the ORD table for orders that have NOT been shipped yet. Use the NOW function for the current date along with the order date in the DATEDIFF function to calculate the days it has been waiting to be shipped. Name this four column Days_Waiting_To_Ship.

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