Movies Schema

Metadata ViewsV$ Performance ViewsData Conversion FormattingSyntax DiagramsUtilities1Z0-007 Summary

Movies Schema ERDs

OLTP Schema ERD

OLTP (Online transaction processing) ERD (Entity relationship diagram)

Data Warehouse Schema ERD

Data Warehouse ERD (Entity relationship diagram)

Movies Schema Creation Script

CREATEMOVIES.SQL

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;

CREATEUSER.SQL

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;

SCHEMAOLTP.SQL


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;

SCHEMADW.SQL


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;

Movies Schema Sequences

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;

Movies Schema Data Generation

STUDIO.SQL

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;

RATING.SQL

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;

GENRE.SQL

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;

SAGA.SQL

SPOOL log/SAGA.LOG;
INSERT INTO SAGA(SAGA_ID,SAGA) VALUES(SAGA_SEQ.NEXTVAL,'Star Wars');
COMMIT;
SPOOL OFF;

DIRECTOR.SQL

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;

ACTOR.SQL

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;

AWARD.SQL

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;

MOVIE.SQL

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;

PART.SQL

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;

RECOGNITION.SQL

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;

DIMENSIONS.SQL

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;

FACTS.SQL

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;

Metadata Views

Movies SchemaV$ Performance ViewsData Conversion FormattingSyntax DiagramsUtilities1Z0-007 Summary

How to Find Metadata and Performance View Names

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;

Tables

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

Datatypes

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)

Constraints

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

Indexes

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

Views

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)

Sequences and Synonyms

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)

Security

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)

Roles

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)

Privileges

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)

V$ Performance Views

Movies SchemaMetadata ViewsData Conversion FormattingSyntax DiagramsUtilities1Z0-007 Summary
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.

Data Conversion Formatting

Movies SchemaMetadata ViewsV$ Performance ViewsSyntax DiagramsUtilities1Z0-007 Summary

Number Format Modeling

Number formats
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> 

Date Format Modeling

Data formats
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

Using The EXTRACT Function

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> 

Syntax Diagrams

Movies SchemaMetadata ViewsV$ Performance ViewsData Conversion FormattingUtilities1Z0-007 Summary

This section contains a small number of sample syntax diagrams from Oracle 9i : SQL Exam Cram 2 (Exam Cram 1Z0-007) (Que's Easy Series).

The SELECT Statement

The Basic SELECT Statement

The basic SELECT statement

The WHERE Clause

The WHERE clause

The ORDER BY Clause

The ORDER BY clause

The Basic GROUP BY Clause

The basic GROUP BY clause

Joins in the SELECT Statement

Joins in the SELECT statement

DML (Data Manipulation Language) Statements

The Basic INSERT Statement

The basic INSERT statement

The Basic UPDATE Statement

The basic UPDATE statement

The DELETE Statement

The DELETE statement

DDL (Data Definition Language) Statements

The Basic CREATE TABLE Statement

The basic CREATE TABLE statement

The Basic CREATE INDEX Statement

The basic CREATE INDEX statement

The CREATE USER Statement

The CREATE USER statement

Utilities

Movies SchemaMetadata ViewsV$ Performance ViewsData Conversion FormattingSyntax Diagrams1Z0-007 Summary

COUNT.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;

showTables.sql

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;

showIndexes.sql

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;

showConstraints.sql

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;