Practical SQL: Creating and Using Views

This chapter is from the book

Practical SQL Handbook, The: Using SQL Variants, 4th Edition

Learn More Buy

This chapter is from the book

This chapter is from the book 

Practical SQL Handbook, The: Using SQL Variants, 4th Edition

Learn More Buy

How Views Work

What actually happens in the database when you create a view, and what happens when you use it?

Creating a view means defining it in terms of its base tables. The definition of the view is stored in the system catalogs without any data. When you query or perform data modification commands through the view, you are accessing the data that is stored in

association with the underlying tables. In other words, creating a view does not generate a copy of the data, neither at the time the view is defined nor at the time the view is accessed.

When you query a view, it looks exactly like any other database table. You can display it in much the same way as you can any other table, with few restrictions.

Changing data through a view has some limitations (explained later in this chapter). For now, consider the simplest case: a view based on a single table. When you modify the data you see through a view, you are actually changing the data in the underlying base table. Conversely, when you change the data in the base tables, these changes are automatically reflected in the views derived from them.

Suppose you are interested only in books priced higher than $15 and for which an advance of more than $5,000 was paid. This straightforward SELECT statement would find the rows that qualify:

SQL

select *
from titles
where price > $15
 and advance > $5000

Now suppose you have a slew of retrieval and update operations to do on this collection of data. You could, of course, combine the conditions shown in the previous query with any command that you issue. However, for convenience, you can create a view in which just the records of interest are visible:

SQL

create view hiprice
as
select *
from titles
where price > $15
 and advance > $5000

When SQL receives this command, it does not actually execute the SELECT statement that follows the keyword AS. Instead, it stores the SELECT statement (which is, in fact, the definition of the view hiprice) in the system catalogs.

Now, when you display or operate on hiprice, SQL combines your statement with the stored definition of hiprice. For example, you can change all the prices in hiprice just as you can change any other table:

SQL

update hiprice
set price = price*2

SQL actually finds the view definition in the system catalogs and converts this update command into the following statement:

SQL

update titles
set price = price*2
where price > $15
 and advance > $5000

In other words, SQL knows from the view definition that the data to be updated is in titles. It also knows that it should increase the prices only in those rows that meet the conditions on the price and advance columns given in the view definition.

Having issued the first update statement—the update to hiprice—you can see its effect either through the view or in the titles table. Conversely, if you had created the view and then issued the second update statement, which operates directly on the base table, the changed prices would also be visible through the view.

If you update a view's underlying table in such a way that more rows qualify for the view, they become visible through the view. For example, suppose you increase the price of the book You Can Combat Computer Stress to $25.95. Because this book now meets the qualifying conditions in the view definition statement, it becomes part of the view.

Naming View Columns

An issue to consider in how views work is view column names. Assigning alias names to a view's columns is required when either of these rules is fulfilled:

• One or more of the view's columns is a complex expression: It includes an arithmetic expression, a built-in function, or a constant.

