Creating Views with Multiple Underlying Objects

Another issue in how views work is the underlying objects. As you've seen, views can be based on one or many underlying objects. The objects can be connected with joins and/or subqueries and can be tables and/or views.

Using Subqueries and Joins

Here is an example of a view definition that includes three joins and a subquery. It finds the author ID, title ID, publisher, and price of each book with a price that's higher than the average of all the books' prices. (Including the author ID means that you'll see more than one row for books with multiple authors.)

SQL
create view highaverage
as
select authors.au_id, titles.title_id, pub_name, price
from authors, titleauthors, titles, publishers
where authors.au_id = titleauthors.au_id and
     titles.title_id = titleauthors.title_id and
     titles.pub_id = publishers.pub_id and
     price >
          (select avg(price)
							          from titles)

Now that the view has been created, you can use it to display the results. In this example, you use the SELECT, WHERE, and ORDER BY clauses to tailor the information you see:

SQL
select price as Price, title_id as BookNum, au_id as
							                Writer
from highaverage
where pub_name = 'Binnet & Hardley'
							order by price, title_id
							Price BookNum Writer
========== ======= ===========
     29.99 MC2222  712-45-1867
     29.99 TC7777  672-71-3249
     29.99 TC7777  267-41-2394
     29.99 TC7777  472-27-2349
     40.95 TC3218  807-91-6654
[5 rows]

Deriving Views from Views

Let's use highaverage to illustrate a view derived from another view. Here's how to create a view that displays all the higher-than-average-priced books published by Binnet & Hardley:

SQL
create view highBandH
as
select *
							from highaverage
							where pub_name = 'Binnet & Hardley'

You can structure your SELECT statement to limit columns and order rows.

SQL
select price, title_id, au_id
from highBandH
order by price, title_id
     price title_id au_id
========== ======== ===========
     29.99 MC2222   712-45-1867
     29.99 TC7777   672-71-3249
     29.99 TC7777   267-41-2394
     29.99 TC7777   472-27-2349
     40.95 TC3218   807-91-6654
[5 rows]

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.219.43.126