oracle 練習之table初始化script

最好建一新的user,如student等.

以便環境清潔

[@more@]

begin
/*
create table dept
(DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT dept_deptno_pk
PRIMARY KEY (deptNO));
*/

insert into dept
values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept
values(
20, 'RESEARCH', 'DALLAS');

insert into dept
values(30, 'SALES', 'CHICAGO');

insert into dept
values(40, 'OPERATIONS', 'BOSTON');

/*
create table emp (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);

ALTER TABLE emp
ADD CONSTRAINT emp_empno_pk
primary key (empno);

ALTER TABLE emp
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno);
*/
insert into emp
values(7839,'KING','PRESIDENT',null,to_date('1981/10/17','yyyy/mm/dd'), 5000 ,null ,10);

insert into emp
values(7698,'BLAKE','MANAGER',7839,to_date('19810501','yyyymmdd'),2850,null,30);

insert into emp
values(7782, 'CLARK','MANAGER',7839,to_date('19810718','yyyymmdd'),2450,null,10);

insert into emp
values(7566,'JONES','MANAGER', 7839,to_date('19810402','yyyymmdd'),2975,null,20);

insert into emp
values(7654,'MARTIN','SALESMAN',7698,to_date('19810928','yyyymmdd'),1250,1400,30);

insert into emp
values(7499, 'ALLEN','SALESMAN',7698,to_date('19810220','yyyymmdd'),1600,300,30);

insert into emp
values(7844,'TURNER','SALESMAN',7698,to_date('19810918','yyyymmdd'),1500,0,30);

insert into emp
values(7900,'JAMES','CLERK',7698,to_date('19811203','yyyymmdd'),950,null,30);

insert into emp
values(7521,'WARD','SALESMAN',7698,to_date('19811222','yyyymmdd'),1250,500,30);

insert into emp
values(7902,'FORD','ANALYST',7566,to_date('19811203','yyyymmdd'),3000,null,20);

insert into emp
values(7369,'SMITH','CLERK',7902,to_date('19801217','yyyymmdd'),800,null,20);

insert into emp
values(7788, 'SCOTT','ANALYST',7566,to_date('19821204','yyyymmdd'),3000,null,20);

insert into emp
values(7876, 'ADAMS','CLERK',7788,to_date('19830213','yyyymmdd'),1100,null,20);

insert into emp
values(7934, 'MILLER','CLERK',7782, to_date('19820223','yyyymmdd'),1300,null,10);

/*
create table salgrade
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER,
CONSTRAINT salgrade_grade_pk
PRIMARY KEY (grade)
);
*/
insert into salgrade
values(1 , 700, 1200);

insert into salgrade
values( 2 , 1201, 1400);

insert into salgrade
values( 3, 1401, 2000);

insert into salgrade
values(4, 2001, 3000);

insert into salgrade
values(5, 3001, 9999);

/*
create table ord
(ORDID NUMBER(4) not null,
ORDERDATE DATE,
COMMPLAN VARCHAR2(1),
CUSTID NUMBER(6) not null,
SHIPDATE DATE,
TOTAL NUMBER(8,2),
CONSTRAINT ord_ordid_pk
PRIMARY KEY (ordid)
) ;
*/

insert into ord
values(610,to_date('19870107','YYYYMMDD'),'A',101,TO_DATE('19870108','YYYYMMDD'),101.4);

insert into ord
values(611,TO_DATE('19870111','YYYYMMDD'),'B',102,TO_DATE('19870111','YYYYMMDD'),45);

insert into ord
values(612,TO_DATE('19870115','YYYYMMDD'),'C',104,TO_DATE('19870120','YYYYMMDD'),5860);

insert into ord
values(601,TO_DATE('19860501','YYYYMMDD'),'A',106,TO_DATE('19860530','YYYYMMDD'),2.4);

insert into ord
values(602,TO_DATE('19860605','YYYYMMDD'),'B',102,TO_DATE('19860620','YYYYMMDD'),56);

