CREATE VIEW statement

Chapter 43. Watcom-SQL Statements
CREATE VIEW statement

Function

To create a view on the database. Views are used to give a different perspective on the data even though it is not stored that way.

Syntax

     CREATE VIEW
          ...     [ owner.]view-name [( column-name, ... )]
          ...     AS select-without-order-by
          ...     [ WITH CHECK OPTION ]

Usage

Anywhere.

Permissions

Must have RESOURCE authority and SELECT permission on the tables in the view definition.

Side effects

Automatic commit.

Description

The CREATE VIEW statement creates a view with the given name. A view can be created for another user by specifying the owner. A view name can be used in place of a table name in SELECT, DELETE, UPDATE, and INSERT statements. Views, however, do not physically exist in the database as tables. They are derived each time they are used. The view is derived as the result of the SELECT statement specified in the CREATE VIEW statement. Table names used in a view should be qualified by the user ID of the table owner. Otherwise, a different user ID might not be able to find the table or might get the wrong table.

The columns in the view are given the names specified in the column name list. If the column name list is not specified, then the view columns are given names from the select list items. In order to use the names from the select list items, the items must be a simple column name or they must have an alias-name specified (see "SELECT statement").

Views can be updated provided the SELECT statement defining the view does not contain a GROUP BY clause, an aggregate function, or involve a UNION operation.

The WITH CHECK OPTION clause rejects any updates and inserts to the view that do not meet the criteria of the view as defined by its SELECT statement.

The SELECT statement must not have an ORDER BY clause on it. It may have a GROUP BY clause and may be a UNION.

Examples

Create a view showing all information for male employees only. This view has the same column names as the base table.

     CREATE VIEW male_employee
     AS SELECT *
     FROM Employee
     WHERE Sex = 'M'

Create a view showing employees and the departments they belong to.

     CREATE VIEW emp_dept
     AS SELECT emp_lname, emp_fname, dept_name
     FROM Employee JOIN Department
     ON Employee.dept_id = Department.dept_id