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

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.

{mospagebreak title=Using SQL to Generate SQL}

Most RDBMS products come with a set of catalog views that allow query access to the metadata that describes the database objects contained in the database. Experienced database administrators know how to use the data in the catalog to their advantage and actually use SQL statements to generate other SQL statements.

Generating SQL in Oracle

In Oracle, the USER_TABLES view contains information about each table that belongs to the current database user. You can use the command “DESCRIBE USER_TABLES” to see the definition of the view, or refer to the Oracle Server Reference manual for descriptions of this and other catalog views.

Here is an SQL statement that creates a DROP TABLE command for every table found in USER_TABLES. The WHERE clause was added to eliminate some Oracle internal tables that might otherwise appear in the result set. There are also techniques available for sending the query results to a file that can then be used as a script, but they are beyond the scope of this text (see the Oracle SQL*Plus SPOOL command for details).

SELECT ‘DROP TABLE ‘ || TABLE_NAME ||
      
‘ CASCADE CONTRAINTS;’ AS SQL
 
FROM USER_TABLES
 
WHERE TABLE_NAME NOT LIKE ‘BIN$%';
SQL
——————————————-
DROP TABLE MOVIE_RENTAL CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_TRANSACTION CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_ACCOUNT_PERSON CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE MOVIE_LANGUAGE CASCADE CONSTRAINTS;
DROP TABLE MOVIE_COPY CASCADE CONSTRAINTS;
DROP TABLE MOVIE CASCADE CONSTRAINTS;
DROP TABLE PERSON CASCADE CONSTRAINTS;
DROP TABLE MPAA_RATING CASCADE CONSTRAINTS;
DROP TABLE MOVIE_GENRE CASCADE CONSTRAINTS;
DROP TABLE LANGUAGE CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_ACCOUNT CASCADE CONSTRAINTS;

Generating SQL in Microsoft SQL Server

Microsoft uses the term “system tables” for the SQL Server tables that contain metadata. You can find descriptions of them in Books Online under the topic “system tables.” Here is the SQL Server equivalent of the previous example (dropping all tables) using the SQL Server SYSOBJECTS table. The WHERE clause filters out all objects except those with the “user table” object type.

SELECT ‘DROP TABLE ‘ + NAME + ‘;’
 
FROM SYSOBJECTS
 
WHERE XTYPE=’U’
DROP TABLE PERSON;
DROP TABLE MOVIE;
DROP TABLE MOVIE_COPY;
DROP TABLE MOVIE_LANGUAGE;
DROP TABLE EMPLOYEE;
DROP TABLE CUSTOMER_ACCOUNT_PERSON;
DROP TABLE CUSTOMER_TRANSACTION;
DROP TABLE MOVIE_RENTAL;
DROP TABLE CUSTOMER_ACCOUNT;
DROP TABLE LANGUAGE;
DROP TABLE MOVIE_GENRE;
DROP TABLE MPAA_RATING;

NOTE: These generated DROP statements won’t run without some additional work because of the referential constraints. SQL Server doesn’t support the “CASCADE CONSTRAINTS” clause with the DROP command, so the referential constraints would have to be dropped before the tables could be, or the DROP statements would have to be placed in the proper order before being run.

{mospagebreak title=The CASE Expression}

The CASE expression is a recent addition to the SQL standard but an important one. For the first time, parts of SQL statements can be executed conditionally. For example, a column in the query results may be formatted based on the values contained in another column. However, your SQL implementation may not support it just yet because it is so new.

The CASE expression allows two general forms.

Simple CASE Expression

Here is the general syntax of the simple form of the CASE expression:

CASE input_expression
 
WHEN comparison_expression THEN result_expression
 
[WHEN comparison_expression THEN result_expression ...]
 
[ELSE result_expression]
END

NOTE:

  • Each WHEN condition is evaluated as input_expression = comparision_expression, and if the result is a logical TRUE, the result_expression is returned and no other WHEN conditions are evaluated.
  • If none of the WHEN conditions evaluates to TRUE, and there is an ELSE condition, the result_expression associated with the ELSE condition is returned.
  • If none of the WHEN conditions evaluates to TRUE, and there is no ELSE condition, a null value is returned.

