Creating Views

The CREATE TABLE command is used to create "base tables"; these are tables that physically exist in the database. A view in SQL is a virtual or logical table created from one or more base tables and views. A view does not physically exist - only the definition of the view is stored. Views allow users to have different perceptions of the database determined by their requirements. Views can also simplify retrievals: a complex retrieval can be stored as a view and a simple SELECT can then be used to access data in the view.

A view is created with the CREATE VIEW command.

Format:

CREATE [ OR Replace] [Force|NoForce] VIEW view-name [(column-name-list)]
AS select-statement

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]]

view-name:   is the name to be used for this table in SQL statements referencing the table

column-name-list:   a list of names, separated by commas, for each column in the view. If omitted, the column names will be the same as the table from which the view was created. The data type will be the same as the data type of the column in the underlying table.

select-statement: this is a SELECT command that defines the view.

Or Replace: re-creates view if it already exists

Force: creates view even if base tables do not exist

NoForce: creates view only if base tables exist

With Check Option: only rows accessible to the view can be inserted or updated

With Read Only: no DML can be performed on this view

Note: the select-statement may not contain an ORDER-BY clause nor the UNION operator.

Examples:

The following view of the HOLDING table will contain only those holdings in portfolio P003:

create view holdpf3
    as select portfoliono,stocksymbol,numbershares
            from holding
            where portfoliono = 'P003';

The following view will contain the current value of holdings in the Holding table:

create view current_value (portfoliono, stocksymbol, totalprice)
    as select portfoliono, holding.stocksymbol, numbershares * currentprice
            from holding, stock
            where holding.stocksymbol = stock.stocksymbol;

Note that a column-name-list must be specified if the select-list contains a derived or calculated column.

Deleting a view

To delete a view:

    DROP VIEW view-name

Note: all other views that reference this view are also deleted.

Inline views (not schema objects)

An inline view is a subquery with an alias that you can use within an SQL statement.

Select managername, p.maxcash

From manager m, (Select managerno, max(cash) maxcash

From portfolio

Group by managerno) p

Where m.managerno = p.managerno;

Show manager names and the maximum cash for their portfolios.