• The view would wind up with more than one column of the same name (the view definition's SELECT statement includes a join, and the columns from the joined tables or views have the same name).

You can assign names to view columns or expressions in two ways:

• Put the names inside parentheses following the view name, separated by commas. Make sure there is a name for every item in the SELECT list. If you do one column name this way, you must do them all the same way.

• Allow the view to inherit names from the columns or display labels in the SELECT clause. Display labels are required for complex expressions and columns with name conflicts. Otherwise, you can leave column names as they are.

Figure 9.3 shows both methods.

Figure 9.3 Column Names in Views

Complex Expressions

Assigning column names in the CREATE VIEW clause can be illustrated with the currentinfo view discussed earlier in this chapter:

SQL

create view currentinfo (PUB#, TYPE, INCOME, 
 AVG_PRICE, AVG_SALES)
as
select pub_id, type, sum(price*ytd_sales),
 avg(price), avg(ytd_sales)
from titles
group by pub_id, type

The computed columns in the SELECT list don't really have names, so you must give them new names in the CREATE VIEW clause or assign display labels in the SELECT clause. Otherwise, you'd have no way to refer to them. When you work with the view currentinfo, always use the new names, like this:

SQL

select PUB#, AVG_SALES
from currentinfo

Using the old names, such as pub_id or avg(ytd_sales), won't work.

Duplicate Column Names

The second circumstance in which assigning new column names is required usually arises when there's a join in the SELECT statement and the joining columns have the same name. Even though they are qualified with different table names in the SELECT statement, you have to rename them in order to resolve the ambiguity:

SQL

create view cities (Author, Authorcity, Pub, Pubcity)
as
select au_lname, authors.city, pub_name, publishers.city
from authors, publishers
where authors.city = publishers.city

Of course, you are free to rename columns in a view definition statement whenever it's helpful to do so. Just remember that when you assign column names in the CREATE VIEW clause, the number and order of column names inside the parentheses has to match the number and order of items in the SELECT list.

Whether you rename a view column, its datatype and null status depend on how it was defined in its base table(s).

Creating Views with Multiple Underlying Objects

Another issue in how views work is the underlying objects. As you've seen, views can be based on one or many underlying objects. The objects can be connected with joins and/or subqueries and can be tables and/or views.

Using Subqueries and Joins

Here is an example of a view definition that includes three joins and a subquery. It finds the author ID, title ID, publisher, and price of each book with a price that's higher than the average of all the books' prices. (Including the author ID means that you'll see more than one row for books with multiple authors.)

SQL

create view highaverage
as
select authors.au_id, titles.title_id, pub_name, price
from authors, titleauthors, titles, publishers
where authors.au_id = titleauthors.au_id and
   titles.title_id = titleauthors.title_id and
   titles.pub_id = publishers.pub_id and
   price >
     (select avg(price)
     from titles)

Now that the view has been created, you can use it to display the results. In this example, you use the SELECT, WHERE, and ORDER BY clauses to tailor the information you see:

SQL

select price as Price, title_id as BookNum, au_id as 
        Writer 
from highaverage
where pub_name = 'Binnet & Hardley'
order by price, title_id 
     Price       BookNum  Writer   
     ==========  =======  ===========
          29.99  MC2222   712-45-1867
          29.99  TC7777   672-71-3249
          29.99  TC7777   267-41-2394
          29.99  TC7777   472-27-2349
          40.95  TC3218   807-91-6654
     [5 rows]

Deriving Views from Views

Let's use highaverage to illustrate a view derived from another view. Here's how to create a view that displays all the higher-than-average-priced books published by Binnet & Hardley:

SQL

create view highBandH
as 
select *
from highaverage
where pub_name = 'Binnet & Hardley'

You can structure your SELECT statement to limit columns and order rows.

SQL

select price, title_id, au_id
from highBandH
order by price, title_id
     price       title_id  au_id   
     ==========  ========  ===========
          29.99  MC2222    712-45-1867
          29.99  TC7777    672-71-3249
          29.99  TC7777    267-41-2394
          29.99  TC7777    472-27-2349
          40.95  TC3218    807-91-6654
    [5 rows]

Resolving Views

The process of combining a query on a view with its stored definition and translating it into a query on the view's underlying tables is called view resolution. Several problems can arise during this process. Be on the alert for issues arising from modifications of underlying tables or views:

• Adding columns to underlying tables or views for a view defined as SELECT * (SQL expands the asterisk shorthand at view creation, not at execution). A query of the view runs without an error message, but results don't include columns added to the underlying object after the view was created.

• Changing underlying tables or views by modifying column names or datatypes or by renaming or dropping the underlying objects. You'll probably get error messages when you query the view.

• Updating through views so that rows in the view become ineligible for it. You can use the WITH CHECK OPTION clause to prevent "hiding" data through a view update.

Adding Columns

A query of the view runs but does not display columns added to underlying objects after the view was created. This is because the asterisk is expanded at view creation, not at execution. In the following example, the addon table starts out with two columns:

SQL

create table addon
(name char(5),
num  int)

insert into addon
values ('one' , 1)

After a view is created, someone adds a column to the table.

SQL

create view addonv
as
select *
from addon

alter table addon
add status char(1) null

A query of the table shows three columns.

SQL

select *
from addon

name     num         status
=====    =========== ======
one                1 (NULL)
[1 row]

A query of the view shows only two columns—the two that existed when the view was created.

SQL

select *
from addonv
     name     num
     =====    ===========
     one                1
     [1 row]

Breaking Object Chains

Because views can be defined in terms of other views and tables, it's possible to wind up with a chain of objects, each dependent on another. Just as an actual chain can break at any link, so can a chain of objects. Any one of the views or tables in the chain might be redefined in such a way that its dependent views no longer make sense.

As an example, three generations of views derived from the authors table are shown here:

SQL

create view number1
as 
select au_lname, phone
from authors
where zip like '94%'

SQL

select * 
from number1
au_lname                                 phone    
======================================== ============
Bennet                                   415 658-9932
Green                                    415 986-7020
Carson                                   415 548-7723
Stringer                                 415 843-2991
Straight                                 415 834-2919
Karsen                                   415 534-9219
MacFeather                               415 354-7128
Dull                                     415 836-7128
Yokomoto                                 415 935-4228
White                                    408 496-7223
Hunter                                   415 836-7128
Locksley                                 415 585-4620
[12 rows]

SQL

create view number2
as 
select au_lname, phone
from number1
where au_lname > 'M'

SQL

select * 
from number2
au_lname                                 phone    
======================================== ============
Stringer                                 415 843-2991
Straight                                 415 834-2919
MacFeather                               415 354-7128
Yokomoto                                 415 935-4228
White                                    408 496-7223
[5 rows]

SQL

create view number3
as 
select au_lname, phone
from number2
where au_lname = 'MacFeather' 

SQL

select * 
from number3
au_lname                                 phone    
======================================== ============
MacFeather                               415 354-7128
[1 row]

What happens to number3 if you redefine number2 with different selection criteria?

• A condition on a column available in number2's underlying table number1 (either au_fname or phone) works fine. The WHERE clause determines what, if any, data is seen through number3.

• A condition such as a zip code matching "947nn" causes failure in creating number2 or displaying data through it because the zip column exists in neither number1 nor number2. With number2 in trouble, there's not much hope for retrieving data through number3.

Whatever you do to view number2, view number3 still exists and becomes usable again by dropping and re-creating view number2.

In short, some systems allow you to change the definition of an intermediate view without affecting dependent views as long as the target list of the dependent views remains valid. If you violate this rule, a query that references the invalid view produces an error message.

Using WITH CHECK OPTION

One of the problems with updating a view is that it is possible to change its values in such a way as to make the values ineligible for the view. For example, consider a view that displays all books with prices greater than $15.00. What happens if you update one of those book prices through the view, changing its price to $14.99? The optional WITH CHECK OPTION clause, which appears after the SELECT statement in the CREATE VIEW syntax, is designed to prevent such a problem:

CREATE VIEW view_name [(column_name [, column_name]...)]
AS
SELECT_statement
[WITH CHECK OPTION]

The WITH CHECK OPTION clause tells SQL to reject any attempt to modify a view in a way that makes one or more of its rows ineligible for the view. In other words, if a data modification statement (UPDATE, INSERT, or DELETE) causes some rows to disappear from the view, the statement is considered illegal.

As an example, recall the view hiprice, which includes all titles with price greater than $15 and advance greater than $5,000:

SQL

create view hiprice
as
select *
from titles
where price > $15
and advance > $5000

select title, price, advance 
from hiprice
title                                              price     advance
================================================== ========= =======
Secrets of Silicon Valley                              40.00 8000.00
Sushi, Anyone?                                         29.99 8000.00
But Is It User Friendly?                               42.95 7000.00
Life Without Fear                                      17.00 6000.00
Onions, Leeks, and Garlic: 
  Cooking Secrets of the Mediterranean                 40.95 7000.00
Computer Phobic and Non-Phobic Individuals: 
  Behavior Variations                                  41.59 7000.00
[6 rows]

This statement updates one of the books visible through hiprice, changing its price to $14.99.

SQL

update hiprice
set price = $14.99
where title = 'Secrets of Silicon Valley'

If WITH CHECK OPTION had been part of the definition of hiprice, the UPDATE statement would be rejected because the new price of Secrets of Silicon Valley would make it ineligible for the view. Because the definition of hiprice does not include the WITH CHECK OPTION clause, the UPDATE statement is accepted. But the next time you look at the data through hiprice, you'll no longer see Secrets of Silicon Valley.

create view checkv (Type, AvPrice)
as
select type, avg(price)
from titles
group by type
with check option

Server: Msg 4510, Level 16, State 1, Procedure checkv, Line 1
Could not perform CREATE VIEW because WITH CHECK OPTION was specified and the view is not updatable.

update checkv
set type = 'Psychology'
where type = 'psychology'

Error at line 1.
Update operation attempted on non-updatable query.