As an example, you can use the CASE expression to translate the MPAA Rating Code to a simple message that can be displayed at the checkout counter in the video store to remind sales clerks to check customer ages for movies rated above PG-13. Note the placement of the AS keyword just after the END keyword to assign a column name to the generated column in the result set. Here is the example:

SELECT MOVIE_ID, MPAA_RATING_CODE AS RATING,
 
CASE MPAA_RATING_CODE
    
WHEN ‘G’ THEN ‘All ages’
    
WHEN ‘PG’ THEN ‘Parental guidance’
    
WHEN ‘PG-13′ THEN ‘Ages 13 and up’
    
ELSE ‘MUST be at least 17′
    
END AS RATING_DESC
  FROM MOVIE
ORDER BY MOVIE_ID;
 MOVIE_ID RATING RATING_DESC
——— —— ——————-
       
1 R      MUST be at least 17
       
2 R      MUST be at least 17
       
3 PG-13  Ages 13 and up
       
4 PG-13  Ages 13 and up
       
5 R      MUST be at least 17
       
6 PG-13  Ages 13 and up
       
7 PG-13  Ages 13 and up
       
8 R      MUST be at least 17
       
9 PG-13  Ages 13 and up
      
10 R      MUST be at least 17
      
11 PG-13  Ages 13 and up
      
12 PG-13  Ages 13 and up
      
13 PG-13  Ages 13 and up
      
14 R      MUST be at least 17
      
15 R      MUST be at least 17
      
16 PG-13  Ages 13 and up
      
17 PG-13  Ages 13 and up
      
18 R      MUST be at least 17
      
19 PG-13  Ages 13 and up
      
20 R      MUST be at least 17

Searched CASE Expression

The so-called searched CASE expression allows for more flexible comparison conditions because each one is written as a complete condition, including the comparison operator. Here is the general syntax:

CASE
 
WHEN condition THEN result_expression
 [WHEN condition THEN result_expression ...]
 
[ELSE result_expression]
END

NOTE:

  • Each condition can be any SQL expression that evaluates to TRUE or FALSE.
  • Each WHEN is evaluated in sequence, and if one of them evaluates to TRUE, the associated result_condition is returned and no other WHEN conditions are evaluated.
  • If none of the WHEN conditions evaluates to TRUE, and there is an ELSE condition, the result_expression associated with the ELSE condition is returned.
  • If none of the WHEN conditions evaluates to TRUE, and there is no ELSE condition, a null value is returned.

As an example, here is a query that classifies VHS movies by price range:

SELECT MOVIE_ID, RETAIL_PRICE_VHS,
  
CASE
    
WHEN RETAIL_PRICE_VHS IS NULL THEN ‘Not Available’
    
WHEN RETAIL_PRICE_VHS < 10 THEN ‘Bargain’
    
WHEN RETAIL_PRICE_VHS < 20 THEN ‘Budget’
    
WHEN RETAIL_PRICE_VHS < 40 THEN ‘Average’
    
ELSE ‘Premium’
  END AS PRICE_CATEGORY
 
FROM MOVIE
ORDER BY MOVIE_ID;
 MOVIE_ID RETAIL_PRICE_VHS PRICE_CATEGORY
——— —————- ————–
       
1            58.97 Premium
       
2            15.95 Budget
       
3            14.95 Budget
       
4            11.95 Budget
       
5            24.99 Average
       
6            24.99 Average
       
7            14.95 Budget
       
8            50.99 Premium
       
9            12.98 Budget
      
10            49.99 Premium
      
11             6.93 Bargain
      
12             9.95 Bargain
      
13             6.93 Bargain
      
14            24.99 Average
      
15             9.99 Bargain
      
16            11.69 Budget
      
17            14.94 Budget
      
18            24.99 Average
      
19            12.98 Budget
      
20            17.99 Budget

{mospagebreak title=Quiz}

