SET ECHO OFF UNDEF DBNAME PROMPT WHAT IS THE DATABASE OR NETWORK NAME? (SUCH AS: OLTP) ACCEPT DBNAME PROMPT CONNECTING TO SYSTEM NOW. CONNECT SYSTEM@&&DBNAME @CREATEUSER.SQL CONNECT MOVIES/MOVIES@&&DBNAME @SCHEMAOLTP.SQL @SEQUENCES.SQL @STUDIO.SQL; @RATING.SQL; @GENRE.SQL; @SAGA.SQL; @DIRECTOR.SQL; @ACTOR.SQL; @AWARD.SQL; @MOVIE.SQL; @PART.SQL; @RECOGNITION.SQL; @SCHEMADW.SQL; @DIMENSIONS.SQL; @FACTS.SQL; EXIT;
SPOOL log/CREATEUSER.LOG; DROP USER MOVIES CASCADE; CREATE USER MOVIES IDENTIFIED BY MOVIES DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS QUOTA UNLIMITED ON TEMP; GRANT CONNECT,RESOURCE TO MOVIES; GRANT UNLIMITED TABLESPACE TO MOVIES; SPOOL OFF;
CREATE TABLE RATING(
RATING_ID NUMBER NOT NULL
,GROUP_ID NUMBER NULL
,MPAA CHAR(5) NULL
,RATING VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_RATING PRIMARY KEY(RATING_ID) USING INDEX TABLESPACE INDX
,CONSTRAINT FK_RATING_1 FOREIGN KEY(GROUP_ID) REFERENCES RATING
) TABLESPACE DATA;
CREATE UNIQUE INDEX XAK_RATING_1 ON RATING(RATING) TABLESPACE INDX;
CREATE INDEX XFK_RATING_1 ON RATING(GROUP_ID) TABLESPACE INDX;
SPOOL log/SCHEMAOLTP.LOG;
DROP TABLE STUDIO CASCADE CONSTRAINTS;
CREATE TABLE STUDIO(
STUDIO_ID NUMBER NOT NULL
,STUDIO VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_STUDIO PRIMARY KEY(STUDIO_ID)
);
CREATE UNIQUE INDEX XAK_STUDIO_1 ON STUDIO(STUDIO);
DROP TABLE RATING CASCADE CONSTRAINTS;
CREATE TABLE RATING(
RATING_ID NUMBER NOT NULL
,GROUP_ID NUMBER NULL
,MPAA CHAR(5) NULL
,RATING VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_RATING PRIMARY KEY(RATING_ID)
,CONSTRAINT FK_RATING_1 FOREIGN KEY(GROUP_ID) REFERENCES RATING
);
CREATE UNIQUE INDEX XAK_RATING_1 ON RATING(RATING);
CREATE INDEX XFK_RATING_1 ON RATING(GROUP_ID);
DROP TABLE GENRE CASCADE CONSTRAINTS;
CREATE TABLE GENRE(
GENRE_ID NUMBER NOT NULL
,PARENT_ID NUMBER NULL
,GENRE VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_GENRE PRIMARY KEY(GENRE_ID)
,CONSTRAINT FK_GENRE_PARENT FOREIGN KEY(PARENT_ID) REFERENCES GENRE
);
CREATE UNIQUE INDEX XAK_GENRE_1 ON GENRE(GENRE);
CREATE INDEX XFK_GENRE_1 ON GENRE(PARENT_ID);
DROP TABLE DIRECTOR CASCADE CONSTRAINTS;
CREATE TABLE DIRECTOR(
DIRECTOR_ID NUMBER NOT NULL
,NAME VARCHAR2(32) NOT NULL
,GENDER CHAR(1) NOT NULL
,TALENT VARCHAR2(64) NULL
,CONSTRAINT XPK_DIRECTOR PRIMARY KEY(DIRECTOR_ID)
);
CREATE UNIQUE INDEX XAK_DIRECTOR_1 ON DIRECTOR(NAME);
DROP TABLE ACTOR CASCADE CONSTRAINTS;
CREATE TABLE ACTOR(
ACTOR_ID NUMBER NOT NULL
,NAME VARCHAR2(32) NOT NULL
,GENDER CHAR(1) NOT NULL
,TYPECAST VARCHAR2(64) NULL
,CONSTRAINT XPK_ACTOR PRIMARY KEY(ACTOR_ID)
);
CREATE UNIQUE INDEX XAK_ACTOR_1 ON ACTOR(NAME);
DROP TABLE AWARD CASCADE CONSTRAINTS;
CREATE TABLE AWARD(
AWARD_ID NUMBER NOT NULL
,AWARD VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_AWARD PRIMARY KEY(AWARD_ID)
);
CREATE UNIQUE INDEX XAK_AWARD_1 ON AWARD(AWARD);
DROP TABLE SAGA CASCADE CONSTRAINTS;
CREATE TABLE SAGA(
SAGA_ID NUMBER NOT NULL
,SAGA VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_SAGA PRIMARY KEY (SAGA_ID)
);
CREATE UNIQUE INDEX XAK_SAGA_1 ON SAGA(SAGA);
DROP TABLE MOVIE CASCADE CONSTRAINTS;
CREATE OR REPLACE TYPE EXTRASCOLLECTION AS VARRAY(100) OF VARCHAR2(32);
/
CREATE TABLE MOVIE(
MOVIE_ID NUMBER NOT NULL
,SAGA_ID NUMBER NULL
,GENRE_ID NUMBER NOT NULL
,RATING_ID NUMBER NOT NULL
,DIRECTOR_ID NUMBER NOT NULL
,STUDIO_ID NUMBER NOT NULL
,TITLE VARCHAR2(32) NOT NULL
,YEAR NUMBER NOT NULL
,RELEASE DATE NULL
,LIST_PRICE FLOAT NULL
,RANK NUMBER NULL
,REVIEWS NUMBER NULL
,REVIEW_RANK NUMBER(2,1) NULL
,CONSTRAINT XPK_MOVIE PRIMARY KEY(MOVIE_ID)
,CONSTRAINT FK_MOVIE_1 FOREIGN KEY(SAGA_ID) REFERENCES SAGA
,CONSTRAINT FK_MOVIE_2 FOREIGN KEY(STUDIO_ID) REFERENCES STUDIO
,CONSTRAINT FK_MOVIE_3 FOREIGN KEY(GENRE_ID) REFERENCES GENRE
,CONSTRAINT FK_MOVIE_4 FOREIGN KEY(RATING_ID) REFERENCES RATING
,CONSTRAINT FK_MOVIE_5 FOREIGN KEY(DIRECTOR_ID) REFERENCES DIRECTOR
);
CREATE UNIQUE INDEX XAK_MOVIE_1 ON MOVIE(TITLE);
CREATE INDEX XFK_MOVIE_1 ON MOVIE(SAGA_ID);
CREATE INDEX XFK_MOVIE_2 ON MOVIE(STUDIO_ID);
CREATE INDEX XFK_MOVIE_3 ON MOVIE(GENRE_ID);
CREATE INDEX XFK_MOVIE_4 ON MOVIE(RATING_ID);
CREATE INDEX XFK_MOVIE_5 ON MOVIE(DIRECTOR_ID);
DROP TABLE PART CASCADE CONSTRAINTS;
CREATE TABLE PART(
MOVIE_ID NUMBER NOT NULL
,ACTOR_ID NUMBER NOT NULL
,LEAD_ROLE CHAR(1) DEFAULT 'N' NULL CHECK(LEAD_ROLE IN('Y','N'))
,SUPPORTING_ROLE CHAR(1) DEFAULT 'N' NULL CHECK(SUPPORTING_ROLE IN('Y','N'))
,CONSTRAINT XPK_PART PRIMARY KEY(MOVIE_ID, ACTOR_ID)
,CONSTRAINT FK_PART_1 FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE
,CONSTRAINT FK_PART_2 FOREIGN KEY(ACTOR_ID) REFERENCES ACTOR
);
CREATE INDEX XFK_PART_1 ON PART(MOVIE_ID);
CREATE INDEX XFK_PART_2 ON PART(ACTOR_ID);
DROP TABLE RECOGNITION CASCADE CONSTRAINTS;
CREATE TABLE RECOGNITION(
AWARD_ID NUMBER NOT NULL
,MOVIE_ID NUMBER NOT NULL
,DIRECTOR_ID NUMBER NULL
,ACTOR_ID NUMBER NULL
,CONSTRAINT XPK_RECOGNITION PRIMARY KEY(AWARD_ID, MOVIE_ID)
,CONSTRAINT FK_RECOGNITION_1 FOREIGN KEY(AWARD_ID) REFERENCES AWARD
,CONSTRAINT FK_RECOGNITION_2 FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE
,CONSTRAINT FK_RECOGNITION_3 FOREIGN KEY(DIRECTOR_ID) REFERENCES DIRECTOR
,CONSTRAINT FK_RECOGNITION_4 FOREIGN KEY(ACTOR_ID) REFERENCES ACTOR
);
CREATE INDEX XFK_RECOGNITION_1 ON RECOGNITION(AWARD_ID);
CREATE INDEX XFK_RECOGNITION_2 ON RECOGNITION(MOVIE_ID);
CREATE INDEX XFK_RECOGNITION_3 ON RECOGNITION(DIRECTOR_ID);
CREATE INDEX XFK_RECOGNITION_4 ON RECOGNITION(ACTOR_ID);
SPOOL OFF;
CREATE TABLE RATING(
RATING_ID NUMBER NOT NULL
,GROUP_ID NUMBER NULL
,MPAA CHAR(5) NULL
,RATING VARCHAR2(32) NOT NULL
,CONSTRAINT XPK_RATING PRIMARY KEY(RATING_ID) USING INDEX TABLESPACE INDX
,CONSTRAINT FK_RATING_1 FOREIGN KEY(GROUP_ID) REFERENCES RATING
) TABLESPACE DATA;
CREATE UNIQUE INDEX XAK_RATING_1 ON RATING(RATING) TABLESPACE INDX;
CREATE INDEX XFK_RATING_1 ON RATING(GROUP_ID) TABLESPACE INDX;
SPOOL log/SCHEMADW.LOG; -- --dimensions -- DROP TABLE REGION CASCADE CONSTRAINTS; CREATE TABLE REGION ( REGION_ID NUMBER NOT NULL ,REGION VARCHAR2(32) ,CONSTRAINT XPKREGION PRIMARY KEY (REGION_ID) ); CREATE UNIQUE INDEX XUK_REGION_NAME ON REGION(REGION); DROP TABLE COUNTRY CASCADE CONSTRAINTS; CREATE TABLE COUNTRY ( COUNTRY_ID NUMBER NOT NULL ,REGION_ID NUMBER ,COUNTRY VARCHAR2(32) ,CONSTRAINT XPKCOUNTRY PRIMARY KEY (COUNTRY_ID) ,CONSTRAINT FKCOUNTRY_1 FOREIGN KEY (REGION_ID) REFERENCES REGION ); CREATE UNIQUE INDEX XUK_COUNTRY_NAME ON COUNTRY(COUNTRY); CREATE INDEX XFKCOUNTRY_1 ON COUNTRY (REGION_ID); CREATE OR REPLACE TYPE REGIONSCOLLECTION AS TABLE OF VARCHAR2(32); / DROP TABLE FORMAT CASCADE CONSTRAINTS; CREATE TABLE FORMAT( FORMAT_ID NUMBER NOT NULL ,TYPE CHAR(3) NOT NULL ,ENCODING CHAR(5) NOT NULL ,EDITION CHAR(10) NULL ,REGIONS REGIONSCOLLECTION ,CONSTRAINT XPK_FORMAT PRIMARY KEY(FORMAT_ID) ) NESTED TABLE REGIONS STORE AS REGIONSTAB; DROP TABLE SOURCE CASCADE CONSTRAINTS; CREATE TABLE SOURCE( SOURCE_ID NUMBER NOT NULL ,SOURCE VARCHAR2(32) NOT NULL ,CONSTRAINT XPK_SOURCE PRIMARY KEY(SOURCE_ID) ); CREATE UNIQUE INDEX XAK_SOURCE_1 ON SOURCE(SOURCE); -- --facts -- DROP TABLE SALE; CREATE TABLE SALE( SALE_ID NUMBER NOT NULL ,MOVIE_ID NUMBER NOT NULL ,SOURCE_ID NUMBER ,FORMAT_ID NUMBER ,REGION_ID NUMBER ,COUNTRY_ID NUMBER ,SALE_PRICE FLOAT NOT NULL ,SALE_DATE DATE NOT NULL ,CONSTRAINT XPKSALE PRIMARY KEY (SALE_ID) ,CONSTRAINT FKSALE_1 FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE ,CONSTRAINT FKSALE_2 FOREIGN KEY (SOURCE_ID) REFERENCES SOURCE ,CONSTRAINT FKSALE_3 FOREIGN KEY (FORMAT_ID) REFERENCES FORMAT ,CONSTRAINT FKSALE_4 FOREIGN KEY (REGION_ID) REFERENCES REGION ,CONSTRAINT FKSALE_5 FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY ); CREATE INDEX XFK_SALE_1 ON SALE (MOVIE_ID); CREATE BITMAP INDEX XFK_SALE_2 ON SALE (SOURCE_ID); CREATE BITMAP INDEX XFK_SALE_3 ON SALE (FORMAT_ID); CREATE INDEX XFK_SALE_4 ON SALE (REGION_ID); CREATE INDEX XFK_SALE_5 ON SALE (COUNTRY_ID); CREATE INDEX XAK_REGIONAL_1 ON SALE (REGION_ID, COUNTRY_ID, MOVIE_ID); CREATE INDEX XAK_FORMATTING_1 ON SALE (REGION_ID, FORMAT_ID, MOVIE_ID); CREATE INDEX XAK_SOURCES_1 ON SALE (SOURCE_ID, MOVIE_ID); CREATE INDEX XAK_REGIONAL_2 ON SALE (REGION_ID, COUNTRY_ID, MOVIE_ID, SALE_DATE); CREATE INDEX XAK_FORMATTING_2 ON SALE (REGION_ID, FORMAT_ID, MOVIE_ID, SALE_DATE); CREATE INDEX XAK_SOURCES_2 ON SALE (SOURCE_ID, MOVIE_ID, SALE_DATE); SPOOL OFF;
SPOOL log/SEQUENCES.LOG; DROP SEQUENCE SAGA_SEQ; DROP SEQUENCE RATING_SEQ; DROP SEQUENCE STUDIO_SEQ; DROP SEQUENCE GENRE_SEQ; DROP SEQUENCE LANGUAGE_SEQ; DROP SEQUENCE PRODUCER_SEQ; DROP SEQUENCE DIRECTOR_SEQ; DROP SEQUENCE AWARD_SEQ; DROP SEQUENCE ACTOR_SEQ; DROP SEQUENCE MOVIE_SEQ; DROP SEQUENCE REGION_SEQ; DROP SEQUENCE COUNTRY_SEQ; DROP SEQUENCE FORMAT_SEQ; DROP SEQUENCE SOURCE_SEQ; DROP SEQUENCE SALE_SEQ; CREATE SEQUENCE SAGA_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE RATING_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE STUDIO_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE GENRE_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE LANGUAGE_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE PRODUCER_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE DIRECTOR_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE AWARD_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE ACTOR_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE MOVIE_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE REGION_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE COUNTRY_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE FORMAT_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE SOURCE_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE SALE_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE; SPOOL OFF;
SPOOL log/STUDIO.LOG; INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Columbia/TriStar Studios'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Twentieth Century Fox'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Lucasfilm Limited'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Metro Goldwyn Mayer'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Miramax Studios'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Paramount Pictures'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Warner Brothers'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Pinewood Studios'); INSERT INTO STUDIO(STUDIO_ID,STUDIO) VALUES(STUDIO_SEQ.NEXTVAL,'Universal Studios'); COMMIT; SPOOL OFF;
SPOOL log/RATING.LOG; --MPAA ratings INSERT INTO RATING(RATING_ID,RATING) VALUES(RATING_SEQ.NEXTVAL,'Family Viewing'); INSERT INTO RATING(RATING_ID,GROUP_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,(SELECT RATING_ID FROM RATING WHERE RATING='Family Viewing'),'G','General'); INSERT INTO RATING(RATING_ID,GROUP_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,(SELECT RATING_ID FROM RATING WHERE RATING='Family Viewing'),'PG','Parent Guidance'); INSERT INTO RATING(RATING_ID,GROUP_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,(SELECT RATING_ID FROM RATING WHERE RATING='Family Viewing'),'PG-13','Parent Guidance Under 13'); INSERT INTO RATING(RATING_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,'R','Restricted'); INSERT INTO RATING(RATING_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,'NC-17','No Children Under 17'); INSERT INTO RATING(RATING_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,'NR','Not Rated'); INSERT INTO RATING(RATING_ID,MPAA,RATING) VALUES(RATING_SEQ.NEXTVAL,'U','Unrated'); COMMIT; SPOOL OFF;
SPOOL log/GENRE.LOG; --level 1 INSERT INTO GENRE(GENRE_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,'Non-Action'); INSERT INTO GENRE(GENRE_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,'Action'); INSERT INTO GENRE(GENRE_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,'Classic'); --level 2 INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Comedy'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Musical'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Horror'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Thriller'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Tragedy'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Drama'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Non-Action') ,'Mystery'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Action') ,'Crime'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Action') ,'Western'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Action') ,'Science Fiction'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Action') ,'War'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Action') ,'Adventure'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Action') ,'Suspense'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Classic') ,'Romantic Classic'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Classic') ,'Western Classic'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE = 'Classic') ,'Historical Classic'); --level 3 INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,'Fantasy'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,'Futuristic'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,'Classic Science Fiction'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Suspense') ,'Espionage'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Suspense') ,'Disaster'); --level 4 INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Historical Classic') ,'Edwardian'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Historical Classic') ,'Shakespearian'); --level 5 INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Shakespearian') ,'Black and White Shakespearian'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Shakespearian') ,'Silent Shakespearian'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Shakespearian') ,'Shakespearian Theatre Adaption'); INSERT INTO GENRE(GENRE_ID,PARENT_ID,GENRE) VALUES(GENRE_SEQ.NEXTVAL,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Shakespearian') ,'Modern Shakespearian'); COMMIT; SPOOL OFF;
SPOOL log/SAGA.LOG; INSERT INTO SAGA(SAGA_ID,SAGA) VALUES(SAGA_SEQ.NEXTVAL,'Star Wars'); COMMIT; SPOOL OFF;
SPOOL log/DIRECTOR.LOG; INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Mel Brooks' ,'M','Comedy'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Wolfgang Petersen' ,'M','Action'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Brian G Hutton' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'George Lucas' ,'M','Science Fiction and Special Effects'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Irvin Kershner' ,'M','Science Fiction and Special Effects'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Richard Marquand' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Kevin Reynolds' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Sir Laurence Olivier' ,'M','Shakespearian'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Martin Brest' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Brian De Palma' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Sidney Lumet' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Alan Parker' ,'M','Multiple'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Robert Zemeckis' ,'M',NULL); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Franklin J Schaffner' ,'M','Multiple'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Richard Attenborough' ,'M','Multiple'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'David Attenborough' ,'M','Multiple'); INSERT INTO DIRECTOR(DIRECTOR_ID,NAME,GENDER,TALENT) VALUES(DIRECTOR_SEQ.NEXTVAL,'Oliver Stone' ,'M','Multiple'); COMMIT; SPOOL OFF;
SPOOL log/ACTOR.LOG; INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Mel Brooks' ,'M','Comedian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Gene Wilder' ,'M','Comedian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Peter Boyle' ,'M',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Marty Feldman' ,'M','Comedian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Cloris Leachman' ,'M','Comedian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Teri Garr' ,'F','Comedian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Kenneth Mars' ,'M',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Madelaine Kahn' ,'F',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'George Clooney' ,'M','Action Drama'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Mark Wahlberg' ,'M',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Diane Lane' ,'F',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Clint Eastwood' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Telly Savalas' ,'M','Action Drama'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Don Rickles' ,'M',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Carroll O''Connor' ,'M','Comedian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Donald Sutherland' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Liam Neeson' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Ewan McGregor' ,'M',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Mark Hamill' ,'M','Science Fiction'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Harrison Ford' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Carrie Fisher' ,'F','Science Fiction'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Peter Cushing' ,'M','Horror'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Alec Guinness' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Kevin Costner' ,'M','Action Drama'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Anthony Hopper' ,'M','Odd'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Sir Laurence Olivier','M','Shakespearian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Sir John Gielgud' ,'M','Shakespearian'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Irene Cara' ,'F','Musical'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Brad Davis' ,'M','Drama'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Al Pacino' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Chris O''Donnell' ,'M',NULL); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Michelle Pfeiffer' ,'F','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Tom Hanks' ,'M','Action Drama'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Sally Field' ,'F','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Billy Dee Williams' ,'M','Science Fiction'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'George C Scott' ,'M','Multiple'); INSERT INTO ACTOR(ACTOR_ID,NAME,GENDER,TYPECAST) VALUES(ACTOR_SEQ.NEXTVAL,'Karl Malden' ,'M',NULL); COMMIT; SPOOL OFF;
SPOOL log/AWARD.LOG; INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Best Picture'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Actor in a Leading Role'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Actor in a Supporting Role'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Actress in a Leading Role'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Actress in a Supporting Role'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Animated Feature Film'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Art Direction'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Cinematography'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Costume Design'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Directing'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Documentary Feature'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Documentary Short Subject'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Film Editing'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Foreign Language Film'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Makeup'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Music - Original Score'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Music - Original Song'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Short Film - Animated'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Short Film - Live Action'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Sound Mixing'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Sound Editing'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Visual Effects'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Writing - Screenplay Adaptation'); INSERT INTO AWARD(AWARD_ID,AWARD) VALUES(AWARD_SEQ.NEXTVAL,'Writing - Screenplay Original'); COMMIT; SPOOL OFF;
SPOOL log/MOVIE.LOG; INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Comedy') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Mel Brooks') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Twentieth Century Fox') ,'Young Frankenstein' ,1974 ,'4-SEP-2001' ,9.98 ,801 ,205 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Disaster') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG-13') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Wolfgang Petersen') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Warner Brothers') ,'The Perfect Storm' ,2000 ,'14-NOV-2000' ,17.38 ,3671 ,408 ,3 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='War') ,(SELECT RATING_ID FROM RATING WHERE MPAA='U') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Brian G Hutton') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Metro Goldwyn Mayer') ,'Kelly''s Heroes' ,1970 ,'6-NOV-2001' ,9.94 ,2323 ,87 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,SAGA_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT SAGA_ID FROM SAGA WHERE SAGA='Star Wars') ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='George Lucas') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Lucasfilm Limited') ,'The Phantom Menace' ,2000 ,'28-MAR-2003' ,12.98 ,1032 ,2431 ,3.5 ); INSERT INTO MOVIE(MOVIE_ID,SAGA_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT SAGA_ID FROM SAGA WHERE SAGA='Star Wars') ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='George Lucas') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Lucasfilm Limited') ,'A New Hope' ,1977 ,NULL --,'26-AUG-1977' ,3.35 ,1183 ,500 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,SAGA_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT SAGA_ID FROM SAGA WHERE SAGA='Star Wars') ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Irvin Kershner') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Lucasfilm Limited') ,'The Empire Strikes Back' ,1980 ,NULL --,'26-AUG-1997' ,6.14 ,829 ,198 ,5 ); INSERT INTO MOVIE(MOVIE_ID,SAGA_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT SAGA_ID FROM SAGA WHERE SAGA='Star Wars') ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Science Fiction') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Richard Marquand') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Lucasfilm Limited') ,'Return of the Jedi' ,1983 ,NULL --,'26-AUG-1997' ,4.49 ,754 ,214 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Futuristic') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG-13') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Kevin Reynolds') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Universal Studios') ,'Waterworld' ,1999 ,'31-AUG-1999' ,9.98 ,7133 ,115 ,4 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Comedy') ,(SELECT RATING_ID FROM RATING WHERE MPAA='R') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Mel Brooks') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Warner Brothers') ,'Blazing Saddles' ,1974 ,'2-SEP-2003' ,6.93 ,328 ,181 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Tragedy') ,(SELECT RATING_ID FROM RATING WHERE MPAA='U') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Sir Laurence Olivier') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Pinewood Studios') ,'Richard III' ,1955 ,'25-AUG-1993' ,24.95 ,6666 ,17 ,5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Crime') ,(SELECT RATING_ID FROM RATING WHERE MPAA='R') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Alan Parker') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Columbia/TriStar Studios') ,'Midnight Express' ,1978 ,NULL --,'20-OCT-1998' ,3.50 ,8723 ,114 ,3.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Musical') ,(SELECT RATING_ID FROM RATING WHERE MPAA='R') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Alan Parker') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Warner Brothers') ,'Fame' ,1980 ,NULL --,'5-JAN-1999' ,3.99 ,991 ,40 ,4 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Drama') ,(SELECT RATING_ID FROM RATING WHERE MPAA='R') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Martin Brest') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Universal Studios') ,'Scent of a Woman' ,1993 ,'23-JUL-1993' ,9.98 ,1338 ,71 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Crime') ,(SELECT RATING_ID FROM RATING WHERE MPAA='U') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Brian De Palma') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Universal Studios') ,'Scarface' ,1983 ,NULL --,'15-MAR-1988' ,3.44 ,1541 ,503 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Crime') ,(SELECT RATING_ID FROM RATING WHERE MPAA='R') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Sidney Lumet') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Paramount Pictures') ,'Serpico' ,1973 ,'29-JUL-2003' ,9.95 ,1506 ,34 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='Drama') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG-13') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Robert Zemeckis') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Paramount Pictures') ,'Forrest Gump' ,1994 ,'10-SEP-1996' ,9.95 ,533 ,394 ,4.5 ); INSERT INTO MOVIE(MOVIE_ID,GENRE_ID,RATING_ID,DIRECTOR_ID,STUDIO_ID,TITLE,YEAR,RELEASE,LIST_PRICE,RANK,REVIEWS,REVIEW_RANK) VALUES( MOVIE_SEQ.NEXTVAL ,(SELECT GENRE_ID FROM GENRE WHERE GENRE='War') ,(SELECT RATING_ID FROM RATING WHERE MPAA='PG') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Franklin J Schaffner') ,(SELECT STUDIO_ID FROM STUDIO WHERE STUDIO='Twentieth Century Fox') ,'Patton' ,1970 ,'21-MAY-2002' ,9.98 ,872 ,150 ,4.5 ); COMMIT; SPOOL OFF;
SPOOL log/PART.LOG; INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Mel Brooks'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Gene Wilder'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Peter Boyle'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Marty Feldman'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Cloris Leachman'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Teri Garr'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Kenneth Mars'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Young Frankenstein') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Madelaine Kahn'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Perfect Storm') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='George Clooney'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Perfect Storm') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Mark Wahlberg'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Perfect Storm') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Diane Lane'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Kelly''s Heroes') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Clint Eastwood'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Kelly''s Heroes') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Telly Savalas'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Kelly''s Heroes') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Donald Sutherland'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Kelly''s Heroes') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Don Rickles'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Kelly''s Heroes') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Carroll O''Connor'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Phantom Menace') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Liam Neeson'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Phantom Menace') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Ewan McGregor'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Alec Guinness'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Mark Hamill'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Harrison Ford'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Carrie Fisher'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Peter Cushing'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Empire Strikes Back') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Mark Hamill'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Empire Strikes Back') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Harrison Ford'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Empire Strikes Back') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Carrie Fisher'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Empire Strikes Back') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Billy Dee Williams'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Return of the Jedi') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Mark Hamill'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Return of the Jedi') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Harrison Ford'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Return of the Jedi') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Carrie Fisher'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Return of the Jedi') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Billy Dee Williams'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Waterworld') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Kevin Costner'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Richard III') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Sir Laurence Olivier'),'Y','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Richard III') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Sir John Gielgud'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Midnight Express') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Brad Davis'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Fame') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Irene Cara'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Scent of a Woman') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Al Pacino'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Scent of a Woman') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Chris O''Donnell'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Scarface') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Al Pacino'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Scarface') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Michelle Pfeiffer'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Forest Gump') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Tom Hanks'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Forest Gump') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Sally Field'),'N','Y'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='George C Scott'),'Y','N'); INSERT INTO PART(MOVIE_ID,ACTOR_ID,LEAD_ROLE,SUPPORTING_ROLE) VALUES( (SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Karl Malden'),'N','Y'); COMMIT; SPOOL OFF;
SPOOL log/RECOGNITION.LOG; INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Best Picture') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Forest Gump') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID,ACTOR_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Actor in a Leading Role') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Forest Gump') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Tom Hanks') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Art Direction') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Costume Design') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Film Editing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Music - Original Score') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Sound Editing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Visual Effects') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='A New Hope') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Sound Editing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Empire Strikes Back') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Visual Effects') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='The Empire Strikes Back') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID,ACTOR_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Actor in a Leading Role') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Scent of a Woman') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='Al Pacino') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID,ACTOR_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Actor in a Leading Role') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ,(SELECT ACTOR_ID FROM ACTOR WHERE NAME='George C Scott') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Art Direction') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Cinematography') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID,DIRECTOR_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Directing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ,(SELECT DIRECTOR_ID FROM DIRECTOR WHERE NAME='Franklin J Schaffner') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Film Editing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Music - Original Score') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Best Picture') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Sound Mixing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Sound Editing') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Visual Effects') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); INSERT INTO RECOGNITION(AWARD_ID,MOVIE_ID) VALUES( (SELECT AWARD_ID FROM AWARD WHERE AWARD='Writing - Screenplay Original') ,(SELECT MOVIE_ID FROM MOVIE WHERE TITLE='Patton') ); COMMIT; SPOOL OFF;
SPOOL log/DIMENSIONS.LOG;
--region
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'North America');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Europe');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Central America');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'South America');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Oceania');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Africa');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Asia');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Australasia');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Middle East');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'United States Territories');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Southeast Asia');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'East Asia');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Caribbean');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Pacific Islands');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Former Soviet Union');
INSERT INTO REGION(REGION_ID,REGION) VALUES(REGION_SEQ.NEXTVAL,'Indian Subcontinent');
COMMIT;
--country
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='North America'),'United States');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='North America'),'Canada');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'United Kingdom');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'France');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'Czech Republic');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'Germany');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'Netherlands');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'Spain');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Europe'),'Sweden');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='South America'),'Argentina');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='South America'),'Brazil');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='South America'),'Chile');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='South America'),'Colombia');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='South America'),'Peru');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='South America'),'Venezuela');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Southeast Asia'),'Singapore');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Southeast Asia'),'South Korea');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Southeast Asia'),'Taiwan');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Middle East'),'Israel');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Middle East'),'Kuwait');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Middle East'),'Qatar');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Middle East'),'United Arab Emirates');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Indian Subcontinent'),'India');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Australasia'),'Australia');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Australasia'),'New Zealand');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Oceania'),'Bermuda');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Southeast Asia'),'Hong Kong');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='East Asia'),'Mongolia');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Southeast Asia'),'North Korea');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Central America'),'Mexico');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Asia'),'China');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Asia'),'Japan');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Africa'),'South Africa');
INSERT INTO COUNTRY(COUNTRY_ID,REGION_ID,COUNTRY) VALUES(COUNTRY_SEQ.NEXTVAL,(SELECT REGION_ID FROM REGION WHERE REGION='Middle East'),'Egypt');
COMMIT;
--format -- REGION will become a vcollection and validated against the REGION table
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'VHS','NTSC', REGIONSCOLLECTION('North America', 'Japan'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'VHS','PAL',REGIONSCOLLECTION('Europe, Australia', 'China', 'Middle East'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'VHS','SECAM',REGIONSCOLLECTION('France'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','1',REGIONSCOLLECTION('United States', 'United States Territories', 'Canada', 'Bermuda'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','2',REGIONSCOLLECTION('Japan', 'Europe', 'South Africa', 'Middle East', 'Egypt'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','3',REGIONSCOLLECTION('Southeast Asia', 'East Asia', 'Hong Kong'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','4',REGIONSCOLLECTION('Australia', 'New Zealand', 'Pacific Islands', 'Mexico', 'Central America', 'South America', 'Caribbean'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','5',REGIONSCOLLECTION('Former Soviet Union', 'Indian Subcontinent', 'Africa', 'North Korea', 'Mongolia'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','6',REGIONSCOLLECTION('China'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,EDITION,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','1','Widescreen',REGIONSCOLLECTION('United States', 'United States Territories', 'Canada', 'Bermuda'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,EDITION,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','2','Widescreen',REGIONSCOLLECTION('Japan', 'Europe', 'South Africa', 'Middle East'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,EDITION,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','3','Widescreen',REGIONSCOLLECTION('Southeast Asia', 'East Asia', 'Hong Kong'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,EDITION,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','4','Widescreen',REGIONSCOLLECTION('Australia', 'New Zealand', 'Pacific Islands', 'Mexico', 'Central America', 'South America', 'Caribbean'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,EDITION,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','5','Widescreen',REGIONSCOLLECTION('Former Soviet Union', 'Indian subcontinent', 'Africa', 'North Korea', 'Mongolia'));
INSERT INTO FORMAT(FORMAT_ID,TYPE,ENCODING,EDITION,REGIONS) VALUES(FORMAT_SEQ.NEXTVAL,'DVD','6','Widescreen',REGIONSCOLLECTION('China'));
COMMIT;
--source (of sale)
INSERT INTO SOURCE(SOURCE_ID,SOURCE) VALUES(SOURCE_SEQ.NEXTVAL,'Rental');
INSERT INTO SOURCE(SOURCE_ID,SOURCE) VALUES(SOURCE_SEQ.NEXTVAL,'Retail');
INSERT INTO SOURCE(SOURCE_ID,SOURCE) VALUES(SOURCE_SEQ.NEXTVAL,'Online Retail');
INSERT INTO SOURCE(SOURCE_ID,SOURCE) VALUES(SOURCE_SEQ.NEXTVAL,'Mail Order Retail');
INSERT INTO SOURCE(SOURCE_ID,SOURCE) VALUES(SOURCE_SEQ.NEXTVAL,'Direct');
INSERT INTO SOURCE(SOURCE_ID,SOURCE) VALUES(SOURCE_SEQ.NEXTVAL,'Wholesale Bulk');
COMMIT;
SPOOL OFF;
SPOOL log/FACTS.LOG;
create or replace procedure delay(limit IN integer) as
i integer;
begin
for i in 1..limit loop
null;
end loop;
end;
/
create or replace function rand(n IN NUMBER DEFAULT 1) return integer is
f float default 0;
rand integer default 0;
begin
--gives a random number between 1 and 1000000000
select to_number(to_char(SYSTIMESTAMP,'FF9'))+1 into f from dual;
f := (f*n)/1000000000;
rand := ROUND(f,0);
if rand = 0 then rand := 1; end if;
if rand > n then rand := n; end if;
return ROUND(rand,0);
exception when others then
dbms_output.put_line('FUNC: rand '||SQLERRM(SQLCODE));
end;
/
create or replace function getSource return integer is
i integer;
begin
select max(source_id) into i from source;
return (rand(i));
exception when others then
dbms_output.put_line('FUNC: getSource '||SQLERRM(SQLCODE));
end;
/
create or replace function getFormat return integer is
i integer;
begin
select max(format_id) into i from format;
return (rand(i));
exception when others then
dbms_output.put_line('FUNC: getFormat '||SQLERRM(SQLCODE));
end;
/
create or replace function getCountry return integer is
j integer;
begin
select max(country_id) into j from country;
return (rand(j));
exception when others then
dbms_output.put_line('FUNC: getCountry '||SQLERRM(SQLCODE));
end;
/
create or replace function getRegion (i IN INTEGER) return integer is
j integer;
begin
dbms_output.put_line(to_char(i));
select region_id into j from country where country_id = i;
return (j);
exception when others then
dbms_output.put_line('FUNC: getRegion '||SQLERRM(SQLCODE));
end;
/
create or replace function getMovie return integer is
j integer;
begin
select max(movie_id) into j from movie;
return (rand(j));
exception when others then
dbms_output.put_line('FUNC: getMovie '||SQLERRM(SQLCODE));
end;
/
create or replace function getListPrice (i IN integer) return float is
f float;
begin
select list_price into f from movie where movie_id = i;
return (f);
exception when others then
dbms_output.put_line('FUNC: getListPrice '||SQLERRM(SQLCODE));
end;
/
create or replace procedure factsGenerate as
vsource_id integer;
vformat_id integer;
vcountry_id integer;
vregion_id integer;
vmovie_id integer;
vlist_price float;
vsale_price float;
vsale_date date;
i integer;
j integer default 0;
begin
vsource_id := getSource();
vformat_id := getFormat();
vcountry_id := getCountry();
vregion_id := getRegion(vcountry_id);
vmovie_id := getMovie();
vlist_price := getListPrice(vmovie_id);
vsale_price := vlist_price * (1 - (rand(3)/10));
vsale_date := (SYSDATE + 300) - rand(500);
insert into sale(
sale_id
,movie_id
,source_id
,format_id
,region_id
,country_id
,sale_price
,sale_date)
values(
sale_seq.nextval
,vmovie_id
,vsource_id
,vformat_id
,vregion_id
,vcountry_id
,vsale_price
,vsale_date);
commit;
exception when others then
dbms_output.put_line('PROC: factsGenerate '||SQLERRM(SQLCODE));
rollback;
end;
/
create or replace procedure facts (i IN integer) is
j integer;
begin
for j in 1..i loop
factsGenerate;
delay(1000000);
dbms_output.put_line('Facts: '||to_char(j));
end loop;
end;
/
set serveroutput on;
exec dbms_output.enable(10000000);
set timing on;
truncate table sale;
exec facts(1000);
--fix sale price
declare
cursor cSale is
select s.sale_id as sale_id, m.list_price * (1 - (rand(3)/10)) as sale_price
from movie m join sale s on(s.movie_id = s.movie_id);
begin
for rSale in cSale loop
update sale set sale_price = rSale.sale_price where sale_id = rSale.sale_id;
end loop;
commit;
end;
/
--update sale set sale_price =
--(
-- select a.sale_price
-- from
-- (
-- select s.sale_id as sale_id, m.list_price * (1 - (rand(3)/10)) as sale_price
-- from movie m join sale s on(s.movie_id = s.movie_id)
-- ) a
-- where a.sale_id = sale.sale_id
--);
exec dbms_output.disable;
set serveroutput off;
SPOOL OFF;
Current user metadata views are prefixed as USER_ (current user only), DBA only accessible views prefixed as DBA_, and Views available to the DBA showing all details are named as ALL_. Find all USER_ metadata views using the following query (logged in as SYS or with SELECT_CATALOG_ROLE):
SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'USER_%' ORDER BY 1;
Performance views are generally only available to the SYS user and any user granted the SELECT_CATALOG_ROLE system privilege. Most performance views are named with the prefix V$. For example the performance view V$SQL contains SQL code for parsed SQL commands. Find all performance view names using the following query (logged in as SYS or with SELECT_CATALOG_ROLE):
SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'V$%' ORDER BY 1;
DESC USER_TABLES Name Null? Type ----------------------------- -------- -------------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) DESC USER_TAB_COLUMNS Name Null? Type ----------------------------- -------- -------------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER CHARACTER_SET_NAME VARCHAR2(44) CHAR_COL_DECL_LENGTH NUMBER GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER CHAR_LENGTH NUMBER CHAR_USED VARCHAR2(1) V80_FMT_IMAGE VARCHAR2(3) DATA_UPGRADED VARCHAR2(3) DESC USER_UNUSED_COL_TABS Name Null? Type ----------------------------- -------- -------------------- TABLE_NAME NOT NULL VARCHAR2(30) COUNT NUMBER
DESC USER_TYPES Name Null? Type ----------------------------- -------- -------------------- TYPE_NAME NOT NULL VARCHAR2(30) TYPE_OID NOT NULL RAW(16) TYPECODE VARCHAR2(30) ATTRIBUTES NUMBER METHODS NUMBER PREDEFINED VARCHAR2(3) INCOMPLETE VARCHAR2(3) FINAL VARCHAR2(3) INSTANTIABLE VARCHAR2(3) SUPERTYPE_OWNER VARCHAR2(30) SUPERTYPE_NAME VARCHAR2(30) LOCAL_ATTRIBUTES NUMBER LOCAL_METHODS NUMBER TYPEID RAW(16) DESC USER_NESTED_TABLES Name Null? Type ----------------------------- -------- -------------------- TABLE_NAME VARCHAR2(30) TABLE_TYPE_OWNER VARCHAR2(30) TABLE_TYPE_NAME VARCHAR2(30) PARENT_TABLE_NAME VARCHAR2(30) PARENT_TABLE_COLUMN VARCHAR2(4000) STORAGE_SPEC VARCHAR2(30) RETURN_TYPE VARCHAR2(20) ELEMENT_SUBSTITUTABLE VARCHAR2(25) DESC USER_VARRAYS Name Null? Type ----------------------------- -------- -------------------- PARENT_TABLE_NAME VARCHAR2(30) PARENT_TABLE_COLUMN VARCHAR2(4000) TYPE_OWNER VARCHAR2(30) TYPE_NAME VARCHAR2(30) LOB_NAME VARCHAR2(30) STORAGE_SPEC VARCHAR2(30) RETURN_TYPE VARCHAR2(20) ELEMENT_SUBSTITUTABLE VARCHAR2(25)
DESC USER_CONSTRAINTS Name Null? Type ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14) DESC USER_CONS_COLUMNS Name Null? Type ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER
DESC USER_INDEXES Name Null? Type ----------------------------- -------- -------------------- INDEX_NAME NOT NULL VARCHAR2(30) INDEX_TYPE VARCHAR2(27) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) UNIQUENESS VARCHAR2(9) COMPRESSION VARCHAR2(8) PREFIX_LENGTH NUMBER TABLESPACE_NAME VARCHAR2(30) INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER PCT_THRESHOLD NUMBER INCLUDE_COLUMN NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER PCT_FREE NUMBER LOGGING VARCHAR2(3) BLEVEL NUMBER LEAF_BLOCKS NUMBER DISTINCT_KEYS NUMBER AVG_LEAF_BLOCKS_PER_KEY NUMBER AVG_DATA_BLOCKS_PER_KEY NUMBER CLUSTERING_FACTOR NUMBER STATUS VARCHAR2(8) NUM_ROWS NUMBER SAMPLE_SIZE NUMBER LAST_ANALYZED DATE DEGREE VARCHAR2(40) INSTANCES VARCHAR2(40) PARTITIONED VARCHAR2(3) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) BUFFER_POOL VARCHAR2(7) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) PCT_DIRECT_ACCESS NUMBER ITYP_OWNER VARCHAR2(30) ITYP_NAME VARCHAR2(30) PARAMETERS VARCHAR2(1000) GLOBAL_STATS VARCHAR2(3) DOMIDX_STATUS VARCHAR2(12) DOMIDX_OPSTATUS VARCHAR2(6) FUNCIDX_STATUS VARCHAR2(8) JOIN_INDEX VARCHAR2(3) DESC USER_IND_COLUMNS Name Null? Type ----------------------------- -------- -------------------- INDEX_NAME VARCHAR2(30) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) COLUMN_POSITION NUMBER COLUMN_LENGTH NUMBER CHAR_LENGTH NUMBER DESCEND VARCHAR2(4) DESC USER_IND_EXPRESSIONS Name Null? Type ----------------------------- -------- -------------------- INDEX_NAME VARCHAR2(30) TABLE_NAME VARCHAR2(30) COLUMN_EXPRESSION LONG COLUMN_POSITION NUMBER
DESC USER_VIEWS Name Null? Type ----------------------------- -------- -------------------- VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(30) VIEW_TYPE VARCHAR2(30) SUPERVIEW_NAME VARCHAR2(30) DESC USER_UPDATABLE_COLUMNS Name Null? Type ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) UPDATABLE VARCHAR2(3) INSERTABLE VARCHAR2(3) DELETABLE VARCHAR2(3)
DESC USER_SEQUENCES Name Null? Type ----------------------------- -------- -------------------- SEQUENCE_NAME NOT NULL VARCHAR2(30) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER DESC USER_SYNONYMS Name Null? Type ----------------------------- -------- -------------------- SYNONYM_NAME NOT NULL VARCHAR2(30) TABLE_OWNER VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) DB_LINK VARCHAR2(128)
DESC USER_USERS Name Null? Type ----------------------------- -------- -------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000)
DESC USER_ROLE_PRIVS Name Null? Type ----------------------------- -------- -------------------- USERNAME VARCHAR2(30) GRANTED_ROLE VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE VARCHAR2(3) OS_GRANTED VARCHAR2(3) DESC ROLE_ROLE_PRIVS Name Null? Type ----------------------------- -------- -------------------- ROLE NOT NULL VARCHAR2(30) GRANTED_ROLE NOT NULL VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DESC ROLE_TAB_PRIVS Name Null? Type ----------------------------- -------- -------------------- ROLE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) DESC ROLE_SYS_PRIVS Name Null? Type ----------------------------- -------- -------------------- ROLE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3)
DESC USER_TAB_PRIVS Name Null? Type ----------------------------- -------- -------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) HIERARCHY VARCHAR2(3) DESC USER_TAB_PRIVS_MADE Name Null? Type ----------------------------- -------- -------------------- GRANTEE NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) HIERARCHY VARCHAR2(3) DESC USER_TAB_PRIVS_RECD Name Null? Type ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) HIERARCHY VARCHAR2(3) DESC USER_COL_PRIVS Name Null? Type ----------------------------- -------- -------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) DESC USER_COL_PRIVS_MADE Name Null? Type ----------------------------- -------- -------------------- GRANTEE NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) DESC USER_COL_PRIVS_RECD Name Null? Type ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) DESC USER_SYS_PRIVS Name Null? Type ----------------------------- -------- -------------------- USERNAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3)
SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'V$%' ORDER B TABLE_NAME ------------------------------ V$ACCESS V$ACTIVE_INSTANCES V$ACTIVE_SESS_POOL_MTH V$AQ V$AQ1 V$ARCHIVE V$ARCHIVED_LOG V$ARCHIVE_DEST V$ARCHIVE_DEST_STATUS V$ARCHIVE_GAP V$ARCHIVE_PROCESSES V$AW_CALC V$AW_OLAP V$AW_SESSION_INFO V$BACKUP V$BACKUP_ASYNC_IO V$BACKUP_CORRUPTION V$BACKUP_DATAFILE V$BACKUP_DEVICE V$BACKUP_PIECE V$BACKUP_REDOLOG V$BACKUP_SET V$BACKUP_SPFILE V$BACKUP_SYNC_IO V$BGPROCESS V$BH V$BSP V$BUFFER_POOL V$BUFFER_POOL_STATISTICS V$CIRCUIT V$CLASS_CACHE_TRANSFER V$CLASS_PING V$COMPATIBILITY V$COMPATSEG V$CONTEXT V$CONTROLFILE V$CONTROLFILE_RECORD_SECTION V$COPY_CORRUPTION V$CR_BLOCK_SERVER V$DATABASE V$DATABASE_BLOCK_CORRUPTION V$DATABASE_INCARNATION V$DATAFILE V$DATAFILE_COPY V$DATAFILE_HEADER V$DATAGUARD_STATUS V$DBFILE V$DBLINK V$DB_CACHE_ADVICE V$DB_OBJECT_CACHE V$DB_PIPES V$DELETED_OBJECT V$DISPATCHER V$DISPATCHER_RATE V$DLM_ALL_LOCKS V$DLM_CONVERT_LOCAL V$DLM_CONVERT_REMOTE V$DLM_LATCH V$DLM_LOCKS V$DLM_MISC V$DLM_RESS V$DLM_TRAFFIC_CONTROLLER V$ENABLEDPRIVS V$ENQUEUE_LOCK V$ENQUEUE_STAT V$EVENT_NAME V$EXECUTION V$FAST_START_SERVERS V$FAST_START_TRANSACTIONS V$FILESTAT V$FILESTATXS V$FILE_CACHE_TRANSFER V$FILE_PING V$FIXED_TABLE V$FIXED_VIEW_DEFINITION V$GCSHVMASTER_INFO V$GCSPFMASTER_INFO V$GC_ELEMENT V$GC_ELEMENTS_WITH_COLLISIONS V$GES_BLOCKING_ENQUEUE V$GES_ENQUEUE V$GLOBALCONTEXT V$GLOBAL_BLOCKED_LOCKS V$GLOBAL_TRANSACTION V$HS_AGENT V$HS_PARAMETER V$HS_SESSION V$HVMASTER_INFO V$INDEXED_FIXED_COLUMN V$INSTANCE V$INSTANCE_RECOVERY V$LATCH V$LATCHHOLDER V$LATCHNAME V$LATCH_CHILDREN V$LATCH_MISSES V$LATCH_PARENT V$LIBRARYCACHE V$LIBRARY_CACHE_MEMORY V$LICENSE V$LOADISTAT V$LOADPSTAT V$LOCK V$LOCKED_OBJECT V$LOCKS_WITH_COLLISIONS V$LOCK_ACTIVITY V$LOCK_ELEMENT V$LOG V$LOGFILE V$LOGHIST V$LOGMNR_CALLBACK V$LOGMNR_CONTENTS V$LOGMNR_DICTIONARY V$LOGMNR_LOGFILE V$LOGMNR_LOGS V$LOGMNR_PARAMETERS V$LOGMNR_PROCESS V$LOGMNR_REGION V$LOGMNR_SESSION V$LOGMNR_STATS V$LOGMNR_TRANSACTION V$LOGSTDBY V$LOGSTDBY_STATS V$LOG_HISTORY V$MANAGED_STANDBY V$MAP_COMP_LIST V$MAP_ELEMENT V$MAP_EXT_ELEMENT V$MAP_FILE V$MAP_FILE_EXTENT V$MAP_FILE_IO_STACK V$MAP_LIBRARY V$MAP_SUBELEMENT V$MAX_ACTIVE_SESS_TARGET_MTH V$MLS_PARAMETERS V$MTS V$MTTR_TARGET_ADVICE V$MVREFRESH V$MYSTAT V$NLS_PARAMETERS V$NLS_VALID_VALUES V$OBJECT_DEPENDENCY V$OBSOLETE_PARAMETER V$OFFLINE_RANGE V$OPEN_CURSOR V$OPTION V$PARALLEL_DEGREE_LIMIT_MTH V$PARAMETER V$PARAMETER2 V$PGASTAT V$PGA_TARGET_ADVICE V$PGA_TARGET_ADVICE_HISTOGRAM V$PQ_SESSTAT V$PQ_SLAVE V$PQ_SYSSTAT V$PQ_TQSTAT V$PROCESS V$PROXY_ARCHIVEDLOG V$PROXY_DATAFILE V$PWFILE_USERS V$PX_PROCESS V$PX_PROCESS_SYSSTAT V$PX_SESSION V$PX_SESSTAT V$QUEUE V$QUEUEING_MTH V$RECOVERY_FILE_STATUS V$RECOVERY_LOG V$RECOVERY_PROGRESS V$RECOVERY_STATUS V$RECOVER_FILE V$REPLPROP V$REPLQUEUE V$REQDIST V$RESERVED_WORDS V$RESOURCE V$RESOURCE_LIMIT V$RESUMABLE V$RMAN_CONFIGURATION V$ROLLNAME V$ROLLSTAT V$ROWCACHE V$ROWCACHE_PARENT V$ROWCACHE_SUBORDINATE V$RSRC_CONSUMER_GROUP V$RSRC_CONSUMER_GROUP_CPU_MTH V$RSRC_PLAN V$RSRC_PLAN_CPU_MTH V$SEGMENT_STATISTICS V$SEGSTAT V$SEGSTAT_NAME V$SESSION V$SESSION_CONNECT_INFO V$SESSION_CURSOR_CACHE V$SESSION_EVENT V$SESSION_LONGOPS V$SESSION_OBJECT_CACHE V$SESSION_WAIT V$SESSTAT V$SESS_IO V$SGA V$SGASTAT V$SGA_CURRENT_RESIZE_OPS V$SGA_DYNAMIC_COMPONENTS V$SGA_DYNAMIC_FREE_MEMORY V$SGA_RESIZE_OPS V$SHARED_POOL_ADVICE V$SHARED_POOL_RESERVED V$SHARED_SERVER V$SHARED_SERVER_MONITOR V$SORT_SEGMENT V$SORT_USAGE V$SPPARAMETER V$SQL V$SQLAREA V$SQLTEXT V$SQLTEXT_WITH_NEWLINES V$SQLXS V$SQL_BIND_DATA V$SQL_BIND_METADATA V$SQL_CURSOR V$SQL_PLAN V$SQL_PLAN_STATISTICS V$SQL_PLAN_STATISTICS_ALL V$SQL_REDIRECTION V$SQL_SHARED_CURSOR V$SQL_SHARED_MEMORY V$SQL_WORKAREA V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA_HISTOGRAM V$STANDBY_LOG V$STATISTICS_LEVEL V$STATNAME V$STREAMS_APPLY_COORDINATOR V$STREAMS_APPLY_READER V$STREAMS_APPLY_SERVER V$STREAMS_CAPTURE V$SUBCACHE V$SYSSTAT V$SYSTEM_CURSOR_CACHE V$SYSTEM_EVENT V$SYSTEM_PARAMETER V$SYSTEM_PARAMETER2 V$TABLESPACE V$TEMPFILE V$TEMPORARY_LOBS V$TEMPSEG_USAGE V$TEMPSTAT V$TEMPSTATXS V$TEMP_CACHE_TRANSFER V$TEMP_EXTENT_MAP V$TEMP_EXTENT_POOL V$TEMP_PING V$TEMP_SPACE_HEADER V$THREAD V$TIMER V$TIMEZONE_NAMES V$TRANSACTION V$TRANSACTION_ENQUEUE V$TYPE_SIZE V$UNDOSTAT V$VERSION V$VPD_POLICY V$WAITSTAT V$_LOCK 265 rows selected.
SQL> SELECT TO_CHAR(100000, '$9,999,990.99') FROM DUAL;
TO_CHAR(100000,'$9,999,990.99'
-----------------------------------------------------------------
$100,000.00
SQL> SELECT TO_CHAR(100000, '0999999') FROM DUAL;
TO_CHAR(100000,'0999999')
-----------------------------------------------------------------
0100000
SQL> SELECT TO_CHAR(100000, '9999990') FROM DUAL;
TO_CHAR(100000,'9999990')
-----------------------------------------------------------------
100000
SQL> SELECT TO_CHAR(001000, 'B999999') FROM DUAL;
TO_CHAR(1000,'B999999')
-----------------------------------------------------------------
1000
SQL> SELECT TO_CHAR(100000, '9.9EEEE') FROM DUAL;
TO_CHAR(100000,'9.9EEEE')
-----------------------------------------------------------------
1.0E+05
SQL> SELECT TO_CHAR(1000, 'FM999999') FROM DUAL;
TO_CHAR(1000,'FM999999')
-----------------------------------------------------------------
1000
SQL> SELECT TO_CHAR(2004, 'RN') FROM DUAL;
TO_CHAR(2004,'RN')
-----------------------------------------------------------------
MMIV
SQL> SELECT TO_CHAR(2004, 'rn') FROM DUAL;
TO_CHAR(2004,'RN')
-----------------------------------------------------------------
mmiv
SQL> SELECT TO_CHAR(-101,'999MI') FROM DUAL;
TO_CHAR(-101,'999MI')
-----------------------------------------------------------------
101-
SQL> SELECT TO_CHAR(-101,'999PR') FROM DUAL;
TO_CHAR(-101,'999PR')
-----------------------------------------------------------------
<101>
SQL> SELECT TO_CHAR(100000, 'S999999') FROM DUAL;
TO_CHAR(100000,'S999999')
-----------------------------------------------------------------
+100000
SQL> SELECT TO_CHAR(100000, '999999S') FROM DUAL;
TO_CHAR(100000,'999999S')
-----------------------------------------------------------------
100000+
SQL>
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'HH:MI:SS') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'HH:MI:SS --------------------------------------------------------------------------- 12:02:35 SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'HH:MI:SSSSS') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'HH:MI:SS --------------------------------------------------------------------------- 12:02:43355 SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'HH12:MI:SS.FF1') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'HH12:MI: --------------------------------------------------------------------------- 12:02:35.3 SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'HH24:MI: --------------------------------------------------------------------------- 12:02:36.095000000 SQL> SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL; TO_CHAR(SYSDATE,'D') --------------------------------------------------------------------------- 2 SQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; TO_CHAR(SYSDATE,'DAY') --------------------------------------------------------------------------- MONDAY SQL> SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL; TO_CHAR(SYSDATE,'DD') --------------------------------------------------------------------------- 06 SQL> SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL; TO_CHAR(SYSDATE,'DDD') --------------------------------------------------------------------------- 250 SQL> SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; TO_CHAR(SYSDATE,'DY') --------------------------------------------------------------------------- MON SQL> SELECT TO_CHAR(SYSDATE, 'IW') FROM DUAL; TO_CHAR(SYSDATE,'IW') --------------------------------------------------------------------------- 37 SQL> SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL; TO_CHAR(SYSDATE,'WW') --------------------------------------------------------------------------- 36 SQL> SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL; TO_CHAR(SYSDATE,'W') --------------------------------------------------------------------------- 1 SQL> SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL; TO_CHAR(SYSDATE,'MM') --------------------------------------------------------------------------- 09 SQL> SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL; TO_CHAR(SYSDATE,'MON') --------------------------------------------------------------------------- SEP SQL> SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL; TO_CHAR(SYSDATE,'MONTH') --------------------------------------------------------------------------- SEPTEMBER SQL> SELECT TO_CHAR(SYSDATE, 'RM') FROM DUAL; TO_CHAR(SYSDATE,'RM') --------------------------------------------------------------------------- IX SQL> SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; TO_CHAR(SYSDATE,'Q') --------------------------------------------------------------------------- 3 SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL; TO_CHAR(SYSDATE,'J') --------------------------------------------------------------------------- 2453255 SQL> SELECT TO_CHAR(SYSDATE, 'Y') FROM DUAL; TO_CHAR(SYSDATE,'Y') --------------------------------------------------------------------------- 4 SQL> SELECT TO_CHAR(SYSDATE, 'YYY') FROM DUAL; TO_CHAR(SYSDATE,'YYY') --------------------------------------------------------------------------- 004 SQL> SELECT TO_CHAR(SYSDATE, 'YEAR') FROM DUAL; TO_CHAR(SYSDATE,'YEAR') --------------------------------------------------------------------------- TWO THOUSAND FOUR SQL> SELECT TO_CHAR(SYSDATE, 'SYEAR') FROM DUAL; TO_CHAR(SYSDATE,'SYEAR') --------------------------------------------------------------------------- TWO THOUSAND FOUR SQL> SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL; TO_CHAR(SYSDATE,'YYYY') --------------------------------------------------------------------------- 2004 SQL> SELECT TO_CHAR(SYSDATE, 'SYYYY') FROM DUAL; TO_CHAR(SYSDATE,'SYYYY') --------------------------------------------------------------------------- 2004 SQL> SELECT TO_CHAR(SYSDATE, 'YYY') FROM DUAL; TO_CHAR(SYSDATE,'YYY') --------------------------------------------------------------------------- 004 SQL> SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL; TO_CHAR(SYSDATE,'YY') --------------------------------------------------------------------------- 04 SQL> SELECT TO_CHAR(SYSDATE, 'Y') FROM DUAL; TO_CHAR(SYSDATE,'Y') --------------------------------------------------------------------------- 4 SQL> SELECT TO_CHAR(SYSDATE, 'IYYY') FROM DUAL; TO_CHAR(SYSDATE,'IYYY') --------------------------------------------------------------------------- 2004 SQL> SELECT TO_CHAR(SYSDATE, 'IYY') FROM DUAL; TO_CHAR(SYSDATE,'IYY') --------------------------------------------------------------------------- 004 SQL> SELECT TO_CHAR(SYSDATE, 'IY') FROM DUAL; TO_CHAR(SYSDATE,'IY') --------------------------------------------------------------------------- 04 SQL> SELECT TO_CHAR(SYSDATE, 'I') FROM DUAL; TO_CHAR(SYSDATE,'I') --------------------------------------------------------------------------- 4 SQL> SELECT TO_CHAR(SYSDATE, 'RR') FROM DUAL; TO_CHAR(SYSDATE,'RR') --------------------------------------------------------------------------- 04 SQL> SELECT TO_CHAR(SYSDATE, 'RRRR') FROM DUAL; TO_CHAR(SYSDATE,'RRRR') --------------------------------------------------------------------------- 2004 SQL> SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'TZD') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'TZD') --------------------------------------------------------------------------- SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'TZH') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'TZH') --------------------------------------------------------------------------- -07 SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'TZM') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'TZM') --------------------------------------------------------------------------- 00 SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'TZR') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'TZR') --------------------------------------------------------------------------- -07:00
SQL> SELECT EXTRACT(YEAR FROM DATE '2004-03-25') FROM DUAL;
EXTRACT(YEARFROMDATE'2004-03-2
------------------------------
2004
SQL> SELECT EXTRACT(MONTH FROM DATE '2004-03-25') FROM DUAL;
EXTRACT(MONTHFROMDATE'2004-03-
------------------------------
3
SQL> SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
EXTRACT(DAYFROMSYSDATE)
-----------------------
1
SQL> SELECT EXTRACT(HOUR FROM TIME '12:24:01') FROM DUAL;
EXTRACT(HOURFROMTIME'12:24:01'
------------------------------
12
SQL> SELECT EXTRACT(MINUTE FROM LOCALTIMESTAMP) FROM DUAL;
EXTRACT(MINUTEFROMLOCALTIMESTAMP)
---------------------------------
22
SQL> SELECT EXTRACT(SECOND FROM LOCALTIMESTAMP) FROM DUAL;
EXTRACT(SECONDFROMLOCALTIMESTAMP)
---------------------------------
14.208
SQL> SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) FROM DUAL;
EXTRACT(TIMEZONE_HOURFROMCURRENT_TIMESTAMP)
-------------------------------------------
-4
SQL> SELECT EXTRACT(TIMEZONE_MINUTE FROM CURRENT_TIMESTAMP) FROM DUAL;
EXTRACT(TIMEZONE_MINUTEFROMCURRENT_TIMESTAMP)
---------------------------------------------
0
SQL>
SET TERMOUT OFF ECHO OFF FEED OFF TRIMSPOOL ON HEAD OFF PAGES 0; SPOOL C:\TEMP\COUNT.LOG; COLUMN TABLE_NAME FORMAT A16; SELECT 'SELECT '''||TABLE_NAME||','', TRIM(COUNT(*)) FROM '||TABLE_NAME||';' FROM USER_TABLES; SPOOL OFF; SET TERMOUT ON; @@C:\TEMP\COUNT.LOG; SET TERMOUT ON ECHO ON FEED ON TRIMSPOOL OFF HEAD ON PAGES 40;
set wrap off linesize 132 pages 80 column tab format a20 column col format a15 column pos format 990 column typ format a10 column tbs format a25 BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES select t.table_name "Tab" ,c.column_name "Col" ,c.column_id "Pos" ,c.data_type "Typ" ,DECODE(c.nullable,'N','NOT NULL',NULL) "Null" ,t.tablespace_name "Tbs" from user_tables t, user_tab_columns c where t.table_name = c.table_name order by t.table_name, c.column_id;
set wrap off linesize 132 pages 80
column tab format a25
column typ format a5
column ind format a25
column col format a20
column pos format 990
column tbs format a25
BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES
select t.table_name "Tab"
,decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap','FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Typ"
,t.index_name "Ind"
,c.column_name "Col"
,c.column_position "Pos"
,t.tablespace_name "Tbs"
from user_indexes t, user_ind_columns c
where t.table_name = c.table_name
and t.index_name = c.index_name
and t.index_type not in ('IOT - TOP','LOB')
order by t.table_name, t.index_name, c.column_position;
set wrap off linesize 132 pages 80
column tab format a20
column key format a10
column cons format a20
column col format a10
column pos format 990
BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES
select t.table_name "Tab"
,decode(t.constraint_type,'P','Primary','R','Foreign','U','Alternate','Unknown') "Key"
,t.constraint_name "Cons"
,c.column_name "Col"
,c.position "Pos"
from user_constraints t, user_cons_columns c
where t.constraint_type in ('P','R','U')
and t.table_name = c.table_name
and t.constraint_name = c.constraint_name
order by t.table_name, t.constraint_type, c.position;