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.

9. Now join the SALES_DATA_V view to the CUSTOMER table to display the customer last name and last name along with the grand total sales for each customer (using an aggregate function). Caveat: If you want to include the first name as well, then you need to modify your Group By clause to provide it correctly. Remember that MySQL allows errors in the Group By clause.

10. Create a view called SALES_PER_DEPT_V that includes only the employee ID (sales rep), employee last name, employee first name, order id, department number, and total for each order. Do NOT include the Dept table. You will use the Dept table later. This is a join. You will need to join three tables. Watch for ambiguous columns.

11. Create a query to display the data in the SALES_PER_DEPT_V view sorted by employee id (sales rep) in ascending order.

12. Create a query using the SALES_PER_DEPT_V view and the appropriate aggregate function to display the number of sales for each sales rep LAST NAME. Give the aggregate function column the name NUMBER_OF_SALES;

13. Create a join query using the SALES_PER_DEPT_V view and the DEPT table to display the department name along with the average sales for each department. Sort the report by the department name in descending order. Round the average sales amount to the nearest dollar.

This is the end of Lab 7.

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