Chapter 21. Views

Introduction

SQL supports two types of tables: real tables, generally known as base tables, and derived tables, also called views. Base tables are created with CREATE TABLE statements and are the only ones in which data can be stored. Examples are the PLAYERS and TEAMS tables from the tennis club database.

A derived table, or view, stores no rows itself. Instead, it exists, and can be seen, as a prescription or formula for combining certain data from base tables to make a “virtual” table. The word virtual is used because the contents of a view exist only when it is used in a statement. At that moment, SQL executes the prescription that makes up the view formula and presents the user with what seems to be a real table.

This chapter describes how views are created and how they can be used. Some useful applications include the simplification of routine statements and the reorganization of tables. Two sections look at restrictions on querying and updating views.

Creating Views

Views are created with the CREATE VIEW statement.

<create view statement> ::=
   CREATE [ OR REPLACE ] VIEW <view name>
      [ <column list> ] AS
      <table expression>
      [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Example 21.1. Create a view that holds all town names from the PLAYERS table, and show the virtual contents of this new view.

CREATE   VIEW TOWNS AS
SELECT   DISTINCT TOWN
FROM     PLAYERS

SELECT   *
FROM     TOWNS

The result is:

TOWN
---------
Stratford
Inglewood
Eltham
Midhurst
Douglas
Plymouth

Example 21.2. Create a view that holds the player numbers and league numbers of all players who have a league number, and show the virtual contents of this view.

CREATE   VIEW CPLAYERS AS
SELECT   PLAYERNO, LEAGUENO
FROM     PLAYERS
WHERE    LEAGUENO IS NOT NULL

SELECT   *
FROM     CPLAYERS

The result is:

PLAYERNO  LEAGUENO
--------  --------
      44  1124
     112  1319
      83  1608
       2  2411
      27  2513
       8  2983
      57  6409
     100  6524
     104  7060
       6  8467

These two CREATE VIEW statements create two views: TOWNS and CPLAYERS. The contents of each view are defined by a table expression. Such a table expression forms the view formula of the view. These two views can be queried just like base tables, and the CPLAYERS view can even be updated.

Example 21.3. Get the player and league numbers for competition players whose numbers run from 6 to 44 inclusive.

SELECT   *
FROM     CPLAYERS
WHERE    PLAYERNO BETWEEN 6 AND 44

The result is:

PLAYERNO  LEAGUENO
--------  --------
       6  8467
      44  1124
      27  2513
       8  2983

If we did not use the CPLAYERS view for the same question, but accessed the PLAYERS table directly, we would need a more complex SELECT statement to retrieve the same information:

SELECT   PLAYERNO, LEAGUENO
FROM     PLAYERS
WHERE    LEAGUENO IS NOT NULL
AND      PLAYERNO BETWEEN 6 AND 44

Example 21.4. Remove the competition player whose league number is 7060.

DELETE
FROM    CPLAYERS
WHERE   LEAGUENO = '7060'

When this statement is executed, the row in the base table, the PLAYERS table, in which the LEAGUENO column equals 7060, is deleted.

The contents of a view are not stored but are derived when the view is referenced. This means that the contents, by definition, are always in line with the contents of the base tables. Every update made to the data in a base table is immediately visible in a view. Users never need to be concerned about the integrity of the contents of the view, as long as the integrity of the base tables is maintained. In Section 21.8, we return to the subject of updating views.

Another view may be specified in a view formula. In other words, we may nest views.

Example 21.5. Create a view that holds all competition players whose player numbers run from 6 to 27 inclusive, and show the virtual contents of this view.

CREATE   VIEW SEVERAL AS
SELECT   *
FROM     CPLAYERS
WHERE    PLAYERNO BETWEEN 6 AND 27

SELECT   *
FROM     SEVERAL

The result is:

PLAYERNO  LEAGUENO
--------  --------
       6  8467
       8  2983
      27  2513

Note that not every form of the table expression may be used as a view formula. These rules are vendor-dependent, however.

In most cases, table expressions retrieve data from base tables or views, but not necessarily. Table expressions can give a result without accessing so much as one table; for example, see Example 7.34, in Chapter 7, “SELECT Statement: The FROM Clause.” Therefore, views do not have to be defined on base tables. Here is an example:

Example 21.6. Create a view in which the number 0 up to and including 9 appear, and show the contents of this view next.

CREATE VIEW DIGITS AS
SELECT '0' DIGIT UNION SELECT '1' UNION
SELECT '2' UNION SELECT '3' UNION
SELECT '4' UNION SELECT '5' UNION
SELECT '6' UNION SELECT '7' UNION
SELECT '8' UNION SELECT '9'

SELECT * FROM DIGITS

The result is:

DIGIT
-----
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9

Behind the word CREATE, we can specify OR REPLACE. If the name of the view already exists, the old view formula is overwritten by the new one.

The Column Names of Views

By default, the column names in a view are the same as the column names in the SELECT clause. For example, the two columns in the SEVERAL view are called PLAYERNO and LEAGUENO. A view, therefore, inherits the column names. You can also explicitly define the column names of views.

Example 21.7. Create a view that holds the player number, name, initials, and date of birth of each player who lives in Stratford.

CREATE   VIEW STRATFORDERS (PLAYERNO, NAME, INIT, BORN) AS
SELECT   PLAYERNO, NAME, INITIALS, BIRTH_DATE
FROM     PLAYERS
WHERE    TOWN = 'Stratford'

SELECT   *
FROM     STRATFORDERS
WHERE    PLAYERNO > 90

The result is (note the column names):

PLAYERNO  NAME       INITIALS  BORN
--------  ---------  --------  ----------
     100  Parmenter  P         1963-02-08

These new column names are permanent. You can no longer refer to the columns PLAYERNO or BIRTH_DATE in the STRATFORDERS view.

If an expression in the SELECT clause of a view formula does not consist of a column specification, but is a function or calculation, it is mandatory to provide names for the columns of the view.

Example 21.8. For each town, create a view that holds the place–name and the number of players who live in that town.

CREATE   VIEW RESIDENTS (TOWN, NUMBER) AS
SELECT   TOWN, COUNT(*)
FROM     PLAYERS
GROUP BY TOWN

Explanation. In this view, you may not leave out the column names TOWN and NUMBER_OF.

Exercise 21.1:

Create a view called NUMBERPLS that contains all the team numbers and the total number of players who have played for that team. (Assume that at least one player has competed for each team.)

Exercise 21.2:

Create a view called WINNERS that contains the number and name of each player who, for at least one team, has won one match.

Exercise 21.3:

Create a view called TOTALS that records the total amount of penalties for each player who has incurred at least one penalty.

Updating Views: WITH CHECK OPTION

We have already shown a number of examples of views being updated. In fact, the underlying tables are being updated. Nevertheless, updating views can have unexpected results. Let us illustrate this with the following example:

Example 21.9. Create a view that holds all players born before 1960.

CREATE   VIEW VETERANS AS
SELECT   *
FROM     PLAYERS
WHERE    BIRTH_DATE < '1960-01-01'

Now we would like to change the date of birth of the veteran whose player number is 2 from 1 September 1948 to 1 September 1970. The update statement reads:

UPDATE   VETERANS
SET      BIRTH_DATE = '1970-09-01'
WHERE    PLAYERNO = 2

This is a correct update. The date of birth of player number 2 in the PLAYERS table is changed. The unexpected effect of this update, though, is that if we look at the view using a SELECT statement, player 2 no longer appears. This is because when the update occurred, the player ceased to satisfy the condition specified in the view formula.

If you extend the view definition using the so-called WITH CHECK OPTION, SQL ensures that such an unexpected effect does not arise.

The view definition then becomes:

CREATE   VIEW VETERANS AS
SELECT   *
FROM     PLAYERS
WHERE    BIRTH_DATE < '1960-01-01'
WITH     CHECK OPTION

If a view includes the WITH CHECK OPTION clause, all changes with UPDATE, INSERT, and DELETE statements are checked for validity:

  • An UPDATE statement is correct if the rows that are updated still belong to the (virtual) contents of the view after the update.

  • An INSERT statement is correct if the new rows belong to the (virtual) contents of the view.

  • A DELETE statement is correct if the rows that are deleted belong to the (virtual) contents of the view.

As said, a view can be stacked on top of another view. The question that comes to mind then is to what extent the check of the WITH CHECK OPTION can be carried out. If we specify WITH CASCADED CHECK OPTION, all views are checked. When WITH LOCAL CHECK OPTION is used, only those checks are carried out that relate to conditions that appear in the view that will be updated. CASCADED is the default.

Example 21.10. Create a view of all players born before 1960 and living in Inglewood.

CREATE   VIEW INGLEWOOD_VETERANS AS
SELECT   *
FROM     VETERANS
WHERE    TOWN = 'Inglewood'
WITH     CASCADED CHECK OPTION

Explanation: If we use an INSERT statement to add a player to this view, he or she must live in Inglewood and must be born before January 1, 1960. When we leave out CASCADED, every player who we add to the INGLEWOOD_VETERANS table must live in Inglewood. SQL no longer carries out the check.

The WITH CHECK OPTION can be used only in conjunction with views that can be updated according to the rules mentioned in Section 21.8.

Deleting Views

The DROP VIEW statement deletes a view. Every other view that references this dropped view is also dropped automatically. Of course, this can lead to the removal of other views. When a base table is dropped, all views that have been defined directly or indirectly on that table are also dropped.

<drop view statement> ::=
   DROP VIEW <table specification>

Example 21.11. Drop the CPLAYERS view.

DROP VIEW CPLAYERS

Views and the Catalog

Information about views is recorded in various tables. In the VIEWS table, a row is stored for each view. The primary key of this catalog table is formed by the column VIEW_ID. The columns VIEW_NAME and CREATOR form an alternate key.

Table 21.1. Columns of the VIEWS Catalog Table

Column Name

Data Type

Description

VIEW_CREATOR

CHAR

Name of the owner (or creator) of the view (in MySQL, this is the name of the database to which the view belongs)

VIEW_NAME

CHAR

Name of the view

CREATE_TIMESTAMP

TIMESTAMP

Date on which the view was created

WITHCHECKOPT

CHAR

Has the value YES (if the view is defined with the WITH CHECK OPTION), CASCADED, LOCAL; other-wise, it has the value NO

IS_UPDATABLE

CHAR

Has the value YES if the view can be updated; otherwise, it has the value NO

COMMENT

CHAR

Comment that is entered with the COMMENT statement

VIEWFORMULA

CHAR

The view formula (table expression)

The columns of the view inherit the data type of the column expressions from the SELECT clause of the view formula.

Example 21.12. Can a table called STOCK be created in the TENNIS DATABASE, or does that name already exist?

SELECT   TABLE_NAME
FROM     TABLES
WHERE    TABLE_NAME = 'STOCK'
AND      TABLE_CREATOR = 'TENNIS'
UNION
SELECT   VIEW_NAME
FROM     VIEWS
WHERE    VIEW_NAME = 'STOCK'
AND      VIEW_CREATOR = 'TENNIS'

Explanation: The SELECT statement checks whether a table or view was created with the name STOCK in the TENNIS database. If the statement has a result, this table name cannot be used again.

Restrictions on Querying Views

The SELECT, INSERT, UPDATE, and DELETE statements may be executed on views. However, a number of restrictions exist. For example, some views may not be queried in certain ways, and the rows of some views may not be deleted.

Portability

The restrictions that apply to querying views can be different per SQL product. The following restrictions do not apply to MySQL.

Restriction 1. When a column in a view is based on an aggregation function in the SELECT clause of the view formula, this column may be used only in the SELECT or ORDER BY clauses of the SELECT statement that queries the view—not, for example, in the WHERE clause.

Example:

CREATE   VIEW TOTALS
        (PLAYERNO, TOT_AMOUNT) AS
SELECT   PLAYERNO, SUM(AMOUNT)
FROM     PENALTIES
GROUP BY PLAYERNO

The following SELECT statement is, therefore, not allowed because the TOT_AMOUNT column is based on a function in the view formula. It cannot be used in the WHERE clause.

SELECT   *
FROM     TOTALS
WHERE    TOT_AMOUNT > 100

Restriction 2. If a column of a view is based on an aggregation function in a view formula, this column may not be used in a function in the SELECT clause of the statement that uses the view.

Consider the TOTALS view again. The following statement is not permitted because the MAX function is specified for the TOT_AMOUNT column from the TOTALS view. TOT_AMOUNT itself is based on a function (SUM(AMOUNT)).

SELECT   MAX(TOT_AMOUNT)
FROM     TOTALS

Restriction 3. If a view formula contains a GROUP BY clause, the view may not be joined with another view or table.

As an illustration, we use the TOTALS view again. This view contains a GROUP BY clause, and that makes the following join invalid:

SELECT   NAME, TOT_AMOUNT
FROM     PLAYERS, TOTALS
WHERE    PLAYERS.PLAYERNO = TOTALS.PLAYERNO

Restrictions on Updating Views

As mentioned, there are also restrictions on updating views. (MySQL has some restrictions as well.) A view can be updated only if the view formula satisfies the following conditions. The first eight conditions apply to all update statements.

  • The view definition must be based, directly or indirectly, on one or more base tables.

  • The SELECT clause may not contain DISTINCT.

  • The SELECT clause may not contain aggregation functions.

  • The FROM clause may not contain more than one table.

  • The SELECT statement may not contain a GROUP BY clause (and, therefore, also no HAVING clause).

  • The SELECT statement may not contain an ORDER BY clause.

  • The SELECT statement may not contain set operators.

  • For the UPDATE statement, a virtual column may not be updated.

  • The BEGIN_AGE column in the following view may not be updated (though the PLAYERNO column may be updated):

    CREATE   VIEW AGE (PLAYERNO, BEGIN_AGE) AS
SELECT   PLAYERNO, JOINED – YEAR(BIRTH_DATE)
FROM     PLAYERS

  • For the INSERT statement, the SELECT clause must contain, from the table that is specified in the FROM clause, all columns in which the NOT NULL value is allowed or for which a default value is specified.

That is why INSERT statements may not be performed against the following view. The view does not contain all NOT NULL columns, such as SEX and TOWN:

CREATE   VIEW PLAYERS_NAMES AS
SELECT   PLAYERNO, NAME, INITIALS
FROM     PLAYERS

Exercise 21.4:

This chapter has shown many examples of views. For each of the following views, say whether an UPDATE, INSERT, or DELETE statement may be performed:

TOWNS
CPLAYERS
SEVERAL
STRATFORDERS
RESIDENTS
VETERANS
TOTALS
AGE

Processing View Statements

How will statements that access views be processed? The processing steps (see Chapter 5, “SELECT Statement: Common Elements”) cannot be executed one by one, as happens for base tables. SQL reaches the FROM clause and attempts to fetch rows from the database; it has a problem because a view contains no stored rows. So which rows must be retrieved from the database when a statement refers to a view? SQL knows that it is dealing with a view (thanks to a routine look in the catalog). To process the steps, SQL can choose between two methods, called substitution and materialization.

With the first method, the view formula is merged into the SELECT statement. This method is called substitution because the view name in the SELECT statement is replaced (substituted) by the view formula. Next, the obtained SELECT statement is processed. We show how this works with an example.

Example 21.13. Create a view of all data of the players who incurred a penalty. Next, give the number of each player from the COST_RAISERS view who has incurred at least one penalty and lives in Stratford.

CREATE   VIEW COST_RAISERS AS
SELECT   *
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)

SELECT   PLAYERNO
FROM     COST_RAISERS
WHERE    TOWN = 'Stratford'

The first processing step comprises the merging of the view formula into the SELECT statement. This step produces the following statement:

SELECT   PLAYERNO
FROM    (SELECT   *
         FROM     PLAYERS
         WHERE    PLAYERNO IN
                 (SELECT   PLAYERNO
                  FROM     PENALTIES)) AS VIEWFORMULA
WHERE    TOWN = 'Stratford'

Now, this statement can be processed by moving through the steps. In short, an additional step emerges that SQL performs before the other steps.

The final result is:

PLAYERNO
--------
       6

Here is another example, using the STRATFORDERS view from Section 21.3.

Example 21.14. Delete all Stratford people born after 1965.

DELETE
FROM     STRATFORDERS
WHERE    BORN > '1965-12-31'

After the name has been substituted by the view formula, the statement reads:

DELETE
FROM     PLAYERS
WHERE    BIRTH_DATE > '1965-12-31'
AND      TOWN = 'Stratford'

Another method of processing is called materialization. Here, the table expression of the view formula is processed first, which gives an intermediate result. Next, the actual SELECT statement is executed on that intermediate result. If we would process Example 21.13 through materialization, the following statement would be executed first:

SELECT   *
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)

This gives the following intermediate result (for the sake of convenience, only the columns PLAYERNO and TOWN have been displayed):

PLAYERNO  TOWN
--------  ---------
       6  Stratford
       8  Inglewood
      27  Eltham
      44  Inglewood
     104  Eltham

SQL keeps this intermediate result in internal memory. After that, the following statement is executed:

SELECT   PLAYERNO
FROM     <intermediate result>
WHERE    TOWN = 'Stratford'

Both methods have their advantages and disadvantages. SQL determines which method can be used best in which situation.

Exercise 21.5:

What will the following statements look like after the view formula has been included through the method substitution?

  1. SELECT   YEAR(BORN) - 1900, COUNT(*)
FROM     STRATFORDERS
GROUP BY 1


  2. SELECT   PLAYERNO
FROM     COST_RAISERS, STRATFORDERS
WHERE    COST_RAISERS.PLAYERNO = STRATFORDERS.PLAYERNO

  3. UPDATE   STRATFORDERS
SET      BORN = 1950
WHERE    PLAYERNO = 7

Application Areas for Views

You can use views in a great variety of applications. In this section, we look at some of them. There is no special significance to the order in which they are discussed.

Simplification of Routine Statements

Statements that are used frequently, or are structurally similar, can be simplified through the use of views.

Example 21.15. Imagine that these two statements are frequently entered.

SELECT   *
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)
AND      TOWN = 'Stratford'

and

SELECT   TOWN, COUNT(*)
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)
GROUP BY TOWN

Both statements are concerned with the players who have incurred at least one penalty, so this subset of players can be defined by a view:

CREATE   VIEW PPLAYERS AS
SELECT   *
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)

Now, the two previous SELECT statements can be greatly simplified by using the PPLAYERS view:

SELECT   *
FROM     PPLAYERS
WHERE    TOWN = 'Stratford'

and

SELECT   TOWN, COUNT(*)
FROM     PPLAYERS
GROUP BY TOWN

Example 21.16. Imagine that the PLAYERS table is often joined with the MATCHES table.

SELECT   ...
FROM     PLAYERS, MATCHES
WHERE    PLAYERS.PLAYERNO = MATCHES.PLAYERNO
AND      ...

In this case, the SELECT statement becomes simpler if the join is defined as a view:

CREATE   VIEW PLAY_MAT AS
SELECT   ...
FROM     PLAYERS, MATCHES
WHERE    PLAYERS.PLAYERNO = MATCHES.PLAYERNO

The join now takes this simplified form:

SELECT   ...
FROM     PLAY_MAT
WHERE    ...

