Drop table movie cascade constraint;
Drop table composer cascade constraint;
Drop table director cascade constraint;
Drop table director_movie cascade constraint;
Drop table cast cascade constraint;
Drop table movie_cast cascade constraint;
create table MOVIE(
MOVIE_ID VARCHAR2(20) PRIMARY KEY,
MOVIE_NAME VARCHAR2(25),
GENRE varchar2(15) constraint gen_chk check(genre in
('ACTION','COMEDY','CRIME','MUSICAL','FAMILY','WAR','ANIMATION','ROMANCE','DOCUMENT
ARY')),
STUDIO VARCHAR2(20),
BOXOFFICE_AMOUNT VARCHAR2(15),
RATING VARCHAR2(10)
);
create table composer
(musician_id varchar2(15) constraint mus_pk primary key,
musician_name varchar2(40) not null,
Composed_year number(8),
award_winner char(1) constraint aw_chk check(award_winner in ('Y','N')),
number_of_albums number(6)
);
CREATE TABLE DIRECTOR(
DIRECTOR_ID VARCHAR2(10) PRIMARY KEY,
DIRECTOR_NAME VARCHAR2(20) not null,
MOVIE_RELEASED_DATE DATE not null,
EMAIL_ID VARCHAR2(20)
);
CREATE TABLE DIRECTOR_MOVIE(
DIRECTOR_ID VARCHAR2(20),
MOVIE_ID VARCHAR2(10),
HOURS VARCHAR2(10),
PRIMARY KEY(DIRECTOR_ID,MOVIE_ID),
FOREIGN KEY(DIRECTOR_ID) REFERENCES DIRECTOR (DIRECTOR_ID),
FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE (MOVIE_ID)
);
CREATE TABLE CAST(
ACTOR_ID VARCHAR2(20) PRIMARY KEY,
ACTOR_NAME VARCHAR2(20),
N0_OF_MOVIES VARCHAR2(5),
DOB DATE,
oscar_nominee char(1) constraint os_no_chk check(oscar_nominee in
('Y','N')),
REMUNERATION VARCHAR2(20)
);
CREATE TABLE MOVIE_CAST(
MOVIE_ID VARCHAR2(10),
ACTOR_ID VARCHAR2(10),
BUDGET_AMOUNT VARCHAR2(15),
musician_id varchar2(15),
PRIMARY KEY(MOVIE_ID,ACTOR_ID),
FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),
FOREIGN KEY(musician_id) REFERENCES composer (musician_id)
);
INSERT INTO MOVIE VALUES('US-925','Summer Day','FAMILY','Fox
Studio','500000','**');
INSERT INTO MOVIE VALUES('US-960','Washington Time','ACTION','Universal
Studio','700000','**');
INSERT INTO MOVIE VALUES('US-000','Freedom Beyond','ACTION','Fox
Studio','150000','***');
INSERT INTO MOVIE VALUES('US-154','Greens Battle','WAR','Warner
Bros','300000','****');
INSERT INTO MOVIE VALUES('UK-115','Jungle Tales','ANIMATION','Walt
Disney','600000','***');
INSERT INTO MOVIE VALUES('US-200','Hobbit',null,'Universal Studio','800000','***');
INSERT INTO MOVIE VALUES('US-220','Interstellar',null,null,null,null);
INSERT INTO MOVIE VALUES('US-420','Jurassic Park','ACTION','Fox
Studio','850000','*****');
insert into composer values('MC0054','Yanni M',1970,'Y',6);
insert into composer values('MC0060','John Duke',1987,'Y',12);
insert into composer values('MC0071','Tim Rudy',1998,'N',3);
insert into composer values('MC0078','Faith Shepherd',1965,'Y',25);
insert into composer values('MC0073','Timothy Cain',2001,'Y',6);
insert into composer values('MC0080','Rhonda Adler',2000,'N',5);
insert into composer values('MC0015','Christopher',2008,null,null);
insert into composer values('MC0022','Chris Martin',2014,'Y',10);
INSERT INTO DIRECTOR VALUES('DR0010','John','10-JUN-97','[email protected]');
INSERT INTO DIRECTOR VALUES('DR0011','Ralph','05-JAN-03','
[email protected]');
INSERT INTO DIRECTOR VALUES('DR0012','McGraw','03-AUG-00','
[email protected]');
INSERT INTO DIRECTOR VALUES('DR0014','Rupert','06-DEC-02','
[email protected]');
INSERT INTO DIRECTOR VALUES('DR0015','William','15-AUG-00','
[email protected]');
INSERT INTO DIRECTOR_MOVIE VALUES('DR0015','US-925','125');
INSERT INTO DIRECTOR_MOVIE VALUES('DR0010','US-925','100');
INSERT INTO DIRECTOR_MOVIE VALUES('DR0010','US-000','130.5');
INSERT INTO DIRECTOR_MOVIE VALUES('DR0011','US-000','150.0');
INSERT INTO DIRECTOR_MOVIE VALUES('DR0010','UK-115','100');
INSERT INTO DIRECTOR_MOVIE VALUES('DR0012','UK-115','125.5');
INSERT INTO CAST VALUES('CST091','Brad Grant','18','16-JUN-77','Y','50000');
INSERT INTO CAST VALUES('CST075','Christina Lee','05','02-MAR-74','N','70000');
INSERT INTO CAST VALUES('CST085','Shawn Martin','02','25-JUN-60','Y','80000');
INSERT INTO CAST VALUES('CST081','Jennifer Glo','01','12-JUN-75','N','100000');
INSERT INTO CAST VALUES('CST080','Elaine Roots',null,'06-APR-72','Y','50000');
INSERT INTO CAST VALUES('CST086','Tom Cruise',null,'10-JUN-80',null,null);
INSERT INTO CAST VALUES('CST021','Lukie Martin','05','08-MAR-74',null,'100000');
INSERT INTO MOVIE_CAST VALUES('US-925','CST075','100000','MC0054');
INSERT INTO MOVIE_CAST VALUES('US-925','CST085','500000','MC0060');
INSERT INTO MOVIE_CAST VALUES('US-960','CST091','400000','MC0071');
INSERT INTO MOVIE_CAST VALUES('US-154','CST081','800000','MC0071');
INSERT INTO MOVIE_CAST VALUES('US-154','CST080','500000','MC0060');
INSERT INTO MOVIE_CAST VALUES('US-960','CST075','100000',null);
INSERT INTO MOVIE_CAST VALUES('US-925','CST081','800000','MC0078');
INSERT INTO MOVIE_CAST VALUES('US-960','CST080','500000',null);
INSERT INTO MOVIE_CAST VALUES('US-154','CST091','400000','MC0080');
