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:
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:
Type:
CREATE VIEW view [(view_columns)]
AS select_query;
view is the name of the view to create. The view name must be unique within the database.
view_columns is an optional, parenthesized list of one or more comma-separated names to be used for the columns in view. The number of columns in view_columns must match the number of columns in the SELECT clause of select_query. (If you name one column this way, then you must name them all this way.) Specify view_columns when a column in select_query is derived from an arithmetic expression, a function, or a literal; when two or more view columns would otherwise have the same name (usually because of a join); or to give a column in view a name different from that of the column from which it was derived. If view_columns is omitted, then view inherits column names from select_query. Column names also can be assigned in select_query via AS clauses. Each column name must be unique within the view.
select_query is a SELECT query that identifies the columns and rows of the table(s) that the view is based on. select_query can be arbitrarily complex and use more than one table or other views. An ORDER BY clause usually is prohibited. For information about the SELECT statement, see Chapters 4 through 9. For DBMS-specific restrictions on SELECT in views, search your DBMS’s documentation for CREATE VIEW (Listings 13.1 through 13.5).
Listing 13.1Create a view that hides the authors’ personal information (telephone numbers and addresses).
CREATE VIEW au_names
AS
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.
CREATE VIEW cities
(au_id, au_city, pub_id, pub_city)
AS
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)
AS
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
AS
SELECT
TRIM(au_fname || ' ' || au_lname)
AS "address1",
TRIM(address)
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)
AS
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');
When you run a CREATE VIEW statement in Microsoft Access, the view appears as a query object in the Database window. To run Listing 13.4, change every occurrence of || to +; see the DBMS tip in “Tips for Concatenating Strings” in Chapter 5. To run Listing 13.5 in Access, nest the joins:
CREATE VIEW au_titles
(LastName, Title)
AS
SELECT an.au_lname, t.title_name
FROM au_names an
INNER JOIN (titles t
INNER JOIN title_authors ta
ON t.title_id = ta.title_id)
ON an.au_id = ta.au_id
WHERE an.au_id IN ('A02','A05');
To run Listings 13.1 through 13.5 in Microsoft SQL Server, remove the terminating semicolon from each statement. Additionally, to run Listing 13.4, change every occurrence of || to + and every occurrence of TRIM(x) to LTRIM(RTRIM(x)); see the DBMS tips in “Tips for Concatenating Strings” and “Tips for Trimming Characters” in Chapter 5.
To run Listings 13.2 and 13.5 in Oracle 8i and earlier, use WHERE syntax instead of JOIN syntax. Type (Listing 13.2):
CREATE VIEW cities
(au_id, au_city, pub_id, pub_city)
AS
SELECT a.au_id, a.city, p.pub_id, p.city
FROM authors a, publishers p
WHERE a.city = p.city;
and (Listing 13.5):
CREATE VIEW au_titles
(LastName, Title)
AS
SELECT an.au_lname, t.title_name
FROM title_authors ta, au_names an, titles t
WHERE ta.au_id = an.au_id
AND t.title_id = ta.title_id
AND an.au_id in ('A02','A05');
To run Listing 13.4 in Db2, change every occurrence of TRIM(x) to LTRIM(RTRIM(x)); see the DBMS tip in “Tips for Trimming Characters” in Chapter 5.
To run Listing 13.4 in MySQL, use the function CONCAT() instead of the concatenation operator ||; see the DBMS tip in “Tips for Concatenating Strings” in Chapter 5. MySQL 5.0 and later support views. Earlier versions won’t run the listings in this section. (To hide data in earlier versions, use MySQL’s privilege system to restrict column access.)
In Microsoft SQL Server, Oracle, Db2, MySQL, and PostgreSQL, you can add the optional clause WITH [CASCADED | LOCAL] CHECK OPTION when you create a view. This clause applies to only updateable views and ensures that only data that can be read by the view can be inserted, updated, or deleted; see “Updating Data Through a View” later in this chapter. If a view shows authors from only New York state, for example, then it would be impossible to insert, update, or delete non–New York authors through that view. The CASCADED and LOCAL options apply to nested views only. CASCADED performs the check for the current view and all the views it references. LOCAL performs the check for the current view only.
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:
Type:
SELECT columns
FROM view
[JOIN joins]
[WHERE search_condition]
[GROUP BY group_columns]
[HAVING search_condition]
[ORDER BY sort_columns];
view is the name of the view to query. The clauses work with views the same way that they work with tables, as described in Chapters 4 through 9.
Listings 13.6 through 13.11 and Figures 13.1 through 13.6 show how to retrieve data through the views created by Listings 13.1 through 13.5 in “Creating a View with CREATE VIEW” earlier in this chapter.
Listing 13.6List all the rows and columns of the view au_titles. See Figure 13.1 for the result.
SELECT *
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.
SELECT DISTINCT au_city
FROM cities;
Figure 13.2Result of Listing 13.7.
au_city
-------------
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.
address3
-------------------
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'.
To run Listing 13.9 in Microsoft Access, enclose the view’s column names in double quotes and brackets:
SELECT ["address3"]
FROM mailing_labels
WHERE ["address1"] LIKE '%Kell%';
To run Listing 13.9 in Oracle and Db2, enclose the view’s column names in double quotes:
SELECT "address3"
FROM mailing_labels
WHERE "address1" LIKE '%Kell%';
MySQL 5.0 and later support views. Earlier versions won’t run the listings in this section.
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.
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
AS
SELECT au_id, au_fname, au_lname, state
FROM authors
WHERE state = 'NY';
SELECT *
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
VALUES('A08','Don','Dawson','NY');
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'
WITH CHECK OPTION;
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
VALUES('A09','Jill','LeFlore','CA');
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.
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.
To run Listing 13.12 in Microsoft SQL Server, omit the terminating semicolon from the CREATE VIEW statement and run the two statements separately.
MySQL 5.0 and later support views. Earlier versions won’t run the listings in this section.
In PostgreSQL, simple views are automatically updatable. For complex views, you can create the effect of an updateable view by creating TRIGGERs or RULEs for INSERTs, UPDATEs, and DELETEs. Search PostgreSQL documentation for CREATE VIEW.
For all DBMSs, check the documentation to see how your DBMS handles updateable views for columns whose data type generates a unique row identifier automatically; see “Unique Identifiers” in Chapter 3.
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:
Type:
DROP VIEW view;
view is the name of the view to drop (Listing 13.17).
Listing 13.17Drop the view ny_authors.
DROP VIEW ny_authors;