Choose the correct responses to each of the multiple-choice questions. Note that there may be more than one correct response to each question.

  1. SQL functions

    a. Return a set of values

    b. Return a single value

    c. Can be used in the WHERE clause of an SQL statement

    d. Can be used as a table name alias in an SQL statement

    e. Can be used in the column list of an SQL statement
  2. The REPLACE function

    a. Replaces a table name with a view name

    b. Replaces a column name with a column alias

    c.  Replaces a character string in a column with another character string

    d. Replaces all values in a column with a new set of values

    e. Replaces all rows in a view with rows containing data from another table 
  3. The null value function

    a. Is called NVL in Oracle databases

    b. Is called ISNULL in IBM DB2 databases

    c. Is called ISNULL in Microsoft SQL Server databases

    d. Is called ISNULL in MySQL databases

    e. Is called IFNULL in the SQL standard
  4. The LTRIM function

    a. Removes trailing spaces from character strings
    b. Removes leading spaces from character strings

    c. Can be nested with other functions

    d. Replaces null values with other values in character strings

    e. Removes both leading and trailing spaces from character strings 
  5. The CHAR function

    a. Is named CHR in some SQL implementations

    b. Is identical to the ASCII function in some SQL implementations

    c. Returns the ASCII character set value for a character

    d. Returns the character for an ASCII character set value

    e. Converts a numeric value to a character string
  6. The SIGN function

    a. Returns −1 if the supplied parameter has a negative value

    b. Returns 0 if the supplied parameter has a value of zero

    c. Returns +1 if the supplied parameter has a value greater than or equal to zero

    d. Returns 0 if the supplied parameter is null

    e. Returns a null value if the supplied parameter is not a number
  7. The CEILING function

    a. Rounds a number down to the next whole number

    b. Rounds a number up to the next whole number

    c. Always returns an integer

    d. Returns either an integer or a null value

    e. Is named CEIL in some SQL implementations
  8. The FLOOR function

    a. Rounds a number down to the next whole number

    b. Rounds a number up to the next whole number

    c. Always returns an integer

    d. Returns either an integer or a null value

    e. Is named FLR in some SQL implementations
  9. Date and time functions

    a. Are very similar across different vendor implementations

    b. Vary markedly across different vendor implementations

    c. Include functions that format date and time data items for display

    d. Include functions that convert character strings to dates and times

    e. Include functions that convert date and time data items to character strings
  10. CASE expressions

    a. Allow for conditional execution of clauses within an SQL statement

    b. Come in two forms named static and dynamic

    c. Come in two forms named searched and nonsearched

    d. Come in two forms named simple and searched

    e. Come in two forms named standard and searched

    Write SQL statements to solve each of the following problems.
  11. List the MPAA_RATING_CODE values from the MPAA_RATING table, with hyphens (dashes) translated to spaces.
  12. Using the CHAR function (called CHR in Oracle), list the MOVIE_ID and MOVIE_TITLE for all movies that have a single quote (ASCII character 39) in their names.
  13. Find the ASCII character set value for an exclamation point (!).
  14. Find the average price of a DVD movie (column DVD_RETAIL_PRICE in the MOVIE table), with the calculated average rounded up to the nearest whole dollar.
  15. Find the average price of a VHS movie (column VHS_RETAIL_PRICE in the MOVIE table), with the calculated average rounded down to the nearest whole dollar.
  16. For an Oracle database, generate the SQL commands to drop all the referential constraints owned by the current user. The Oracle catalog view is called USER_CONSTRAINTS, and referential constraints have a CONSTRAINT_TYPE of  ‘R’. The table name on which the constraint is based is in the TABLE_NAME column of the catalog view. Remember that you have to use the ALTER TABLE command to drop table constraints.
  17. For a Microsoft SQL Server database, list all the foreign key constraints. Use the SYSOBJECTS system table, where NAME is the name of the constraint, and XTYPE has the value  ‘F’  for foreign key constraints.
  18. Write an SQL statement that lists each Customer Account (CUSTOMER_ACCOUNT_ID) with a character string based on the value of CHILD_RENTAL_ALLOWED_INDIC where the string shows  ‘Child Rental OK’  if the value is ‘Y’ and  ‘NO CHILD RENTAL’  if the value is  ‘N’. Hint: A simple CASE expression should work the best here.
  19. Write an SQL statement that lists each Movie (MOVIE_ID) along with the decade based on the value of YEAR_PRODUCED (80s, 90s, 00s, Unknown). A search CASE expression should be helpful here.
  20. Write an SQL statement that lists each movie rental (MOVIE_RENTAL table) with the LATE_OR_LOSS_FEE categorized as follows: None (data value null or 0), Minor (data value < 10), Major (data value >=10).
[gp-comments width="770" linklove="off" ]

chat