RDBMS Lab 07

Download as pdf or txt
Download as pdf or txt
You are on page 1of 3

Lab # 7: VIEWS SWE-209

LAB # 7

VIEWS

OBJECTIVE

 The nature of views, and how they can be used


 How to create, alter, and drop views using T-SQL

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.

SQL CREATE VIEW Syntax


CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE conditio

SQL CREATE VIEW Examples


If you have the Northwind database you can see that it has several views installed by
default.
The view "Current Product List" lists all active products (products that are not
Discontinued) from the "Products" table.

The view is created with the following SQL:

CREATE VIEW [Current Product List] AS


SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

We can query the view above as follows:

SELECT * FROM [Current Product List]

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

CREATE VIEW [Products Above Average Price] AS


SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the view above as follows:

SELECT * FROM [Products Above Average Price]

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":

CREATE VIEW [Category Sales For 1997] AS


SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

We can query the view above as follows:

SELECT * FROM [Category 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":

SELECT * FROM [Category Sales For 1997]


WHERE CategoryName='Beverages'

SQL Updating a View


You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now we want to add the "Category" column to the "Current Product List" view. We
will update the view with the following SQL:

CREATE OR REPLACE VIEW [Current Product List] AS


SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

43
Lab # 7: Views SWE-209

SQL Dropping a View


You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name

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'.

salesman_id | name | city | commission


-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
4. From the following table, create a view to count the number of
customers in each grade.

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

You might also like