Database Reference
In-Depth Information
Figure 19.3
Constraint Views
Require Inserted
Rows to Fit Inside
the View.
SELECT A.NAME, I.NAME
FROM ARTIST A JOIN INSTRUMENTATION IA
ON (IA.GUESTARTIST_ID = A.ARTIST_ID)
JOIN INSTRUMENT I ON (IA.INSTRUMENT_ID = I.INSTRUMENT_ID);
Now let's look at the rows returned from the view issuing the following
query.
SELECT * FROM INSTRUMENTS;
The view joins three tables and displays two columns of information.
Notice the list of columns just after the view name. This is needed for the
view because the two columns in the SELECT clause happen to have the
same name. By listing different names for each of the two columns, the
view can be created.
Here is a view that summarizes an artist's billing for studio time.
CREATE VIEW ARTIST_MONTHLY_STATEMENT AS
SELECT ARTIST_ID, NAME
, TO_CHAR(DUE_DATE,'MON/YY') BILLING_MONTH
, SUM(AMOUNT_CHARGED) DUE, SUM(AMOUNT_PAID) PAID
, SUM(AMOUNT_CHARGED) - SUM(AMOUNT_PAID) BALANCE
FROM ARTIST NATURAL JOIN STUDIOTIME
GROUP BY ARTIST_ID, NAME, TO_CHAR(DUE_DATE,'MON/YY');
 
Search WWH ::




Custom Search