CREATE VIEW

CREATE VIEW statement is used to create a virtual table in the database.

view_definition
    : CREATE [OR REPLACE] 
      VIEW [IF NOT EXISTS] view_name [ (column_name_list) ]
      AS query_expression
      [ WITH CHECK OPTION ]

This clause forces the dropping of an existing table with the specified name and then a new empty table will be created as specified. So effectively this is the same as

DROP TABLE T;
CREATE TABLE T ....

ATTENTION: 'CREATE OR REPLACE' will destroy all sub-objects of the existing view, such as triggers, and constraints. If you need to preserve sub-objects, use the ALTER VIEW command.

If the table does not exist, then nothing happens.

NOTE: 'OR REPLACE' syntax Valentina has taken from Oracle/Postgre world.

The IF NOT EXISTS option allows you to suppress the error message in case such a view already exists. This makes it much easier to perform SQL dumps without interruption.

NOTE: 'IF NOT EXISTS' syntax Valentina has taken from MySQL world.

Specifies the name of the new view. A view belongs to a database. Tables and Views share the same namespace within a database, so a database cannot contain a table and a view with the same name.

Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.

Columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth.

Specifies a SELECT statement that defines the view.

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

• Valentina 3.0 has read-only views.

CREATE VIEW wMale AS 
SELECT * FROM tblPerson WHERE fldGender = 'M';