Java Reference
In-Depth Information
Creating a view
Rather than being created as a fundamental part of the underlying database, a view is
created using a query, as shown here:
CREATE VIEW ViewCorleones AS
SELECT *
FROM CUSTOMERS
WHERE Last_Name = 'Corleone'
Now you can execute a query just as if this view were a normal table:
SELECT *
FROM ViewCorleones
WHERE State = 'NJ'
This query would return this result set:
FIRST_NAME
MI
LAST_NAME
STREET
CITY
STATE
ZIP
Sonny
A
Corleone
123 Walnut
Newark
NJ
12346
Vito
G
Corleone
23 Oak St
Newark
NJ
12345
Since a view is really nothing more than a named result set, you can create a view by
joining multiple tables. One way to retrieve data from multiple tables is to use an
INNER JOIN. The following code snippet shows how to use an INNER JOIN to create
a view called "Orders_by_Name":
CREATE VIEW Orders_by_Name AS
SELECT c.LAST_NAME + ', ' + c.FIRST_NAME AS Name,
COUNT(i.Item_Number) AS Items, SUM(oi.Qty * i.Cost)
AS Total
FROM ORDERS o INNER JOIN
ORDERED_ITEMS oi ON
o.Order_Number = oi.Order_Number INNER JOIN
INVENTORY i ON
oi.Item_Number = i.Item_Number INNER JOIN
CUSTOMERS c ON
o.Customer_Number = c.CUSTOMER_NUMBER
GROUP BY c.LAST_NAME + ', ' + c.FIRST_NAME
In effect, any result set returned that a SELECT statement returns can be used to
create a view. That means you can use nested queries, JOINS, or UNIONS as well
as simple SELECTS.
