Advanced Query Writing, concluded

Taking Advantage of Views

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.

Next: Using SQL to Generate SQL >>
 

More MySQL Articles
More By McGraw-Hill/Osborne

Comments On: Advanced Query Writing, concluded

· This article is an excerpt from the book "SQL DeMYSTified," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249). Check it out today at your favorite bookstore. Buy this book now.