Reorganizing Tables

The structure of tables is designed and implemented on the basis of a particular situation. This situation can change from time to time, which means that the structure also changes. For example, a new column is added to a table, or two tables are joined to make a single table. In most cases, the reorganization of a table structure requires altering already developed and operational statements. Such changes can be time-consuming and expensive. Appropriate use of views can keep this time and cost to a minimum. Let us see how.

Example 21.17. Get the name and initials of each competition player, and give also the divisions in which he or she has ever played.

SELECT   DISTINCT NAME, INITIALS, DIVISION
FROM     PLAYERS AS P, MATCHES AS M, TEAMS AS T
WHERE    P.PLAYERNO = M.PLAYERNO
AND      M.TEAMNO = T.TEAMNO

The result is:

NAME       INITIALS  DIVISION
---------  --------  --------
Parmenter  R         first
Baker      E         first
Hope       PK        first
Everett    R         first
Collins    DD        second
Moorman    D         second
Brown      M         first
Bailey     IP        second
Newcastle  B         first
Newcastle  B         second

For some presently unknown reasons, the TEAMS and MATCHES tables have to be reorganized; they are combined to form one table, the RESULT table, shown here:

MATCH_NO  TEAMNO  PLAYERNO  WON  LOST  CAPTAIN  DIVISION
--------  ------  --------  ---  ----  -------  --------
       1       1         6    3     1        6  first
       2       1         6    2     3        6  first
       3       1         6    3     0        6  first
       4       1        44    3     2        6  first
       5       1        83    0     3        6  first
       6       1         2    1     3        6  first
       7       1        57    3     0        6  first
       8       1         8    0     3        6  first
       9       2        27    3     2       27  second
      10       2       104    3     2       27  second
      11       2       112    2     3       27  second
      12       2       112    1     3       27  second
      13       2         8    0     3       27  second

The CAPTAIN column in the RESULT table is the former PLAYERNO column from the TEAMS table. This column has been given another name; otherwise, there would have been two columns called PLAYERNO. All statements that refer to the two tables now have to be rewritten, including the previous SELECT statement. A solution, which renders a total rewrite unnecessary, is to define two views that represent the former TEAMS and MATCHES tables, respectively:

CREATE   VIEW TEAMS (TEAMNO, PLAYERNO, DIVISION) AS
SELECT   DISTINCT TEAMNO, CAPTAIN, DIVISION
FROM     RESULT

CREATE   VIEW MATCHES AS
SELECT   MATCHNO, TEAMNO, PLAYERNO,
         WON, LOST
FROM     RESULT

The virtual contents of each of these two views are the same as the contents of the two original tables. Not one statement has to be rewritten, including the SELECT statement from the beginning of this section.

Of course, you cannot manage every reorganization of a table with views. It might be decided, for example, to store data about male and female players in separate tables. Both tables acquire the same columns as the PLAYERS table but omit the SEX column. It is no longer possible to reconstruct the original PLAYERS table with a view because the UNION operator would be required, and inserts on this view are not allowed.

Stepwise Development of SELECT Statements

Imagine that you have to complete the following task: Get the name and initials of each player from Stratford who has incurred a penalty that is greater than the average penalty for players from the second team and who played for at least one first-division team. You could write a huge SELECT statement to answer this, but you could also develop a query in a stepwise fashion.

First, we create a view of all the players who have incurred at least one penalty that is greater than the average penalty for players from the second team:

CREATE   VIEW GREATER AS
SELECT   DISTINCT PLAYERNO
FROM     PENALTIES
WHERE    AMOUNT >
        (SELECT   AVG(AMOUNT)
         FROM     PENALTIES
         WHERE    PLAYERNO IN
                 (SELECT   PLAYERNO
                  FROM     MATCHES
                  WHERE TEAMNO = 2))

