Chapter 9: SQL Derived Tables

Using views with SQL derived tables

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

Data can be inserted 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"

You must 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

View this book as PDF