Chapter 11: Views: Limiting Access to Data

View definition with a computed column

Here is a view definition statement that creates a view with a computed column generated from the columns price, royalty, and total_sales:

create view accounts (title, advance, amt_due) 
as select titles.title_id, advance, 
(price * royalty /100) * total_sales 
from titles, roysched 
where price > $15 
and advance > $5000 
and titles.title_id = roysched.title_id 
and total_sales between lorange and hirange

There is no name that can be inherited by the column computed by multiplying together price, royalty, and total_sales, so you must include the list of columns in the create clause. The computed column is named amt_due. It must be listed in the same position in the create clause as the expression from which it is computed is listed in the select clause.

View definition with an aggregate or built-in function

View this book as PDF