RDBMS Lab 07
RDBMS Lab 07
RDBMS Lab 07
LAB # 7
VIEWS
OBJECTIVE
THEORY
A view is, at its core, nothing more than a stored query. What’s great is that you can
mix and match your data from base tables (or other views) to create what will, in most
respects, function just like an ordinary base table. You can create a simple query that
selects from only one table and leaves some rows or columns out, or you can create a
complex query that joins several tables and makes them appear as one.
Another view in the Northwind sample database selects every product in the
"Products" table with a unit price higher than the average unit price:
42
Lab # 7: VIEWS SWE-209
Another view in the Northwind database calculates the total sale for each category in
1997. Note that this view selects its data from another view called "Product Sales for
1997":
We can also add a condition to the query. Now we want to see the total sale only for
the category "Beverages":
Now we want to add the "Category" column to the "Current Product List" view. We
will update the view with the following SQL:
43
Lab # 7: Views SWE-209
Exercise:
1. From the following table, create a view for those salespersons belong to the city the
city of your choice..
2. From the following table, create a view for all salespersons. Return salesperson ID,
name, and city.
3. From the following table, create a view to find the salespersons of the city 'New York'.
5. From the following table, create a view to count the number of unique
customer, compute average and total purchase amount of customer
orders by each date.
ord_no purch_amt ord_date customer_id salesman_id
---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001
44