Database Programming


In this chapter

Creating a View with CREATE VIEW

Retrieving Data Through a View

Updating Data Through a View

Dropping a View with DROP VIEW

A view is a stored SELECT statement that returns a table whose data are derived from one or more other tables (called underlying tables). Some important characteristics of a view are:

Creating a View with CREATE VIEW

Think of a view as being a tailored presentation that provides a tabular window into one or more base tables. The window can display an entire base table, part of a base table, or a combination of base tables (or parts thereof). A view also can reflect the data in base tables through other views—windows into windows. Generally, SQL programmers use views to present data to end-users in database applications. Views offer these advantages:

Simplified data access.Views hide data complexity and simplify statements, so users can perform operations on a view more easily than on the base tables directly. If you create a complex view—one that involves, say, multiple base tables, joins, and subqueries—then users can query this view without having to understand complex relational concepts or even knowing that multiple tables are involved.

Automatic updating.When a base table is updated, all views that reference the table reflect the change automatically. If you insert a row representing a new author into the table authors, for example, then all views defined over authors will reflect the new author automatically. This scheme saves storage space and prevents redundancy because, without views, the DBMS would have to store derived data to keep it synchronized.

Increased security.One of the most common uses of views is to hide data from users by filtering the underlying tables. Suppose that the table employees contains the columns salary and commission. If you create a view on employees that omits these two columns but contains other innocuous columns (such as email_address), then the database administrator can grant users permission to see the view but not see the underlying table, thereby hiding compensation data from the curious.

Logical data independence.Base tables provide a real view of a database. But when you use SQL to build a database application, you want to present end users not the real view, but a virtual view specific to the application. The virtual view hides the parts of the database (entire tables or specific rows or columns) that aren’t relevant to the application. Thus, users interact with the virtual view, which is derived from—although independent of—the real view presented by the base tables.

A virtual view immunizes an application from logical changes in the design of the database. Suppose that many applications access the table titles. Books go out of print over time, so the database designer decides to reduce the system load by segregating out-of-print books. He splits titles into two tables: in_print_titles and out_of_print_titles. Consequently, all the applications break because they expect the now-unavailable table titles.

But if those applications had accessed a view of titles instead of the real table, then that view could be redefined to be the UNION of in_print_titles and out_of_print_titles (see “Combining Rows with UNION” in Chapter 9). The applications transparently would see the two new tables as though they were the one original table and continue to work as though the split never happened. (You can’t use views to immunize an application against all changes, however. Views can’t compensate for dropped tables or columns, for example.)

When you’re creating a view, some important considerations are:

To create a view:

Listing 13.1Create a view that hides the authors’ personal information (telephone numbers and addresses).

CREATE VIEW au_names
  SELECT au_id, au_fname, au_lname
    FROM authors;

Listing 13.2Create a view that lists the authors who live in a city in which a publisher is located. Note that I use the column names au_city and pub_city in the view. Renaming these columns resolves the ambiguity that would arise if both columns inherited the same column name city from the underlying tables.

  (au_id, au_city, pub_id, pub_city)
  SELECT a.au_id, a.city, p.pub_id, p.city
    FROM authors a
    INNER JOIN publishers p
      ON a.city = p.city;

Listing 13.3Create a view that lists total revenue (= price × sales) grouped by book type within publisher. This view will be easy to query later because I name the result of an arithmetic expression explicitly rather than let the DBMS assign a default name.

CREATE VIEW revenues
  (Publisher, BookType, Revenue)
  SELECT pub_id, type, SUM(price * sales)
    FROM titles
    GROUP BY pub_id, type;

Listing 13.4Create a view that makes it easy to print mailing labels for authors. Note that I assigned column names in the SELECT clause rather than in the CREATE VIEW clause.

CREATE VIEW mailing_labels
    TRIM(au_fname || ' ' || au_lname)
      AS "address1",
      AS "address2",
    TRIM(city) || ', ' || TRIM(state) || ' ' || TRIM(zip)
      AS "address3"
    FROM authors;

Listing 13.5Create a view that lists the last names of authors A02 and A05, and the books that each one wrote (or cowrote). Note that this statement uses a nested view: it references the view au_names created by Listing 13.1.

