Views can be thought of as virtual tables. Any SELECT command can be used in a view definition except commands containing ORDER BY. Views can use GROUP BY clauses, subqueries, and joins. Disallowing ORDER BY is consistent with the fact that rows of a table in a relational database are not stored in any particular order. When you use the view, you can specify an ORDER BY.
You can also use views in more complicated queries:
CREATE VIEW Q1_Revenue AS SELECT Products.Name AS Item, Products.Description AS Style, SUM(SalesOrderItems.Quantity) AS Sales, Products.UnitPrice, SUM(SalesOrderItems.Quantity * Products.UnitPrice) AS Revenue FROM EmployeesJOIN SalesOrders ON SalesOrders.SalesRepresentative = Employees.EmployeeID JOIN SalesOrderItems ON SalesOrderItems.ID = SalesOrders.ID JOIN Products ON Products.ID = SalesOrderItems.ProductID WHERE SalesOrders.OrderDate >='2001-01-01' AND SalesOrders.OrderDate <='2001-04-30' GROUP BY Products.Description, Products.Name, Products.UnitPrice
Running the query creates a results set that looks like this:
Item |
Style |
Sales |
Unit Price |
Revenue |
|---|---|---|---|---|
Tee Shirt |
Tank Top |
744 |
9.00 |
6696.00 |
Sweatshirt |
Hooded Sweatshirt |
756 |
24.00 |
18144.00 |
Visor |
Cloth Visor |
756 |
7.00 |
5292.00 |
Baseball Cap |
Wool cap |
685 |
10.00 |
6850.00 |
Tee Shirt |
Crew Neck |
672 |
14.00 |
9408.00 |
Shorts |
Cotton Shorts |
1524 |
15.00 |
22860.00 |
Baseball Cap |
Cotton Cap |
900 |
9.00 |
8100.00 |
Tee Shirt |
V-neck |
780 |
14.00 |
10920.00 |
Sweatshirt |
Zipped Sweatshirt |
564 |
24.00 |
13536.00 |
Visor |
Plastic Visor |
684 |
7.00 |
4788.00 |
Views cannot be used in a join index.
