Java Reference
In-Depth Information
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, as follows:
SELECT *
FROM ViewCorleones
The result set this query returns looks like this:
FIRST_NAME
MI
LAST_NAME
STREET
CITY
STATE
ZIP
Michael
A
Corleone
123 Pine
New York
NY
10006
Fredo
X
Corleone
17 Main
New York
NY
10007
Sonny
A
Corleone
123 Walnut
Newark
NJ
12346
Francis
X
Corleone
17 Main
New York
NY
10005
Vito
G
Corleone
23 Oak St
Newark
NJ
12345
As with any other table, you can use more complex queries. Here's an example:
SELECT *
FROM ViewCorleones
WHERE State = 'NJ'
This query returns the following 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
You can use a view for updating or deleting rows, as well as for retrieving data. Since the view is not a
table in its own right, but merely a way of looking at a table, rows updated or deleted in the view are
updated or deleted in the original table. For example, you can use the view to change Fredo
Corleone's street address by using the following SQL statement:
UPDATE ViewCorleones
SET Street = '19 Main'
WHERE First_Name = 'Fredo'
This example illustrates one of the advantages of using a view. A lot of the filtering required to identify
the target row is done in the view, so the SQL code is simpler and more maintainable. In a nontrivial
example, this can be a worthwhile improvement.
