Home arrow MySQL arrow Advanced Query Writing, concluded

Advanced Query Writing, concluded

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).

TABLE OF CONTENTS:
  1. Advanced Query Writing, concluded
  2. Using SQL to Generate SQL
  3. The CASE Expression
  4. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 8
March 09, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: