Creating a View (CREATE VIEW)

Creating a View (CREATE VIEW)
The format of the CREATE VIEW statement is:
CREATE VIEW ViewName [(newColumnName [, . . . ])]
AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION]
A view is defined by specifying an SQL SELECT statement. A name may optionally be
assigned to each column in the view. If a list of column names is specified, it must have
the same number of items as the number of columns produced by the subselect. If the list
of column names is omitted, each column in the view takes the name of the corresponding
column in the subselect statement. The list of column names must be specified if there
is any ambiguity in the name for a column. This may occur if the subselect includes
calculated columns, and the AS subclause has not been used to name such columns, or it
produces two columns with identical names as the result of a join.
The subselect is known as the defining query. If WITH CHECK OPTION is specified,
SQL ensures that if a row fails to satisfy the WHERE clause of the defining query of the
view, it is not added to the underlying base table of the view (see Section 6.4.6). It should
be noted that to create a view successfully, you must have SELECT privilege on all the
tables referenced in the subselect and USAGE privilege on any domains used in referenced
columns. These privileges are discussed further in Section 6.6. Although all views are created
created
in the same way, in practice different types of view are used for different purposes.
We illustrate the different types of view with examples.
Example 6.3 Create a horizontal view
Create a view so that the manager at branch B003 can see only the details for staff who
work in his or her branch office.
A horizontal view restricts a user’s access to selected rows of one or more tables.
CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branchNo = ‘B003’;
This creates a view called Manager3Staff with the same column names as the Staff table but
containing only those rows where the branch number is B003. (Strictly speaking, the
branchNo column is unnecessary and could have been omitted from the definition of the
view, as all entries have branchNo = ‘B003’.) If we now execute the statement:
SELECT * FROM Manager3Staff;
we would get the result table shown in Table 6.3. To ensure that the branch manager can
see only these rows, the manager should not be given access to the base table Staff. Instead,
the manager should be given access permission to the view Manager3Staff. This, in effect,
gives the branch manager a customized view of the Staff table, showing only the staff at
his or her own branch. We discuss access permissions in Section 6.6.