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;