Oracle SQL Jumpstart with Examples- P13

Oracle SQL Jumpstart with Examples- P13: Review"As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples- P13 570 A.3 SCHEMAOLTP.SQL A.2 CREATEUSER.SQL SPOOL log/CREATEUSER.LOG; DROP USER MUSIC CASCADE; CREATE USER MUSIC IDENTIFIED BY MUSIC DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS QUOTA UNLIMITED ON TEMP; GRANT CONNECT,RESOURCE TO MUSIC; GRANT UNLIMITED TABLESPACE TO MUSIC; SPOOL OFF; A.3 SCHEMAOLTP.SQL SPOOL log/SCHEMA_OLTP.LOG; DROP TABLE INSTRUMENT CASCADE CONSTRAINTS; CREATE TABLE INSTRUMENT ( INSTRUMENT_ID NUMBER NOT NULL ,SECTION_ID NUMBER NULL ,NAME VARCHAR2(32) NOT NULL ,CONSTRAINT XPKINSTRUMENT PRIMARY KEY (INSTRUMENT_ID) ,CONSTRAINT FKI_1 FOREIGN KEY (SECTION_ID) REFERENCES INSTRUMENT ); CREATE UNIQUE INDEX XUK_INSTRUMENT_NAME ON INSTRUMENT (NAME); CREATE INDEX XFK_I_1 ON INSTRUMENT (SECTION_ID); DROP TABLE ARTIST CASCADE CONSTRAINTS; CREATE OR REPLACE TYPE INSTRUMENTSCOLLECTION AS VARRAY(10) OF VARCHAR2(32); / CREATE TABLE ARTIST ( ARTIST_ID NUMBER NOT NULL ,NAME VARCHAR2(32) NOT NULL ,STREET VARCHAR2(32) ,POBOX CHAR(20) ,CITY VARCHAR2(32) ,STATE_PROVINCE VARCHAR2(32) ,COUNTRY VARCHAR2(32) ,ZIP CHAR(10) ,EMAIL VARCHAR2(32) ,INSTRUMENTS INSTRUMENTSCOLLECTIONPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. A.3 SCHEMAOLTP.SQL 571 ,CONSTRAINT XPKARTIST PRIMARY KEY (ARTIST_ID) ); CREATE UNIQUE INDEX XUK_ARTIST_NAME ON ARTIST (NAME); DROP TABLE SONG CASCADE CONSTRAINTS; CREATE TABLE SONG ( SONG_ID NUMBER NOT NULL ,ARTIST_ID NUMBER NOT NULL ,TITLE VARCHAR2(64) NOT NULL ,RECORDING_DATE DATE ,PLAYING_TIME CHAR(10) ,RECORDING BLOB ,CONSTRAINT XPKSONG PRIMARY KEY (SONG_ID) ,CONSTRAINT FKSONG_1 FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST ); CREATE INDEX XFK_SONG_1 ON SONG (ARTIST_ID); CREATE UNIQUE INDEX XUK_SONG_TITLE ON SONG (TITLE); DROP TABLE GUESTAPPEARANCE CASCADE CONSTRAINTS; CREATE TABLE GUESTAPPEARANCE ( SONG_ID NUMBER NOT NULL ,GUESTARTIST_ID NUMBER NO NULL ,COMMENT_TEXT VARCHAR2(256) ,CONSTRAINT XPKGUESTAPPEARANCE PRIMARY KEY (SONG_ID,GUESTARTIST_ID) ,CONSTRAINT FKGUESTAPPEARANCE_1 FOREIGN KEY (GUESTARTIST_ID) REFERENCES ARTIST ,CONSTRAINT FKGUESTAPPEARANCE_2 FOREIGN KEY (SONG_ID) REFERENCES SONG ); CREATE INDEX XFK_GUESTAPPEARANCE_1 ON GUESTAPPEARANCE (GUESTARTIST_ID); CREATE INDEX XFK_GUESTAPPEARANCE_2 ON GUESTAPPEARANCE (SONG_ID); DROP TABLE INSTRUMENTATION CASCADE CONSTRAINTS; CREATE TABLE INSTRUMENTATION ( SONG_ID NUMBER NOT NULL ,GUESTARTIST_ID NUMBER NOT NULL ,INSTRUMENT_ID NUMBER NOT NULL ,COMMENT_TEXT VARCHAR2(256) ,CONSTRAINT XPKISG PRIMARY KEY (SONG_ID,GUESTARTIST_ID,INSTRUMENT_ID) ,CONSTRAINT FKISG_1 FOREIGN KEY (SONG_ID,GUESTARTIST_ID) REFERENCES GUESTAPPEARANCE ,CONSTRAINT FKISG_2 FOREIGN KEY (INSTRUMENT_ID) REFERENCES INSTRUMENT ); CREATE INDEX XFK_ISG_1 ON INSTRUMENTATION (INSTRUMENT_ID); CREATE INDEX XFK_ISG_2 ON INSTRUMENTATION (SONG_ID, GUESTARTIST_ID); Appendix APlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 572 A.3 SCHEMAOLTP.SQL DROP TABLE GENRE CASCADE CONSTRAINTS; CREATE TABLE GENRE ( GENRE_ID NUMBER NOT NULL ,STYLE_ID NUMBER ,GENRE VARCHAR2(32) ,CONSTRAINT XPKGENRE PRIMARY KEY (GENRE_ID) ,CONSTRAINT FKG_1 FOREIGN KEY (STYLE_ID) REFERENCES GENRE ); CREATE INDEX XFK_G_1 ON GENRE (STYLE_ID); DROP TABLE MUSICCD CASCADE CONSTRAINTS; CREATE TABLE MUSICCD ( MUSICCD_ID NUMBER NOT NULL ,GENRE_ID NUMBER ,TITLE VARCHAR2(32) ,PRESSED_DATE DATE ,PLAYING_TIME CHAR(10) ,LIST_PRICE FLOAT ,CONSTRAINT XPKMUSICCD PRIMARY KEY (MUSICCD_ID) ,CONSTRAINT FKMCD_1 FOREIGN KEY (GENRE_ID) REFERENCES GENRE ); CREATE UNIQUE INDEX XUK_MUSICCD_TITLE ON MUSICCD (TITLE); CREATE INDEX XFK_MCD_1 ON MUSICCD (GENRE_ID); DROP TABLE CDTRACK CASCADE CONSTRAINTS; CREATE TABLE CDTRACK ( MUSICCD_ID NUMBER NOT NULL ,SONG_ID NUMBER NOT NULL ,TRACK_SEQ_NO NUMBER NOT NULL ,CONSTRAINT XPKCDTRACK PRIMARY KEY (MUSICCD_ID,SONG_ID) ,CONSTRAINT FKCDTRACK_1 FOREIGN KEY (SONG_ID) REFERENCES SONG ,CONSTRAINT FKCDTRACK_2 FOREIGN KEY (MUSICCD_ID) REFERENCES MUSICCD ); CRE ...