CREATE VIEW au_titles (LastName, Title)
  SELECT an.au_lname, t.title_name
    FROM title_authors ta
    INNER JOIN au_names an
      ON ta.au_id = an.au_id
    INNER JOIN titles t
      ON t.title_id = ta.title_id
    WHERE an.au_id in ('A02','A05');


Retrieving Data Through a View

Creating a view displays nothing. All that CREATE VIEW does is cause the DBMS to save the view as a named SELECT statement. To see data through a view, query the view by using SELECT, just as you would query a table. You can:

To retrieve data through a view:

Listing 13.6List all the rows and columns of the view au_titles. See Figure 13.1 for the result.

  FROM au_titles;

Figure 13.1Result of Listing 13.6.

LastName  Title
--------- -----------------------------
Kells     Ask Your System Administrator
Heydemark How About Never?
Heydemark I Blame My Mother
Heydemark Not Without My Faberge Egg
Heydemark Spontaneous, Not Annoying

Listing 13.7List the distinct cities in the view cities. See Figure 13.2 for the result.

  FROM cities;

Figure 13.2Result of Listing 13.7.

New York
San Francisco

Listing 13.8List the types of books whose average revenue exceeds $1 million. See Figure 13.3 for the result.

SELECT BookType,
    AVG(Revenue) AS "AVG(Revenue)"
  FROM revenues
  GROUP BY BookType
  HAVING AVG(Revenue) > 1000000;

Figure 13.3Result of Listing 13.8.

BookType  AVG(Revenue)
--------- ------------
biography 18727318.50
computer   1025396.65
psychology 2320933.76

Listing 13.9List the third line of the mailing address of each author whose name contains the string Kell. See Figure 13.4 for the result.

SELECT address3
  FROM mailing_labels
  WHERE address1 LIKE '%Kell%';

Figure 13.4Result of Listing 13.9.

New York, NY 10014
Palo Alto, CA 94305

Listing 13.10List the name of each author who wasn’t the lead author of at least one book. See Figure 13.5 for the result.

SELECT DISTINCT an.au_fname, an.au_lname
  FROM au_names an
  INNER JOIN title_authors ta
    ON an.au_id = ta.au_id
  WHERE ta.au_order > 1;

Figure 13.5Result of Listing 13.10.

au_fname au_lname
-------- --------
Hallie   Hull
Klee     Hull

Listing 13.11List the names of the authors from California. See Figure 13.6 for the result.

SELECT au_fname, au_lname
  FROM au_names
  WHERE state = 'CA';

Figure 13.6Result of Listing 13.11. The view au_names references authors but hides the column state, so referring to state through the view causes an error.

ERROR: Invalid column name 'state'.

Tips for Retrieving Data Through Views

Updating Data Through a View

An updateable view is a view to which you can apply INSERT, UPDATE, and DELETE operations to modify data in the underlying table(s). Any changes made in an updateable view always pass through to the base table(s) unambiguously. The syntax for the INSERT, UPDATE, and DELETE statements is the same for views as it is for tables; see Chapter 10.

A nonupdateable view (or read-only view) view is one that doesn’t support INSERT, UPDATE, and DELETE operations because changes would be ambiguous. To change the data that appear in a read-only view, you must change the underlying table(s) directly (or through another, nonambiguous view).

Each row in an updateable view is associated with exactly one row in an underlying base table. A view isn’t updateable if its SELECT statement uses GROUP BY, HAVING, DISTINCT, or aggregate functions, for example.

The SQL-92 standard said that an updateable view must be defined over only one table, which is stringent but very safe. The SQL:1999 standard relaxed that restriction because many more types of updateable views exist. By the time that standard was released, the DBMS vendors already offered an expanded set of updateable views. Single-table views always are updateable. DBMSs also examine the underlying tables’ joins and referential-integrity constraints of a multitable view to determine whether the view is updateable. Here are some of the types of queries that can define updateable views:

The examples in this section use updateable views that reference only one underlying table. See your DBMS documentation to find out which multitable views you can update and how those updates affect each base table.

Inserting a Row Through a View

Consider the view ny_authors, which consists of the IDs, names, and states of only those authors from New York State (Listing 13.12 and Figure 13.7). ny_authors references only the base table authors.

