insert_data.txt

orale培训教材包括了所有的sql说明和实例

------------------------ PERSON ------------------------
DROP TABLE plsql101_person;
CREATE TABLE plsql101_person (
     person_code VARCHAR2(3),
     first_name  VARCHAR2(15),
     last_name   VARCHAR2(20),
     hire_date   DATE
     )
;

INSERT INTO plsql101_person VALUES
     ('CA', 'Charlene', 'Atlas', '01-2月-02');
INSERT INTO plsql101_person VALUES
     ('GA', 'Gary', 'Anderson', '15-2月-02');
INSERT INTO plsql101_person VALUES
     ('BB', 'Bobby', 'Barkenhagen', '28-2月-02');
INSERT INTO plsql101_person VALUES
     ('LB', 'Laren', 'Baxter', '01-5月-02');


------------------------ PRODUCT ------------------------

DROP TABLE plsql101_product;
CREATE TABLE plsql101_product (
     product_name     VARCHAR2(25),
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE
     )
;

INSERT INTO plsql101_product VALUES
     ('Small Widget', 99, 1, '15-1月-03');
INSERT INTO plsql101_product VALUES
     ('Medium Wodget', 75, 1000, '15-1月-02');
INSERT INTO plsql101_product VALUES
     ('Chrome Phoobar', 50, 100, '15-1月-03');
INSERT INTO plsql101_product VALUES
     ('Round Chrome Snaphoo', 25, 10000, null);
--Round Chrome Snaphoo只在product中有，
--在purchase中无，用为outer join

INSERT INTO plsql101_product VALUES
     ('Extra Huge Mega Phoobar +',9.95,1234,'15-1月-04');

INSERT INTO plsql101_product VALUES ('Square Zinculator', 
     45, 1, TO_DATE('12月 31, 2002, 11:30 下午',
                       'Month dd, YYYY, HH:MI P.M.')
     )
;
--Square Zinculator只在product中有，在purchase中无，用为outer join


------------------------ PURCHASE ------------------------
DROP TABLE plsql101_purchase;
CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;

INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'CA', '14-7月-03', 1);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'BB', '14-7月-03', 75);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'GA', '14-7月-03', 2);
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'GA', '15-7月-03', 8);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'LB', '15-7月-03', 20);
INSERT INTO plsql101_purchase VALUES 
     ('Round Snaphoo', 'CA', '16-7月-03', 5);

-- Round Chrome Snaphoo和Square Zinculator只在product中有，
--在purchase中无，用为outer join