CREATE VIEW statement

Wed, 03/04/2009 - 20:02 |  hdanzl

Define a persistent viewed table.


Syntax

<view definition> ::=

CREATE VIEW [ schema-name. ] view-name <view specification>

AS <query expression> [ <order by clause> ]

<view specification> ::= [ ( column-name [ { , column-name }... ] ) ]


Usage

The CREATE VIEW statements creates a persistent view in the database based on the query expression. Views are a useful mechanism for defining complex queries once and to use them repeatedly in SQL statements. Views can also be used to enhance database security, for example by exposing only parts of a base table to other users.


Notes

♦ The schema and view names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The view is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.
♦ The optional view specification can be used to rename the columns processed by the query expression. If view specification is specified, then the degree shall be equal to the degree of the query expression.
♦ All table references in the subquery shall identify persistent base tables or views.
♦ Only the metadata about the view is stored in the database. The data itself is processed from the base tables when the view is referenced in SQL statements.
♦ Once created, views can be queried just like ordinary base tables.
♦ Views are read-only.        


Examples

1) The following example creates a view that exposes only some of the columns in the Teachers base table:

       CREATE VIEW teachers_view AS (

SELECT teacherID, teacherName, phone

FROM teachers

       )

2) The following example references teachers_view in a SELECT statement:

       SELECT courseID, teacherID, teacherName

       FROM sections s

       JOIN teachers_view t on t.teacherID = s.teacherID

       ORDER BY courseID


Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

Core SQL

Views are read-only

Support for the ORDER BY clause in <view definition>
‹ CREATE INDEX statementupCREATE TRIGGER statement ›
Print this chapter |  Login or register to post comments |  Send to friend |  PDF version |  metadata Tags:
Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference | SQL Statements | Schema Statements

Powered by Drupal and Drupal Theme created with Artisteer.