CREATE TABLE customers (
customer_id NUMBER CONSTRAINT custs_pk PRIMARY KEY,
last_name VARCHAR2(30) CONSTRAINT custs_lnm_nn NOT NULL,
first_name VARCHAR2(30) CONSTRAINT custs_fnm_nn NOT NULL,
gender CHAR(1),
dob DATE,
member_dt DATE,
status CHAR(1),
address VARCHAR2(50),
city VARCHAR2(30),
state CHAR(2),
zip VARCHAR2(10),
day_phone VARCHAR2(10),
eve_phone VARCHAR2(10),
comedy CHAR(1),
drama CHAR(1),
mystery CHAR(1),
foreign CHAR(1)
);
INSERT INTO CUSTOMERS VALUES ('230','Andrews','Rebecca','F','12-MAR-69','19-FEB-95','A',
'1456 Sycamore St','Falls Church','VA','22040','7038661521','7053567712','Y','','','');
INSERT INTO CUSTOMERS VALUES ('326','Milton','Henry','M','21-SEP-53','12-MAY-95','A',
'1723 Marshall Lane','Arlington','VA','22019','3019522214','7037213725','Y','Y','','');
INSERT INTO CUSTOMERS VALUES ('327','Jackson','Cindy','F','31-OCT-43','30-AUG-94','A',
'123 Courthouse Road','Fairfax','VA','22102','7034454454','7038547854','Y','','','Y');
INSERT INTO CUSTOMERS VALUES ('329','Jeffrey','Denning','M','15-MAR-64','19-FEB-96','A',
'1721 Whitney Drive','Falls Church','VA','22040','7036547896','7055435712','Y','','','');
INSERT INTO CUSTOMERS VALUES ('330','Mayer','Arthur','M','11-JUN-73','12-MAY-96','A',
'729 Castle Ct','Reston','VA','22019','7038246545','7037213324','','','Y','Y');
INSERT INTO CUSTOMERS VALUES ('331','Baker','Paul','M','19-DEC-69','30-SEP-95','A',
'1423 North 15th Rd','Arlington','MD','22102','7034257894','7038542154','','Y','','');
INSERT INTO CUSTOMERS VALUES ('332','Johnson','Michelle','F','21-MAR-66','20-APR-95','A',
'123 North Main Street Apt. 309','Falls Church','MD','20129','7035643215','7038659856','','','Y','');
COMMIT;
CREATE TABLE movies (
movie_id NUMBER CONSTRAINT movies_pk PRIMARY KEY,
title VARCHAR2(30) CONSTRAINT movies_nn NOT NULL,
status CHAR(1),
director VARCHAR2(30),
actor VARCHAR2(30),
actress VARCHAR2(30),
producer VARCHAR2(30),
company VARCHAR2(30),
start_dt DATE,
end_dt DATE,
rating CHAR(1)
);
INSERT INTO MOVIES VALUES ('231','JFK','A','Oliver Stone','Kevin Kostner','Sissy Spacek',
'Kitman Ho / Oliver Stone','Warner Bros.','01-JAN-91','','3');
INSERT INTO MOVIES VALUES ('238','The Godfather','A','Francis Ford Copola','Marlon Brando',
'Dean Keaton','Albert S. Ruddy','Pramount Pictures','01-JAN-72','','3');
INSERT INTO MOVIES VALUES ('243','Gone with the Wind','A','Victor Fleming','Clark Gable',
'Vivien Leigh','David Selznik','MGM','','','2');
INSERT INTO MOVIES VALUES ('247','Doctor Zhivago','A','David Lean','Omar Sharif',
'Julie Christie','Carlo Ponti','MGM','','','2');
INSERT INTO MOVIES VALUES ('250','The Silence of Lambs','A','Jonathan Demme','Anthony Hopkins',
'Jodie Foster','Keneth Uff / Edward Saxon','Orion Pictures Corporation','01-JAN-90','','2');
INSERT INTO MOVIES VALUES ('261','The Piano','A','Jane Champion','Harvey Keitel',
'Holly Hunter','Jan Chapman','MIRAMAX','01-JAN-92','','3');
INSERT INTO MOVIES VALUES ('265','Cinema Paradiso','A','Giuseppe Tornatore','Philippe Noiret',
'Antonella Attili','Franco Cristaldi','MIRAMAX','01-JAN-91','','3');
INSERT INTO MOVIES VALUES ('271','Amadeus','A','Milos Forman','E. Murray Abrahams',
'Elisabeth Berridge','Saul Zaentz','Saul Zaentz Group','01-JAN-84','','2');
INSERT INTO MOVIES VALUES ('321','Ghandi','A','Richard Attenborough','Ben Kingslay',
'Candice Bergen','Terence A. Clegg','Columbia Pictures','01-JAN-82','','2');
COMMIT;
CREATE TABLE tapes (
tape_id NUMBER CONSTRAINT tapes_pk PRIMARY KEY,
movie_id NUMBER,
status CHAR(1),
CONSTRAINT tapes_movie_id_fk FOREIGN KEY (movie_id)
REFERENCES movies
ON DELETE CASCADE
);
INSERT INTO TAPES VALUES ('235','231','A');
INSERT INTO TAPES VALUES ('236','231','A');
INSERT INTO TAPES VALUES ('239','238','A');
INSERT INTO TAPES VALUES ('234','231','A');
INSERT INTO TAPES VALUES ('240','238','A');
INSERT INTO TAPES VALUES ('241','238','A');
INSERT INTO TAPES VALUES ('244','243','A');
INSERT INTO TAPES VALUES ('245','243','A');
INSERT INTO TAPES VALUES ('248','247','A');
INSERT INTO TAPES VALUES ('249','247','A');
INSERT INTO TAPES VALUES ('251','250','A');
INSERT INTO TAPES VALUES ('252','250','A');
INSERT INTO TAPES VALUES ('253','250','A');
INSERT INTO TAPES VALUES ('262','261','A');
INSERT INTO TAPES VALUES ('263','261','A');
INSERT INTO TAPES VALUES ('266','265','A');
INSERT INTO TAPES VALUES ('267','265','A');
INSERT INTO TAPES VALUES ('272','271','A');
INSERT INTO TAPES VALUES ('273','271','A');
INSERT INTO TAPES VALUES ('274','271','A');
INSERT INTO TAPES VALUES ('276','271','A');
INSERT INTO TAPES VALUES ('322','321','A');
INSERT INTO TAPES VALUES ('323','321','A');
INSERT INTO TAPES VALUES ('324','321','A');
COMMIT;
CREATE TABLE rentals (
customer_id NUMBER,
tape_id NUMBER,
rent_dt DATE,
return_dt DATE,
daily_rate NUMBER(4, 2),
CONSTRAINT rentals_pk PRIMARY KEY (customer_id, tape_id, rent_dt),
CONSTRAINT rentals_cust_id_fk FOREIGN KEY (customer_id)
REFERENCES customers
ON DELETE CASCADE,
CONSTRAINT rentals_tape_id_fk FOREIGN KEY (tape_id)
REFERENCES tapes
ON DELETE CASCADE
);
INSERT INTO RENTALS VALUES ('327','248','10-MAR-96','11-MAR-96','1.99');
INSERT INTO RENTALS VALUES ('329','253','21-MAR-96','23-MAR-96','1.99');
INSERT INTO RENTALS VALUES ('332','266','01-MAR-96','13-MAR-96','.99');
INSERT INTO RENTALS VALUES ('331','266','29-FEB-96','02-MAR-96','1.99');
INSERT INTO RENTALS VALUES ('331','239','11-MAR-96','13-MAR-96','1.99');
INSERT INTO RENTALS VALUES ('230','248','11-MAR-96','13-MAR-96','1.29');
INSERT INTO RENTALS VALUES ('331','262','11-MAR-96','13-MAR-96','.99');
INSERT INTO RENTALS VALUES ('230','235','11-MAR-96','13-MAR-96','1.49');
INSERT INTO RENTALS VALUES ('332','263','11-MAR-96','13-MAR-96','1.99');
INSERT INTO RENTALS VALUES ('332','235','11-MAR-96','13-MAR-96','1.99');
INSERT INTO RENTALS VALUES ('327','245','11-MAR-96','13-MAR-96','.99');
INSERT INTO RENTALS VALUES ('327','276','11-MAR-96','13-MAR-96','.99');
COMMIT;
CREATE TABLE states (
code CHAR(2) CONSTRAINT state_pk PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO STATES VALUES ('VA','Virginia');
INSERT INTO STATES VALUES ('MD','Maryland');
INSERT INTO STATES VALUES ('NY','New York');
INSERT INTO STATES VALUES ('DC','District of Columbia');
INSERT INTO STATES VALUES ('TX','Texas');
INSERT INTO STATES VALUES ('IL','Illinois');
INSERT INTO STATES VALUES ('AZ','Arizona');
INSERT INTO STATES VALUES ('ND','North Dakota');
INSERT INTO STATES VALUES ('SD','South Dakota');
INSERT INTO STATES VALUES ('NM','New Mexico');
INSERT INTO STATES VALUES ('NJ','New Jersey');
INSERT INTO STATES VALUES ('NH','New Hampshire');
INSERT INTO STATES VALUES ('OH','Ohio');
INSERT INTO STATES VALUES ('GA','Georgia');
INSERT INTO STATES VALUES ('SC','South Carolina');
INSERT INTO STATES VALUES ('NC','North Carolina');
INSERT INTO STATES VALUES ('FL','Florida');
INSERT INTO STATES VALUES ('VT','Vermont');
INSERT INTO STATES VALUES ('MA','Massachussets');
INSERT INTO STATES VALUES ('OK','Oklahoma');
INSERT INTO STATES VALUES ('WA','Washington');
INSERT INTO STATES VALUES ('KY','Kentuky');
INSERT INTO STATES VALUES ('WV','West Virgina');
INSERT INTO STATES VALUES ('PA','Pennsylvania');
INSERT INTO STATES VALUES ('TN','Tennessee');
INSERT INTO STATES VALUES ('LO','Louisiana');
INSERT INTO STATES VALUES ('ID','Idaho');
INSERT INTO STATES VALUES ('CA','California');
INSERT INTO STATES VALUES ('UT','Utah');
COMMIT;
