Database Reference
In-Depth Information
In the query results shown in Figure 4-6, the column headings are PNum, PDesc, OnHd, and Price.
124
New field
names
Data in view
FIGURE 4-6
Datasheet for the Housewares view with changed field names
The Housewares view is an example of a row-and-column subset view because it consists of a subset of
the rows and columns in some individual table, which, in this case, is the Part table. Because the query can
be any SQL query, a view can also join two or more tables.
Suppose, for example, Francesca needs to know the number and name of each sales rep, along with the
number and name of the customers represented by each sales rep. It would be much simpler for her if this
information were stored in a single table instead of in two tables that she has to join together. She would like
a single table that contains the sales rep number, sales rep name, customer number, and customer name.
Suppose she would also like these fields to be named SNum, SLast, SFirst, CNum, and CName, respectively.
She could use a join in the CREATE VIEW command as follows:
CREATE VIEW SalesCust (SNum, SLast, SFirst, CNum, CName) AS
SELECT Rep.RepNum, LastName, FirstName, CustomerNum, CustomerName
FROM Rep, Customer
WHERE Rep.RepNum=Customer.RepNum
;
Given the current data in the Premiere Products database, conceptually this view is the table shown in
Figure 4-7.
SalesCust
SNum
SLast
SFirst
CNum
CName
20
Kaiser
Valerie
148
Al's Appliance and Sport
20
Kaiser
Valerie
524
Kline's
20
Kaiser
Valerie
842
All Season
35
Hull
Richard
282
Brookings Direct
35
Hull
Richard
408
The Everything Shop
35
Hull
Richard
687
Lee's Sport and Appliance
35
Hull
Richard
725
Deerield's Four Seasons
65
Perez
Juan
356
Ferguson's
65
Perez
Juan
462
Bargains Galore
65
Perez
Juan
608
Johnson's Department Store
FIGURE 4-7
SalesCust view
