SAND CDBMS SQL Reference Guide
CREATE VIEW

The CREATE VIEW command is used to define a view (or virtual table) that is based on one or more tables or other views. While a view does not actually contain data, it provides indirect access to data contained in the tables upon which the view is based. Views may be used to define subsets of tables, or to combine data from several tables into a logical view. Views may contain expressions that are computed from other columns, including aggregate functions such as averages, totals, and so on.

Required Privileges

To execute the CREATE VIEW command, the user authorization must own the schema where the view will be created, possess the OWNER privilege on the schema, or possess DBA privileges.

For every base table or view identified in the query expression (SELECT statement) of the CREATE VIEW statement, the user authorization must own the table/view, own or possess OWNER privileges on the schema containing the table/view, possess SELECT privileges on the table/view, or possess DBA privileges.

Syntax

view name
The view name argument is an identifier that names the view uniquely within its schema. To create a view in a schema other than the current schema, prefix the view name with the desired schema name and a period ( . ), that is, schema-name.view-name. A view cannot have the same name as another view or table in the schema. View names can be up to 128 characters long. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a view name, as this may cause problems when referencing the view in certain SQL statements.

view-col-name list
The view-col-name argument is an SQL identifier (up to 128 characters long) specifying a column in the view. Column names for views must satisfy the same criteria as base table column names. If multiple view-col-name items are specified, the number of these columns must match the number of columns in the result table specified in the query expression of the CREATE VIEW statement.

The view column name list is optional. If the view-col-name list is absent, the view columns take the column names or column aliases specified in the query expression result table. If the result table of the query expression has duplicate column names or unnamed columns derived from constants, functions, or expressions, a view-col-name list must be specified.

query expression
Preceded by the keyword AS, the query expression argument is a SELECT statement producing a results table that constitutes the data to be contained in the view. The query expression in a view definition can contain multiple SELECT statements connected by UNION operators. When creating views with query expressions, all referenced tables must exist in the database. When a user queries a view, the system reads the view definition, and performs the corresponding query to obtain the result.

Description

Privileges on Views
The creator of a view has the authority to drop the view, as well as the SELECT privilege on the view. To obtain a grantable SELECT privilege on the view, the view owner must already possess grantable SELECT privileges on every table or view identified in the first FROM clause of the view query expression. These grantable privileges must be acquired by the owner before the creation of the view.

Value Expressions
Value expressions, including those involving aggregate functions, are permitted in view definitions. If value expressions are included in the query expression (SELECT), you must explicitly name all columns of the view.

Joins
You may create views by joining other tables and views. If the tables or views being joined contain duplicate names, the SELECT statement must explicitly name all columns. See the section on Joins in the entry for the SELECT statement for more information.

GROUP BY and HAVING Clauses
HAVING clauses are allowed in view definitions, with or without a corresponding GROUP BY clauses. If a HAVING clause is used in conjunction with a GROUP BY clause, it must follow the GROUP BY clause in the statement.

ORDER BY Clause
An ORDER BY clause can be used in the query expression that defines a view when a FETCH FIRST...ONLY clause is included as well.

x
Examples

The following example defines a view that is a subset of the part table. Because the optional view column name list is omitted, the view column names will be the same as the column names of the part table:

   CREATE VIEW redparts AS
        SELECT *
        FROM part
        WHERE color = 'RED';

If all records are retrieved from the redparts view, the result would be:

The next example shows a view defined by joining three tables: part, supplier, and partsupp.

   CREATE VIEW s_ps_p
        (s#, supplier, p#, part, quantity) AS
        SELECT sno, sname, pno, pname, qty
        FROM supplier s, partsupp ps, part p
        WHERE s.sno = ps.sno
        AND p.pno = ps.pno;

If all records are retrieved from the s_ps_p view, the result would be:
 

The following is an example of a view containing aggregate functions and a GROUP BY clause:

CREATE VIEW order_statistics
(o_no, total_items, total_order) AS
     SELECT o_no, COUNT(p_no), SUM(quantity * price)
     FROM items
     GROUP BY o_no;

Selecting all records from the view will produce the following result: