This article, the second of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).
From Chapter 1, you should recall that a view is a stored database query that provides a database user with a customized subset of the data from one or more tables in the database. Note that Microsoft Access uses the term query instead of view. The inherent beauty of views is that, once created, they can be queried just like tables. In fact, the user never needs to know they are using a view instead of a real table. There can be some restrictions when data manipulation (inserts, updates, and deletes) is attempted using views (check your DBMS documentation), but queries work just the same against views as they do against tables. Furthermore, views provide a number of very useful benefits:
Hiding columns a user does not need or should not see
Hiding rows a user does not need or should not see
Hiding complex operations such as joins
Improving query performance (in some RDBMSs, such as Microsoft SQL Server)
The examples in this section show views that provide the aforementioned benefits. From Chapter 4, the general syntax for creating a view is
CREATE [OR REPLACE] VIEW view_name AS sql_query;
Here are some practical examples using the video store sample database:
Obvious privacy rules require that the store manager keep employee tax IDs (social security numbers) and rates of pay in strict confidence. However, there are a number of web-based applications that require some employee information, such as the name of the employee and their supervisor’s name (if any). What is required in such cases is a view that can be safely used by those web applications without the possibility of a developer who’s using the data accidentally revealing confidential information. The query in the view must join with the PERSON table to find the name of the employee and again (using an outer join) for the name of the employee’s supervisor. Furthermore, column names should be as user friendly to a U.S.-based web development team as possible. Here is the SQL statement to create the view:
CREATE VIEW EMPLOYEE_LIST AS SELECT A.PERSON_ID AS ID, B.PERSON_GIVEN_NAME AS FIRST_NAME, B.PERSON_MIDDLE_NAME AS MIDDLE_NAME, B.PERSON_FAMILY_NAME AS LAST_NAME, C.PERSON_GIVEN_NAME AS MANAGER_FIRST_NAME, C.PERSON_FAMILY_NAME AS MANAGER_LAST_NAME FROM EMPLOYEE A JOIN PERSON B ON A.PERSON_ID = B.PERSON_ID LEFT OUTER JOIN PERSON C ON A.SUPERVISOR_PERSON_ID = C.PERSON_ID View created.
Once the view is created, you can write queries against it just like you can with tables. Here is a simple query against the view just created:
SELECT LAST_NAME, MANAGER_LAST_NAME FROM EMPLOYEE_LIST ORDER BY LAST_NAME; LAST_NAME MANAGER_LAST_NAME ------------------- ----------------- Alexander Bernstein Alexander Chung Alexander
For a movie catalog listing, you want to list the MPAA Rating Description and the Movie Genre Description. It seems obvious that this three-table join will be needed quite often, so you help the video store staff by creating a view with the joins already done for them. Here is the SQL statement to create the view:
CREATE VIEW MOVIE_LISTING AS SELECT A.MOVIE_ID, B.MOVIE_GENRE_DESCRIPTION AS GENRE, C.MPAA_RATING_CODE AS RATING, C.MPAA_RATING_DESCRIPTION AS RATING_DESC, A.MOVIE_TITLE, A.RETAIL_PRICE_VHS, A.RETAIL_PRICE_DVD, A.YEAR_PRODUCED FROM MOVIE A JOIN MOVIE_GENRE B ON A.MOVIE_GENRE_CODE = B.MOVIE_GENRE_CODE JOIN MPAA_RATING C ON A.MPAA_RATING_CODE = C.MPAA_RATING_CODE
Here is a simple query that uses the view instead of the base tables:
SELECT GENRE, RATING, MOVIE_TITLE FROM MOVIE_LISTING ORDER BY GENRE, RATING, MOVIE_TITLE; GENRE RATING MOVIE_TITLE ------------------ ------ ----------- Action and Adventure PG-13 Master and Commander: The Far Action and Adventure PG-13 Pirates of the Caribbean: The Action and Adventure PG-13 The Day After Tomorrow Action and Adventure PG-13 The Italian Job Action and Adventure R Kill Bill: Vol. 1 Action and Adventure R Man on Fire Action and Adventure R The Last Samurai Comedy PG-13 50 First Dates Comedy PG-13 Matchstick Men Comedy PG-13 Something’s Gotta Give Comedy PG-13 The School of Rock Drama PG-13 Big Fish Drama R Cold Mountain Drama R Lost in Translation Drama R Monster Drama R Mystic River Drama R Road to Perdition Foreign R Das Boot Romance PG-13 13 Going on 30 Romance PG-13 Two Weeks Notice 20 rows selected.
The video store is very interested in placing a computer-based kiosk in the children’s section to allow youngsters to search for movies online. However, the store manager wants to be sure only G, PG, and PG-13 rated movies show up on the children’s kiosk. It would be simple enough to enforce this rule in the application program that will control the kiosk. However, if a database view is used and the kiosk application is forced to use only the view, there can be no accidental slip-ups in the application program’s logic, and it will be easy to adjust the view later if the MPAA changes its rating system again (as they have done several times in the past). Rather than repeat the join logic in the MOVIE_LISTING view again in this view, you can simply use the MOVIE_LISTING view to create the new view. Yes, you can create a view based on another view! It provides unparalleled flexibility. Here is the example:
CREATE VIEW CHILDRENS_MOVIE_LISTING AS SELECT * FROM MOVIE_LISTING WHERE RATING IN ('G','PG','PG-13')
Using the same select you used against MOVIE_LISTING, here is the new result:
SELECT GENRE, RATING, MOVIE_TITLE FROM CHILDRENS_MOVIE_LISTING ORDER BY GENRE, RATING, MOVIE_TITLE; GENRE RATING MOVIE_TITLE ------------------ ------ ------------ Action and Adventure PG-13 Master and Commander: The Far Action and Adventure PG-13 Pirates of the Caribbean: The Action and Adventure PG-13 The Day After Tomorrow Action and Adventure PG-13 The Italian Job Comedy PG-13 50 First Dates Comedy PG-13 Matchstick Men Comedy PG-13 Something's Gotta Give Comedy PG-13 The School of Rock Drama PG-13 Big Fish Romance PG-13 13 Going on 30 Romance PG-13 Two Weeks Notice 11 rows selected.
Notice how the WHERE clause in the CHILDRENS_MOVIE_LISTING view blocks the unwanted rows from the query results. However, a word of caution on creating views based on other views: the CHILDRENS_MOVIE_LISTING view is dependent on the MOVIE_LISTING view and may become invalid or otherwise malfunction any time the MOVIE_LISTING view is changed. Without a bit of planning and control, a house of cards can result that will fall down every time changes are made. Nevertheless, views are a powerful tool that simply cannot be ignored.
/ 8 
