SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 316

This SQL view joins TRANS, WORK, and ARTIST and creates the computed column
NetProfit. We can now use NetProfit in an SQL WHERE clause in a query, as follows:
/* *** SQL-Query-View-CH07-06 *** */
SELECT ArtistLastName, ArtistFirstName,
WorkID, Title, Copy, DateSold, NetProfit
FROM ArtistWorkNetView
WHERE NetProfit > 5000
ORDER BY DateSold;
Here we are using the result of a computation in a WHERE clause, something that is not al-
lowed in a single SQL statement. The result of the SQL SELECT statement is:
Such layering can be continued over many levels. We can define another view with an-
other computation on the computation in the first view. For example, note that in the results
above, the Horiuchi work Memories IV has been acquired and sold more than once by the View
Ridge Gallery, and then consider the SQL view ArtistWorkTotalNetView, which will calculate
the total net profit from all sales of each work:
/* *** SQL-CREATE-VIEW-CH07-07 *** */
CREATE VIEW ArtistWorkTotalNetView AS
SELECT ArtistLastName, ArtistFirstName,
WorkID, Title, Copy,
SUM(NetProfit) AS TotalNetProfit
FROM ArtistWorkNetView
GROUP BY ArtistLastName, ArtistFirstName,
WorkID, Title, Copy;
Now we can use TotalNetProfit in an SQL WHERE clause on the ArtistWorkTotalNet view,
as follows:
/* *** SQL-Query-View-CH07-07 *** */
SELECT *
FROM ArtistWorkTotalNetView
WHERE TotalNetProfit > 5000
ORDER BY TotalNetProfit;
In this SELECT, we are using an SQL view on an SQL view and a built-in function on a
computed variable in the WHERE clause. The results are as follows: