Insert

Description: Examples of INSERT statements.

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;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.