Database Reference
In-Depth Information
Placing computations in views has two major advantages. First, it saves users from
having to know or remember how to write an expression to get the results they want.
Second, it ensures consistent results. If each developer who uses a computation writes his
or her own SQL expression, that developer may write it differently and obtain inconsistent
results.
Using SQL Views to hide Complicated SQL Syntax
Another use of SQL views is to hide complicated SQL syntax. Using a view, developers need
not enter a complex SQL statement when they want a particular result. Also, such views give
the benefits of complicated SQL statements to developers who do not know how to write such
statements. This use of views also ensures consistency.
For example, suppose that the View Ridge Gallery salespeople want to see which custom-
ers are interested in which artists. To display these interests, two joins are necessary: one to
join CUSTOMER to CUSTOMER_ARTIST_INT and another to join that result to ARTIST. We
can code an SQL statement that constructs these joins and define it as an SQL view to create
the CustomerInterestsView:
/* *** SQL-CREATE-VIEW-CH07-05 *** */
CREATE VIEW CustomerInterestsView AS
SELECT C.LastName AS CustomerLastName,
C.FirstName AS CustomerFirstName,
A.LastName AS ArtistName
FROM CUSTOMER AS C JOIN CUSTOMER_ARTIST_INT AS CAI
ON C.CustomerID = CAI.CustomerID
JOIN ARTIST AS A
ON CAI.ArtistID = A.ArtistID;
Notice the aliasing of C.LastName to CustomerLastName and A.LastName to
ArtistLastName. We
must
use at least one of these column aliases, for without them the
resulting table has two columns named LastName. The DBMS would not be able to distin-
guish one LastName from the other and would generate an error when an attempt is made
to create such a view.
This is a complicated SQL statement to write, but once the view is created,
the result of this statement can be obtained with a simple SELECT statement. For
example, the following statement shows the results sorted by CustomerLastName and
CustomerFirstName:
/* *** SQL-Query-View-CH07-05 *** */
SELECT
*
FROM
CustomerInterestsView
ORDER BY
CustomerLastName, CustomerFirstName;
Figure 7-18 displays the fairly large result set. Clearly, using the view is much simpler than
constructing the join syntax. Even developers who know SQL well will appreciate having a
simpler SQL view with which to work.
Layering Built-in Functions
Recall from Chapter 2 that you cannot use a computation or a built-in function as part of an
SQL WHERE clause. You can, however, construct a view that computes a variable and then
Search WWH ::
Custom Search