If you are interested in viewing only the titles of books written in Colorado, you might create a view like this:
create view vw_colorado_titles as select title from titles, titleauthor, authors where titles.title_id = titleauthor.title_id and titleauthor.au_id = authors.au_id and authors.state ="CO"
You can repeatedly use the view vw_colorado_titles, stored in memory, to display its results:
select * from vw_colorado_titles
Drop the view when it is no longer needed:
drop view vw_colorado_titles
If the query results are only needed once, you might instead use a SQL-derived table:
select title from (select title from titles, titleauthor, authors where titles.title_id = titleauthor.title_id and titleauthor.au_id = authors.au_id and authors.state = "CO") dt_colo_titles
The SQL-derived table created is named dt_colo_titles. The SQL-derived table persists only for the duration of the query, in contrast with a temporary table, which exists for the entire session.
In the previous example for query results that are only needed once, a view is less desirable than a SQL-derived table query because the view is more complicated, requiring both create and drop statements in addition to a select statement. The benefits of creating a view for only one query are additionally offset by the overhead of administrative tasks such as dealing with system catalogs. SQL-derived tables spontaneously create nonpersistent tables which require no administrative tasks. A SQL-derived table used multiple times performs comparably to a query using a view with a cached definition.
