Navigation:  NexusDB Guide > SQL Reference > SQL Statements > Schema Statements >

CREATE VIEW statement

Previous pageReturn to chapter overviewNext page

NexusDB V3 Manual

CREATE VIEW statement

button_main button_prev button_next

NexusDB Manual V3 > SQL Reference > SQL Statements > Schema Statements

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>

 

© Nexus Database Systems Pty Ltd.

nexus_logo