Database Reference
In-Depth Information
Figure 19.5
Views Can
Contain
Subqueries of Their
Own.
The next view combines the SONG, CDTRACK, and MUSICCD tables
to show the title of the CD along with details about each song on the CD.
CREATE VIEW CD_DETAILS AS
SELECT CD.TITLE CDTITLE, CD.PRESSED_DATE
, CT.TRACK_SEQ_NO ,S.TITLE SONGTITLE, A.NAME
, S.PLAYING_TIME
FROM MUSICCD CD, CDTRACK CT, SONG S, ARTIST A
WHERE CD.MUSICCD_ID = CT.MUSICCD_ID
AND CT.SONG_ID = S.SONG_ID
AND S.ARTIST_ID = A.ARTIST_ID
ORDER BY 1, 3;
This view has column aliases for the two TITLE columns (one in the
MUSICCD table and the other in the SONG table); it has an ORDER BY
clause; and it uses Oracle's proprietary syntax for the join.
Figure 19.6
ALTER VIEW and
DROP VIEW
Syntax.