Then we create a view of all players who have competed for a team in the first division:

CREATE   VIEW FIRST AS
SELECT   DISTINCT PLAYERNO
FROM     MATCHES
WHERE    TEAMNO IN
        (SELECT   TEAMNO
         FROM     TEAMS
         WHERE    DIVISION = 'first')

Using these two views, answering the original question is quite simple:

SELECT   NAME, INITIALS
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
AND      PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     GREATER)
AND      PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     FIRST)

We can split the problem into “mini problems” and execute this in steps. In this way, you can create one long SELECT statement.

Specifying Integrity Constraints

By using the WITH CHECK OPTION clause, you can implement rules that restrict the possible set of values that may be entered into columns.

Example 21.18. The SEX column in the PLAYERS table may contain either the value M or the value F. You can use the WITH CHECK OPTION clause to provide an automatic control for this. The following view should be defined:

CREATE   VIEW PLAYERSS AS
SELECT   *
FROM     PLAYERS
WHERE    SEX IN ('M', 'F')
WITH     CHECK OPTION

To follow this up, we give nobody the privilege of accessing the PLAYERS table directly; instead they have to do so via the PLAYERSS view. The WITH CHECK OPTION clause tests every update (that is, every UPDATE and INSERT statement) to see whether the value in the SEX column falls into the permitted range.

Note

If the desired check can be defined with a check integrity constraint, we recommend that you use it in this application.

Data Security

Views can also be used to protect parts of tables. Chapter 23, “Users and Data Security,” deals with this topic in detail.

Exercise 21.6:

Decide whether the following reorganizations of the database structure are possible through the use of views.

  • The NAME column is added to the PENALTIES table but also remains in the PLAYERS table.

  • The TOWN column is removed from the PLAYERS table and placed together with the PLAYERNO column in a separate table.

Answers

21.1

CREATE   VIEW NUMBERPLS (TEAMNO, NUMBER) AS
SELECT   TEAMNO, COUNT(*)
FROM     MATCHES
GROUP BY TEAMNO

21.2

CREATE   VIEW WINNERS AS
SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     MATCHES
         WHERE    WON > LOST)

21.3

CREATE   VIEW TOTALS (PLAYERNO, SUM_PENALTIES) AS
SELECT   PLAYERNO, SUM(AMOUNT)
FROM     PENALTIES
GROUP BY PLAYERNO

21.4

View

Update

Insert

Delete

TOWNS

No

No

No

CPLAYERS

Yes

No

Yes

SEVERAL

Yes

No

Yes

STRATFORDERS

Yes

No

Yes

RESIDENTS

No

No

No

VETERANS

Yes

Yes

Yes

TOTALS

No

No

No

AGE

Yes

No

Yes

21.5

  1. SELECT   YEAR(BORN) - 1900, COUNT(*)
FROM    (SELECT   PLAYERNO, NAME,
                  INITIALS, BIRTH_DATE AS BORN
         FROM     PLAYERS
         WHERE    TOWN = 'Stratford') AS STRATFORDERS
GROUP BY BORN

  2. SELECT   EXPENSIVE.PLAYERNO
FROM    (SELECT   *
         FROM     PLAYERS
         WHERE    PLAYERNO IN
                 (SELECT   PLAYERNO
                  FROM     PENALTIES)) AS EXPENSIVE,
        (SELECT   PLAYERNO, NAME,
                  INITIALS, BIRTH_DATE AS BORN
         FROM     PLAYERS
         WHERE    TOWN = 'Stratford') AS STRATFORDERS
WHERE    EXPENSIVE.PLAYERNO = STRATFORDERS.PLAYERNO

  3. UPDATE   PLAYERS
SET      BIRTH_DATE = '1950-04-04'
WHERE    PLAYERNO = 7

21.6

Yes, but the view can be only queried, not updated, because the view formula contains a join.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.19.75.133