Views with SQL-Derived Tables

You can create a view using a SQL-derived table.

The following example creates a view, view_colo_publishers, using a SQL-derived table, dt_colo_pubs, to display publishers based in Colorado:

create view view_colo_publishers (Pub_Id, Publisher,
City, State)
as select pub_id, pub_name, city, state
from
(select * from publishers where state="CO")
dt_colo_pubs

You can insert data through a view that contains a SQL-derived table if the insert rules and permission settings for the derived-table expression follow the insert rules and permission settings for the select part of the create view statement. For example, the following insert statement inserts a row through the view_colo_publishers view into the publishers table on which the view is based:

insert view_colo_publishers
values ('1799', 'Gigantico Publications', 'Denver',
'CO')

You can also update existing data through a view that uses a SQL-derived table:

update view_colo_publishers
set Publisher = "Colossicorp Industries"
where Pub_Id = "1699"

Note: Specify the column names of the view definition, not the column names of the underlying table.

Views that use a SQL-derived table are dropped in the standard manner:

drop view view_colo_publishers