Listing 13.12Create and display the view ny_authors, which lists the IDs, names, and states of only those authors from New York state. See Figure 13.7 for the result.

CREATE VIEW ny_authors
  SELECT au_id, au_fname, au_lname, state
    FROM authors
    WHERE state = 'NY';

  FROM ny_authors;

Figure 13.7Result of Listing 13.12: the view ny_authors.

au_id au_fname  au_lname state
----- --------- -------- -----
A01   Sarah     Buchman  NY
A05   Christian Kells    NY

Listing 13.13 inserts a new row through a view. The DBMS inserts a new row into the table authors. The row contains A08 in the column au_id, Don in au_fname, Dawson in au_lname, and NY in state. The other columns in the row—phone, address, city, and zip—are set to null (or their default values, if DEFAULT constraints exist).

Listing 13.13Insert a new row through the view ny_authors.

INSERT INTO ny_authors

Listing 13.14, like Listing 13.13, inserts a new row through a view. But this time, the new author is from California, not New York, which violates the WHERE condition in the view’s definition. Does the DBMS insert the row or cancel the operation? The answer depends on how the view was created. In this particular example, the insertion is allowed because the CREATE VIEW statement (see Listing 13.12) lacks a WITH CHECK OPTION clause, so the DBMS isn’t forced to maintain consistency with the view’s original definition. For information about WITH CHECK OPTION, see the DBMS tip in “Tips for CREATE VIEW” earlier in this chapter. The DBMS would have canceled the insertion if ny_authors were defined as:

CREATE VIEW ny_authors AS
  SELECT au_id, au_fname, au_lname, state
    FROM authors
    WHERE state = 'NY'

Listing 13.14Insert a new row through the view ny_authors. The DBMS would cancel this insertion if WITH CHECK OPTION had been used when ny_authors was created.

INSERT INTO ny_authors

Updating a Row Through a View

Listing 13.15 updates an existing row through a view. The DBMS updates the row for author A01 in the table authors by changing the author’s name from Sarah Buchman to Yasmin Howcomely. The values in the other columns in the row—au_id, phone, address, city, state, and zip—don’t change.

Listing 13.15Update an existing row through the view ny_authors.

UPDATE ny_authors
  SET au_fname = 'Yasmin',
      au_lname = 'Howcomely'
  WHERE au_id = 'A01';

But suppose that Listing 13.15 looked like this:

UPDATE ny_authors
  SET au_fname = 'Yasmin',
      au_lname = 'Howcomely',
      state = 'CA'
  WHERE au_id = 'A01';

This statement presents the same problem as Listing 13.14: the desired change would cause Yasmin’s row to no longer meet the conditions for membership in the view. Again, the DBMS will accept or reject the UPDATE depending on whether the WITH CHECK OPTION clause was specified when the view was created. If WITH CHECK OPTION is used, then rows can’t be modified in a way that causes them to disappear from the view.

Deleting a Row Through a View

Listing 13.16 deletes a row through a view. The DBMS deletes the row for author A05 in the table authors. (Every column in the row is deleted, not just those in the view.) In turn, the row disappears from the view ny_authors.

Listing 13.16Delete a row through the view ny_authors.

DELETE FROM ny_authors
  WHERE au_id = 'A05';

View updates can have integrity repercussions, of course. The DBMS will disallow a deletion if removing a row violates a referential-integrity constraint; see “Specifying a Foreign Key with FOREIGN KEY” in Chapter 11. If you delete a row, then all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the deletion to succeed. Some updating can be handled by the CASCADE option (if specified) of a FOREIGN KEY constraint, not by the view definition.

In Listing 13.16, for example, the DBMS will cancel the DELETE if I don’t first change or delete the foreign-key values in the table title_authors that point to author A05 in authors.

Tips for Updating Data Through a View

Dropping a View with DROP VIEW

Use the DROP VIEW statement to destroy a view. Because a view is physically independent of its underlying table(s), you can drop the view at any time without affecting those table(s). All SQL programs, applications, and other views that reference the dropped view will break, however.

To drop a view:

Listing 13.17Drop the view ny_authors.

DROP VIEW ny_authors;

Tips for DROP VIEW