insert into ord
values(604,TO_DATE('19860615','YYYYMMDD'),'A',106,TO_DATE('19860630','YYYYMMDD'),698);

insert into ord
values(605, TO_DATE('19860714','YYYYMMDD'),'A', 106,TO_DATE('19860730','YYYYMMDD'),8324);

insert into ord
values(606, TO_DATE('19860714','YYYYMMDD'),'A',100,TO_DATE('19860730','YYYYMMDD'),3.4);

insert into ord
values(609, TO_DATE('19860801','YYYYMMDD'),'B',100,TO_DATE('19860815','YYYYMMDD'),97.5);

insert into ord
values(607,TO_DATE('19860718','YYYYMMDD'),'C',104,TO_DATE('19860718','YYYYMMDD'),5.6);

insert into ord
values( 608,TO_DATE('19860725','YYYYMMDD'),'C',104,TO_DATE('19860725','YYYYMMDD'),35.2);

insert into ord
values( 603,TO_DATE('19860705','YYYYMMDD'),NULL,102,TO_DATE('19860705','YYYYMMDD'),224);

insert into ord
values( 620,TO_DATE('19870312','YYYYMMDD'),NULL,100,TO_DATE('19870312','YYYYMMDD'),4450);

insert into ord
values( 613,TO_DATE('19870201','YYYYMMDD'),NULL,108,TO_DATE('19870201','YYYYMMDD'),6400);

insert into ord
values( 614,TO_DATE('19870201','YYYYMMDD'),NULL,102,TO_DATE('19870205','YYYYMMDD'),23940);

insert into ord
values( 616,TO_DATE('19870203','YYYYMMDD'),NULL,103,TO_DATE('19870201','YYYYMMDD'),764);

insert into ord
values( 619,TO_DATE('19870222','YYYYMMDD'),NULL,104,TO_DATE('19870204','YYYYMMDD'),1260);

insert into ord
values( 617,TO_DATE('19870205','YYYYMMDD'),NULL, 105,TO_DATE('19870303','YYYYMMDD') ,46370);

insert into ord
values( 615,TO_DATE('19870201','YYYYMMDD'),NULL,107,TO_DATE('19870206','YYYYMMDD'),710);

insert into ord
values( 618,TO_DATE('19870215','YYYYMMDD'),'A',102,TO_DATE('19870306','YYYYMMDD'),3510.5);

insert into ord
values( 621,TO_DATE('19870515','YYYYMMDD'),'A',100,TO_DATE('19870101','YYYYMMDD'),730);

/*
CREATE TABLE PRODUCT
( PRODID NUMBER(6) NOT NULL,
DESCRIP VARCHAR2(30),
CONSTRAINT PRODUCT_PRODID_pk
PRIMARY KEY (PRODID));
*/

INSERT INTO PRODUCT VALUES(100860,'ACE TENNIS RACKET I');

INSERT INTO PRODUCT VALUES(100861 ,'ACE TENNIS RACKET II');

INSERT INTO PRODUCT VALUES( 100870, 'ACE TENNIS BALLS-3 PACK');

INSERT INTO PRODUCT VALUES( 100871, 'ACE TENNIS BALLS-6 PACK');

INSERT INTO PRODUCT VALUES( 100890, 'ACE TENNIS NET');

INSERT INTO PRODUCT VALUES( 101860, 'SP TENNIS RACKET');

INSERT INTO PRODUCT VALUES( 101863, 'SP JUNIOR RACKET');

INSERT INTO PRODUCT VALUES( 102130, 'RH:GUIDE TO TENNIS');

INSERT INTO PRODUCT VALUES( 200376, 'SB ENERGY BAR-6 PACK');

INSERT INTO PRODUCT VALUES( 200380, 'SB VITA SNACK-6 PACK');

/*
CREATE TABLE ITEM
(ORDID NUMBER(4) NOT NULL,
ITEMID NUMBER(4) NOT NULL,
PRODID NUMBER(6),
ACTUALPRICE NUMBER(8,2),
QTY NUMBER(8),
ITEMTOT NUMBER(8,2));
*/

INSERT INTO ITEM
VALUES(610, 3 , 100890 , 58 , 1 , 58);

INSERT INTO ITEM
VALUES( 611 , 1 , 100861 , 45 , 1 , 45);

INSERT INTO ITEM
VALUES( 612 , 1 , 100860 , 30 , 100 , 3000);

INSERT INTO ITEM
VALUES( 601 , 1 , 200376 , 2.4 , 1 , 2.4);

INSERT INTO ITEM
VALUES( 602 , 1 , 100870 , 2.8 , 20 , 56);

INSERT INTO ITEM
VALUES( 604 , 1 , 100890 , 58 , 3 , 174);

INSERT INTO ITEM
VALUES( 604 , 2 , 100861 , 42 , 2 , 84);

INSERT INTO ITEM
VALUES( 604 , 3 , 100860 , 44 , 10 , 440);

INSERT INTO ITEM
VALUES( 603 , 2 , 100860 , 56 , 4 , 224);

INSERT INTO ITEM
VALUES( 610 , 1 , 100860 , 35 , 1 , 35);

INSERT INTO ITEM
VALUES( 610 , 2 , 100870 , 2.8 , 3, 8.4);

INSERT INTO ITEM
VALUES( 613 , 4 , 200376 , 2.2 , 200 , 440);

INSERT INTO ITEM
VALUES( 614 , 1 , 100860 , 35 , 444 , 15540);

INSERT INTO ITEM
VALUES( 614 , 2 , 100870 , 2.8 , 1000 , 2800);

INSERT INTO ITEM
VALUES( 612 , 2 , 100861 , 40.5 , 20 , 810);

INSERT INTO ITEM
VALUES( 612 , 3 , 101863 , 10 , 150 , 1500);

INSERT INTO ITEM
VALUES( 620 , 1 , 100860 , 35 , 10 , 350);

INSERT INTO ITEM
VALUES( 620 , 2 , 200376 , 2.4 , 1000 , 2400);

INSERT INTO ITEM
VALUES( 620 , 3 , 102130 , 3.4 , 500 , 1700);

INSERT INTO ITEM
VALUES( 613 , 1 , 100871 , 5.6 , 100 , 560);

INSERT INTO ITEM
VALUES( 613 , 2 , 101860 , 24 , 200 , 4800);

INSERT INTO ITEM
VALUES( 613 , 3 , 200380 , 4 , 150 , 600);

INSERT INTO ITEM
VALUES( 619 , 3 , 102130 , 3.4 , 100 , 340);

INSERT INTO ITEM
VALUES( 617 , 1 , 100860 , 35 , 50 , 1750);

INSERT INTO ITEM
VALUES( 617 , 2 , 100861 , 45 , 100 , 4500);

INSERT INTO ITEM
VALUES( 614 , 3 , 100871 , 5.6 , 1000 , 5600);

INSERT INTO ITEM
VALUES( 616 , 1 , 100861 , 45 , 10 , 450);

INSERT INTO ITEM
VALUES( 616 , 2 , 100870 , 2.8 , 50 , 140);

INSERT INTO ITEM
VALUES(
616 , 3 , 100890 , 58 , 2 , 116);

INSERT INTO ITEM
VALUES(
616 , 4 , 102130 , 3.4 , 10 , 34);

INSERT INTO ITEM
VALUES(
616 , 5 , 200376 , 2.4 , 10 , 24);

INSERT INTO ITEM
VALUES(
619 , 1 , 200380 , 4 , 100 , 400);

INSERT INTO ITEM
VALUES(
619 , 2 , 200376 , 2.4 , 100 , 240);

INSERT INTO ITEM
VALUES(
615 , 1 , 100861 , 45 , 4 , 180);

INSERT INTO ITEM
VALUES(
607 , 1 , 100871 , 5.6 , 1 , 5.6);

INSERT INTO ITEM
VALUES(
615 , 2 , 100870 , 2.8 , 100 , 280);

INSERT INTO ITEM
VALUES(
617 , 3 , 100870 , 2.8 , 500 , 1400);

INSERT INTO ITEM
VALUES(
617 , 4 , 100871 , 5.6 , 500 , 2800);

INSERT INTO ITEM
VALUES(
617 , 5 , 100890 , 58 , 500 , 29000);

INSERT INTO ITEM
VALUES(
617 , 6 , 101860 , 24 , 100 , 2400);

INSERT INTO ITEM
VALUES(
617 , 7 , 101863 , 12.5 , 200 , 2500);

INSERT INTO ITEM
VALUES(
617 , 8 , 102130 , 3.4 , 100 , 340);

INSERT INTO ITEM
VALUES(
617 , 9 , 200376 , 2.4 , 200 , 480);

INSERT INTO ITEM
VALUES(
617 , 10 , 200380 , 4 , 300 , 1200);

INSERT INTO ITEM
VALUES(
609 , 2 , 100870 , 2.5 , 5 ,12.5);

INSERT INTO ITEM
VALUES(
609 , 3 , 100890 , 50 , 1 , 50);

INSERT INTO ITEM
VALUES(
618 , 1 , 100860 , 35 , 23 , 805);

INSERT INTO ITEM
VALUES(
618 , 2 , 100861 , 45.11 , 50 , 2255.5);

INSERT INTO ITEM
VALUES(
618 , 3, 100870 , 45 , 10 , 450);

INSERT INTO ITEM
VALUES(
621 , 1 , 100861 , 45 , 10 , 450);

INSERT INTO ITEM
VALUES(
621 , 2 , 100870 , 2.8 , 100 , 280);

INSERT INTO ITEM
VALUES(
615 , 3 , 100871 , 5 , 50 , 250);

INSERT INTO ITEM
VALUES(
608 , 1 , 101860 , 24 , 1 , 24);

INSERT INTO ITEM
VALUES(
608 , 2 , 100871 , 5.6 , 2 , 11.2);

INSERT INTO ITEM
VALUES(
609 , 1 , 100861 , 35 , 1 , 35);

INSERT INTO ITEM
VALUES(
606 , 1 , 102130 , 3.4 , 1 , 3.4);

INSERT INTO ITEM
VALUES(
605 , 1 , 100861 , 45 , 100 , 4500);

INSERT INTO ITEM
VALUES(
605 , 2 , 100870 , 2.8 , 500 , 1400);

INSERT INTO ITEM
VALUES(
605 , 3 , 100890 , 58 , 5 , 290);

INSERT INTO ITEM
VALUES(
605 , 4 , 101860 , 24 , 50 , 1200);

INSERT INTO ITEM
VALUES(
605 , 5 , 101863 , 9 , 100 , 900);

INSERT INTO ITEM
VALUES(
605 , 6 , 102130 , 3.4 , 10 , 34);

INSERT INTO ITEM
VALUES(
612 , 4 , 100871 , 5.5 , 100 , 550);

INSERT INTO ITEM
VALUES(
619 , 4 , 100871 , 5.6 , 50 , 280);

/*
CREATE TABLE CUSTOMER
( CUSTID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP VARCHAR2(9),
AREA NUMBER(3),
PHONE VARCHAR2(9),
REPID NUMBER(4) NOT NULL,
CREDITLIMIT NUMBER(9,2),
COMMENTS LONG,
CONSTRAINT CUSTOMER_CUSTID_pk
PRIMARY KEY (CUSTID));
*/

INSERT INTO CUSTOMER VALUES(
100 ,'JOCKSPORTS','345 VIEWRIDGE','BELMONT','CA', '96711',415,'598-6609',7844,5000,NULL);

INSERT INTO CUSTOMER VALUES(
101, 'TKB SPORT SHOP','490 BOLI RD.','REDWOOD CITY','CA','94061',415,'368-1223',7521,10000,NULL);

INSERT INTO CUSTOMER VALUES(
102,'VOLLYRITE','9722 HAMILTON','BURLINGAME','CA','95133',415,'644-3341',7654,7000,NULL);

INSERT INTO CUSTOMER VALUES(
103, 'JUST TENNIS','HILLVIEW MALL',
'BURLINGAME', 'CA','97544',415,'677-9312', 7521, 3000,NULL);

INSERT INTO CUSTOMER VALUES(
104, 'EVERY MOUNTAIN' , '574 SURRY RD.',
'CUPERTINO' , 'CA', 93301 , 408, 996-2323 , 7499 , 10000,NULL);

INSERT INTO CUSTOMER VALUES(
105, 'K + T SPORTS' , '3476 EL PASEO',
'SANTA CLARA', 'CA' ,91003 , 408 ,376-9966 , 7844 , 5000,NULL);

INSERT INTO CUSTOMER VALUES(
106, 'SHAPE UP' , '908 SEQUOIA',
'PALO ALTO' , 'CA' ,94301 , 415 ,'364-9777' , 7521 , 6000,NULL);

INSERT INTO CUSTOMER VALUES(
107, 'WOMENS SPORTS' , 'VALCO VILLAGE',
'SUNNYVALE' , 'CA', '93301' , 408, '967-4398' , 7499 , 10000,NULL);

INSERT INTO CUSTOMER VALUES(
108, 'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER', '98 LONE PINE WAY',
'HIBBING' , 'MN', 55649 , 612, '566-9123' , 7844 , 8000,NULL);

/*
CREATE TABLE PRICE
( PRODID NUMBER(6) NOT NULL,
STDPRICE NUMBER(8,2),
MINPRICE NUMBER(8,2),
STARTDATE DATE,
ENDDATE DATE,
CONSTRAINT PRICE_PRODID_fk
FOREIGN KEY(PRODID) REFERENCES PRODUCT(PRODID)
);
*/

INSERT INTO PRICE VALUES
(100871 ,4.8, 3.2 ,TO_DATE('19850101','YYYYMMDD'),TO_DATE('19851201','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100890 , 58 , 46.4 ,TO_DATE('19850101','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 100890 , 54 , 40.5, TO_DATE('19840601','YYYYMMDD'),TO_DATE('19840531','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100860 , 35 , 28 ,TO_DATE('19860601','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 100860 , 32 , 25.6 ,TO_DATE('19860601','YYYYMMDD'),TO_DATE('19860531','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100860 , 30 , 24, TO_DATE('19850501','YYYYMMDD'),TO_DATE('19851201','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100861 , 45 , 36, TO_DATE('19860601','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 100861 , 42 , 33.6, TO_DATE('19860601','YYYYMMDD'),TO_DATE('19860531','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100861 , 39 , 31.2 ,TO_DATE('19850101','YYYYMMDD'),TO_DATE('19851201','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100870 , 2.8 , 2.4,TO_DATE('19860101','YYYYMMDD') ,NULL);

INSERT INTO PRICE VALUES
( 100870 , 2.4 , 1.9,TO_DATE('19850101','YYYYMMDD'),TO_DATE('19851201','YYYYMMDD'));

INSERT INTO PRICE VALUES
( 100871 , 5.6 , 4.8 ,TO_DATE('19860101','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 101860 , 24 , 18, TO_DATE('19850215','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 101863 , 12.5 , 9.4, TO_DATE('19850215','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 102130 , 3.4 , 2.8, TO_DATE('19850818','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 200376 , 2.4 , 1.75, TO_DATE('19861015','YYYYMMDD'),NULL);

INSERT INTO PRICE VALUES
( 200380 , 4, 3.2, TO_DATE('19861015','YYYYMMDD') ,NULL);

commit;

end;