Database Concepts - Data Warehouse Systems: Design and Implementation - page 40

Views can be used for various purposes. They are used to structure data in
a way that users find it natural or intuitive. They can also be used to restrict
access to data such that users can have access only to the data they need.
Finally, views can also be used to summarize data from various tables, which
can be used, for example, to generate reports.
Views are created with the CREATE VIEW statement. To create a view, a
user must have appropriate system privileges to modify the database schema.
Once a view is created, it can then be used in a query as any other table.
For example, the following statement creates a view CustomerOrders that
computes for each customer and order the total amount of the order:

CREATE VIEW CustomerOrders AS (
SELECT O.CustomerID, O.OrderID,
SUM(D.Quantity * D.UnitPrice) AS Amount
FROM Orders O, OrderDetails D
WHERE O.OrderID = D.OrderID
GROUP BY O.CustomerID, O.OrderID )

This view is used in the next query to compute for each customer the
maximum amount among all her orders:

SELECT CustomerID, MAX(Amount) AS MaxAmount
FROM CustomerOrders
GROUP BY CustomerID

The result of this query is as follows.

CustomerID MaxAmount
ALFKI 1086.00

ANATR 514.40

ANTON 2156.50

AROUT 4675.00

BERGS 4210.50

... ...

As we will see in Chap. 7 , views can be materialized, that is, they can be
physically stored in a database.

Common Table Expressions

A common table expression (CTE) is a temporary table defined within
an SQL statement. Such temporary tables can be seen as views within the
scope of the statement. A CTE is typically used when a user does not have
the necessary privileges for creating a view.