Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: INSERT Jump to:  
Category: >> Oracle PL/SQL >> INSERT Bookmark and Share

<< lastnext >>

Snippet Name: INSERT

Description: Examples of INSERT statements.

Also see:
» INSERT With Returning Clause
» Create Table INSERT (CTAS)
» Inserting into SELECT statement
» INSERT WITH CHECK OPTION
» INSERT FIRST WHEN
» INSERT ALL WHEN
» INSERT ALL
» INSERT WHEN
» INSERT: Using A Record
» INSERT with Select
» INSERT: Multiple Column Table Or View ...
» INSERT: Multiple Column Table Or View ...
» INSERT: Single Column Table Or View
» FORALL Insert

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 04th, 2009

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;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 195 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?