Creating Views

In this section: CREATE VIEW Example: Creating a View

A view is a transient object that inherits most of the characteristics of a table. Like a table, a view is composed of rows and columns. The columns have data types and may, on occasion, contain null values. Views may be queried as if they were tables. But, unlike tables, views are dependent entities. Views are defined by selecting and pasting together elements from existing tables. When a view, V, is referenced in an SQL request, the server creates the illusion that V is a table by associating the appropriate data with it. You may not insert, update, or delete from an SQL view.

In the server, a view does not persist beyond the close of the session in which it is created. A session, for an individual connection, begins when an initial connection is established and continues until no remaining active connections exist.

The server allows you to create one or more views based on the information that is contained within a table, and to delete or drop the views created. The CREATE verb is used to create views and DROP is used to explicitly remove views from the environment.

CREATE VIEW

CREATE VIEW permits the creation of a view based on the fields contained in a table.

CREATE VIEW <view-name> [<column-list>] AS <query-specification>
column-list     ::=  <column-name> [, <column-name>]
query-exp       ::=  <query-exp-head> <order-by-clause>
query-exp-head  ::=  <query-term> | <query-exp> UNION [ALL] <query-term>
order-by-clause ::= ORDER BY <ord-spec> [, <ord-spec>]*
ord-spec        ::=  {<integer> | <column-ref>} [{ASC | DSC}]
query-term      ::=  <query-spec> | ( <query-exp> )
query-spec      ::=  SELECT [<sel-qualifier>] <selection> <table-exp>

Example: Creating a View

CREATE VIEW person_view AS SELECT first_name, last_name FROM person 

where:

person_view

Is the name of the view.

first_name

Is a column name.

last_name

Is a column name.

person

Is a table name.