CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
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 ;
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
25 users online
© 2009 psoug.org