tables.txt

《大型数据库技术:Oracle 9i高级程序设计教程》随书光盘。 本书结合大量的实例

create index Review_Index_Context on BOOK_REVIEW_CONTEXT(Review_Text)
indextype is ctxsys.context;

REM ****************************
REM The BOOK_REVIEW_CTXCAT Table
REM ****************************

drop table BOOK_REVIEW_CTXCAT;
create table BOOK_REVIEW_CTXCAT
(Title       VARCHAR2(100) primary key,
Reviewer     VARCHAR2(25),
Review_Date  DATE,
Review_Text  VARCHAR2(4000));

insert into BOOK_REVIEW_CTXCAT values
('MY LEDGER', 'EMILY TALBOT', '01-MAY-02',
'A fascinating look into the transactions and finances of G. B. Talbot and Dora Talbot as they managed a property in New Hampshire around 1900.  The stories come through the purchases - for medicine, doctor visits and gravesites - for workers during harvests - for gifts at the general store at Christmas.  A great read. ');
create index Review_Index_Ctxcat on BOOK_REVIEW_CTXCAT(Review_Text)
indextype is ctxsys.ctxcat;

REM *******************
REM The BOOKSHELF Table
REM *******************
REM  The CATFK foreign key requires that the CATEGORY table
REM  is created and populated before BOOKSHELF.

drop table BOOKSHELF;
create table BOOKSHELF
(Title       VARCHAR2(100) primary key,
Publisher    VARCHAR2(20),
CategoryName VARCHAR2(20),
Rating       VARCHAR2(2), 
constraint CATFK foreign key (CategoryName) 
references CATEGORY(CategoryName));

Insert into BOOKSHELF values
('TO KILL A MOCKINGBIRD','HARPERCOLLINS','ADULTFIC','5');
Insert into BOOKSHELF values
 ('WONDERFUL LIFE','W.W.NORTON','ADULTNF','5');
Insert into BOOKSHELF values
 ('INNUMERACY','VINTAGE BOOKS','ADULTNF','4');
Insert into BOOKSHELF values
 ('KIERKEGAARD ANTHOLOGY','PRINCETON UNIV PR','ADULTREF','3');
Insert into BOOKSHELF values
 ('ANNE OF GREEN GABLES','GRAMMERCY','CHILDRENFIC','3');
Insert into BOOKSHELF values
 ('GOOD DOG, CARL','LITTLE SIMON','CHILDRENPIC','1');
Insert into BOOKSHELF values
 ('LETTERS AND PAPERS FROM PRISON','SCRIBNER','ADULTNF','4');
Insert into BOOKSHELF values
 ('THE DISCOVERERS','RANDOM HOUSE','ADULTNF','4');
Insert into BOOKSHELF values
 ('THE MISMEASURE OF MAN','W.W. NORTON','ADULTNF','5');
Insert into BOOKSHELF values
 ('EITHER/OR','PENGUIN','ADULTREF','3');
Insert into BOOKSHELF values
 ('POLAR EXPRESS','HOUGHTON MIFFLIN','CHILDRENPIC','1');
Insert into BOOKSHELF values
('RUNAWAY BUNNY','HARPERFESTIVAL','CHILDRENPIC','1');
Insert into BOOKSHELF values
('MY LEDGER','KOCH PRESS','ADULTNF','5');
Insert into BOOKSHELF values
('COMPLETE POEMS OF JOHN KEATS','VIKING','ADULTREF','2');
Insert into BOOKSHELF values
('UNDER THE EYE OF THE CLOCK','ARCADE PUB','CHILDRENNF','3');
Insert into BOOKSHELF values
('JOHN ADAMS','SIMON SCHUSTER','ADULTNF','4');
Insert into BOOKSHELF values
('TRUMAN','SIMON SCHUSTER','ADULTNF','4');
Insert into BOOKSHELF values
('JOURNALS OF LEWIS AND CLARK','MARINER','ADULTNF','4');
Insert into BOOKSHELF values
('GOSPEL','PICADOR','ADULTFIC','4');
Insert into BOOKSHELF values
('EMMA WHO SAVED MY LIFE','ST MARTIN''S PRESS','ADULTFIC','3');
Insert into BOOKSHELF values
('MIDNIGHT MAGIC','SCHOLASTIC','CHILDRENFIC','1');
Insert into BOOKSHELF values
('HARRY POTTER AND THE GOBLET OF FIRE','SCHOLASTIC','CHILDRENFIC','4');
Insert into BOOKSHELF values
('SHOELESS JOE','MARINER','ADULTFIC','3');
Insert into BOOKSHELF values
('BOX SOCIALS','BALLANTINE','ADULTFIC','3');
Insert into BOOKSHELF values
('TRUMPET OF THE SWAN','HARPERCOLLINS','CHILDRENFIC','3');
Insert into BOOKSHELF values
('CHARLOTTE''S WEB','HARPERTROPHY','CHILDRENFIC','3');
Insert into BOOKSHELF values
('WEST WITH THE NIGHT','NORTH POINT PRESS','ADULTNF','3');
Insert into BOOKSHELF values
('THE GOOD BOOK','BARD','ADULTREF','4');
Insert into BOOKSHELF values
('PREACHING TO HEAD AND HEART','ABINGDON PRESS','ADULTREF','4');
Insert into BOOKSHELF values
('THE COST OF DISCIPLESHIP','TOUCHSTONE','ADULTREF','3');
Insert into BOOKSHELF values
('THE SHIPPING NEWS','SIMON SCHUSTER','ADULTFIC','4');

commit;

REM *************************
REM The BOOKSHELF_AUDIT Table
REM *************************

drop table BOOKSHELF_AUDIT;
create table BOOKSHELF_AUDIT
(Title       VARCHAR2(100),
Publisher    VARCHAR2(20),
CategoryName VARCHAR2(20),
Old_Rating   VARCHAR2(2),
New_Rating   VARCHAR2(2),
Audit_Date   DATE); 

REM **************************
REM The BOOKSHELF_AUTHOR Table
REM **************************

REM    The AUTHOR, CATEGORY, and BOOKSHELF tables 
REM    must be created before BOOKSHELF_AUTHOR.

drop table BOOKSHELF_AUTHOR;
create table BOOKSHELF_AUTHOR
(Title      VARCHAR2(100),
AuthorName  VARCHAR2(50),
constraint TitleFK Foreign key (Title) references BOOKSHELF(Title),
constraint AuthorNameFK Foreign key (AuthorName) 
references AUTHOR(AuthorName));

Insert into BOOKSHELF_AUTHOR values
('TO KILL A MOCKINGBIRD','HARPER LEE');
Insert into BOOKSHELF_AUTHOR values
 ('WONDERFUL LIFE','STEPHEN JAY GOULD');
Insert into BOOKSHELF_AUTHOR values
 ('INNUMERACY','JOHN ALLEN PAULOS');
Insert into BOOKSHELF_AUTHOR values
 ('KIERKEGAARD ANTHOLOGY','ROBERT BRETALL');
Insert into BOOKSHELF_AUTHOR values
 ('KIERKEGAARD ANTHOLOGY','SOREN KIERKEGAARD');
Insert into BOOKSHELF_AUTHOR values
 ('ANNE OF GREEN GABLES','LUCY MAUD MONTGOMERY');
Insert into BOOKSHELF_AUTHOR values
 ('GOOD DOG, CARL','ALEXANDRA DAY');
Insert into BOOKSHELF_AUTHOR values
 ('LETTERS AND PAPERS FROM PRISON','DIETRICH BONHOEFFER');
Insert into BOOKSHELF_AUTHOR values
 ('THE DISCOVERERS','DANIEL BOORSTIN');
Insert into BOOKSHELF_AUTHOR values
 ('THE MISMEASURE OF MAN','STEPHEN JAY GOULD');
Insert into BOOKSHELF_AUTHOR values
 ('EITHER/OR','SOREN KIERKEGAARD');
Insert into BOOKSHELF_AUTHOR values
 ('POLAR EXPRESS','CHRIS VAN ALLSBURG');
Insert into BOOKSHELF_AUTHOR values
('RUNAWAY BUNNY','MARGARET WISE BROWN');
Insert into BOOKSHELF_AUTHOR values
('RUNAWAY BUNNY','CLEMENT HURD');
Insert into BOOKSHELF_AUTHOR values
('MY LEDGER','G. B. TALBOT');
Insert into BOOKSHELF_AUTHOR values
('COMPLETE POEMS OF JOHN KEATS','JOHN KEATS');
Insert into BOOKSHELF_AUTHOR values
('COMPLETE POEMS OF JOHN KEATS','JOHN BARNARD');
Insert into BOOKSHELF_AUTHOR values
('UNDER THE EYE OF THE CLOCK','CHRISTOPHER NOLAN');
Insert into BOOKSHELF_AUTHOR values
('JOHN ADAMS','DAVID MCCULLOUGH');
Insert into BOOKSHELF_AUTHOR values
('TRUMAN','DAVID MCCULLOUGH');
Insert into BOOKSHELF_AUTHOR values
('JOURNALS OF LEWIS AND CLARK','MERIWETHER LEWIS');
Insert into BOOKSHELF_AUTHOR values
('JOURNALS OF LEWIS AND CLARK','WILLIAM CLARK');
Insert into BOOKSHELF_AUTHOR values
('JOURNALS OF LEWIS AND CLARK','STEPHEN AMBROSE');
Insert into BOOKSHELF_AUTHOR values
('JOURNALS OF LEWIS AND CLARK','BERNARD DE VOTO');
Insert into BOOKSHELF_AUTHOR values
('GOSPEL','WILTON BARNHARDT');
Insert into BOOKSHELF_AUTHOR values
('EMMA WHO SAVED MY LIFE','WILTON BARNHARDT');
Insert into BOOKSHELF_AUTHOR values
('MIDNIGHT MAGIC','AVI');
Insert into BOOKSHELF_AUTHOR values
('HARRY POTTER AND THE GOBLET OF FIRE','J. K. ROWLING');
Insert into BOOKSHELF_AUTHOR values
('SHOELESS JOE','W. P. KINSELLA');
Insert into BOOKSHELF_AUTHOR values
('BOX SOCIALS','W. P. KINSELLA');
Insert into BOOKSHELF_AUTHOR values
('TRUMPET OF THE SWAN','E. B. WHITE');
Insert into BOOKSHELF_AUTHOR values
('CHARLOTTE''S WEB','E. B. WHITE');
Insert into BOOKSHELF_AUTHOR values
('WEST WITH THE NIGHT','BERYL MARKHAM');
Insert into BOOKSHELF_AUTHOR values
('THE GOOD BOOK','PETER GOMES');
Insert into BOOKSHELF_AUTHOR values
('PREACHING TO HEAD AND HEART','THOMAS SWEARS');
Insert into BOOKSHELF_AUTHOR values
('THE COST OF DISCIPLESHIP','DIETRICH BONHOEFFER');
Insert into BOOKSHELF_AUTHOR values
('THE SHIPPING NEWS','E. ANNIE PROULX');

commit;

REM ****************************
REM The BOOKSHELF_CHECKOUT Table
REM ****************************

drop table BOOKSHELF_CHECKOUT;
create table BOOKSHELF_CHECKOUT
(Name  VARCHAR2(25),
 Title VARCHAR2(100),
 CheckoutDate  DATE,
 ReturnedDate  DATE);

Insert into BOOKSHELF_CHECKOUT values
('JED HOPKINS','INNUMERACY','01-JAN-02','22-JAN-02');
Insert into BOOKSHELF_CHECKOUT values
('GERHARDT KENTGEN','WONDERFUL LIFE','02-JAN-02','02-FEB-02');
Insert into BOOKSHELF_CHECKOUT values
('DORAH TALBOT','EITHER/OR','02-JAN-02','10-JAN-02');
Insert into BOOKSHELF_CHECKOUT values
('EMILY TALBOT','ANNE OF GREEN GABLES','02-JAN-02','20-JAN-02');
Insert into BOOKSHELF_CHECKOUT values
('PAT LAVAY','THE SHIPPING NEWS','02-JAN-02','12-JAN-02');
Insert into BOOKSHELF_CHECKOUT values
('ROLAND BRANDT','THE SHIPPING NEWS','12-JAN-02','12-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('ROLAND BRANDT','THE DISCOVERERS','12-JAN-02','01-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('ROLAND BRANDT','WEST WITH THE NIGHT','12-JAN-02','01-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('EMILY TALBOT','MIDNIGHT MAGIC','20-JAN-02','03-FEB-02');
Insert into BOOKSHELF_CHECKOUT values
('EMILY TALBOT','HARRY POTTER AND THE GOBLET OF FIRE','03-FEB-02','14-FEB-02');
Insert into BOOKSHELF_CHECKOUT values
('PAT LAVAY','THE MISMEASURE OF MAN','12-JAN-02','12-FEB-02');
Insert into BOOKSHELF_CHECKOUT values
('DORAH TALBOT','POLAR EXPRESS','01-FEB-02','15-FEB-02');
Insert into BOOKSHELF_CHECKOUT values
('DORAH TALBOT','GOOD DOG, CARL','01-FEB-02','15-FEB-02');
Insert into BOOKSHELF_CHECKOUT values
('GERHARDT KENTGEN','THE MISMEASURE OF MAN','13-FEB-02','05-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('FRED FULLER','JOHN ADAMS','01-FEB-02','01-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('FRED FULLER','TRUMAN','01-MAR-02','20-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('JED HOPKINS','TO KILL A MOCKINGBIRD','15-FEB-02','01-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('DORAH TALBOT','MY LEDGER','15-FEB-02','03-MAR-02');
Insert into BOOKSHELF_CHECKOUT values
('GERHARDT KENTGEN','MIDNIGHT MAGIC','05-FEB-02','10-FEB-02');

commit;

REM ******************
REM The BORROWER Table
REM ******************

rem  Requires that the TOOLS_VA datatype be created first.

drop table BORROWER;
create table BORROWER
(Name          VARCHAR2(25),
 Tools         TOOLS_VA,
constraint BORROWER_PK primary key (Name));

insert into BORROWER values
('JED HOPKINS',
  TOOLS_VA('HAMMER','SLEDGE','AX'));
commit;

REM *****************
REM The BREEDER Table
REM *****************

rem  Requires that the ANIMAL_TY and ANIMALS_NT types
rem    already exist.

drop table BREEDER;
create table BREEDER (
BreederName      VARCHAR2(25),
Animals          ANIMALS_NT)
nested table ANIMALS store as ANIMALS_NT_TAB;

REM ******************
REM The BREEDING Table
REM ******************

drop table BREEDING;
create table BREEDING (
Offspring     VARCHAR2(10),
Sex           CHAR(1),
Cow           VARCHAR2(10),
Bull          VARCHAR2(10),
Birthdate     DATE
);

insert into BREEDING values ('EVE','F',null,null,null);
insert into BREEDING values ('ADAM','M',null,null,null);
insert into BREEDING values ('BANDIT','M',null,null,null);
insert into BREEDING values ('BETSY','F','EVE','ADAM',
   TO_DATE('02-JAN-1900','DD-MON-YYYY'));
insert into BREEDING values ('POCO','M','EVE','ADAM',
   TO_DATE('15-JUL-1900','DD-MON-YYYY'));
insert into BREEDING values ('GRETA','F','EVE','BANDIT',
   TO_DATE('12-MAR-1901','DD-MON-YYYY'));
insert into BREEDING values ('MANDY','F','EVE','POCO',
   TO_DATE('22-AUG-1902','DD-MON-YYYY'));
insert into BREEDING values ('NOVI','F','BETSY','ADAM',
   TO_DATE('30-MAR-1903','DD-MON-YYYY'));
insert into BREEDING values ('GINNY','F','BETSY','BANDIT',
   TO_DATE('04-DEC-1903','DD-MON-YYYY'));
insert into BREEDING values ('CINDY','F','EVE','POCO',
   TO_DATE('09-FEB-1903','DD-MON-YYYY'));
insert into BREEDING values ('DUKE','M','MANDY','BANDIT',
   TO_DATE('24-JUL-1904','DD-MON-YYYY'));
insert into BREEDING values ('TEDDI','F','BETSY','BANDIT',
   TO_DATE('12-AUG-1905','DD-MON-YYYY'));
insert into BREEDING values ('SUZY','F','GINNY','DUKE',
   TO_DATE('03-APR-1906','DD-MON-YYYY'));
insert into BREEDING values ('RUTH','F','GINNY','DUKE',
   TO_DATE('25-DEC-1906','DD-MON-YYYY'));
insert into BREEDING values ('PAULA','F','MANDY','POCO',
   TO_DATE('21-DEC-1906','DD-MON-YYYY'));
insert into BREEDING values ('DELLA','F','SUZY','BANDIT',
   TO_DATE('11-OCT-1908','DD-MON-YYYY'));
commit;

REM ******************
REM The CATEGORY Table
REM ******************

drop table CATEGORY;
create table CATEGORY
(CategoryName  VARCHAR2(12) primary key,
ParentCategory  VARCHAR2(8),
SubCategory VARCHAR2(20));

Insert into CATEGORY values
('ADULTREF','ADULT','REFERENCE');
Insert into CATEGORY values
('ADULTFIC','ADULT','FICTION');
Insert into CATEGORY values
('ADULTNF','ADULT','NONFICTION');
Insert into CATEGORY values
('CHILDRENPIC','CHILDREN','PICTURE BOOK');
Insert into CATEGORY values
('CHILDRENFIC','CHILDREN','FICTION');
Insert into CATEGORY values
('CHILDRENNF','CHILDREN','NONFICTION');

commit;

REM *****************
REM The COMFORT Table
REM *****************

drop table COMFORT;
create table COMFORT (
City          VARCHAR2(13) NOT NULL,
SampleDate    DATE NOT NULL,
Noon          NUMBER(3,1),
Midnight      NUMBER(3,1),
Precipitation NUMBER
);

insert into COMFORT values ('SAN FRANCISCO',
   TO_DATE('21-MAR-2001','DD-MON-YYYY'),62.5,42.3,.5);
insert into COMFORT values ('SAN FRANCISCO',
   TO_DATE('22-JUN-2001','DD-MON-YYYY'),51.1,71.9,.1);
insert into COMFORT values ('SAN FRANCISCO',
   TO_DATE('23-SEP-2001','DD-MON-YYYY'),NULL,61.5,.1);
insert into COMFORT values ('SAN FRANCISCO',
   TO_DATE('22-DEC-2001','DD-MON-YYYY'),52.6,39.8,2.3);
insert into COMFORT values ('KEENE',
   TO_DATE('21-MAR-2001','DD-MON-YYYY'),39.9,-1.2,4.4);
insert into COMFORT values ('KEENE',
   TO_DATE('22-JUN-2001','DD-MON-YYYY'),85.1,66.7,1.3);
insert into COMFORT values ('KEENE',
   TO_DATE('23-SEP-2001','DD-MON-YYYY'),99.8,82.6,NULL);
insert into COMFORT values ('KEENE',
   TO_DATE('22-DEC-2001','DD-MON-YYYY'),-7.2,-1.2,3.9);
commit;

REM ******************
REM The COMFORT2 Table
REM ******************

drop table COMFORT2;
create table COMFORT2 (
City          VARCHAR2(13) NOT NULL,
SampleDate    DATE NOT NULL,
Noon          NUMBER(3,1),
Midnight      NUMBER(3,1),
Precipitation NUMBER
);

REM **********************
REM The COMFORT_TEST Table
REM **********************

drop table COMFORT_TEST;
create table COMFORT_TEST (
City          VARCHAR2(13) NOT NULL,
SampleDate    DATE NOT NULL,
Measure       VARCHAR2(10),
Value         NUMBER(3,1)
);

REM *******************
REM The CONTINENT Table
REM *******************

drop table CONTINENT;
create table CONTINENT (
 Continent    VARCHAR2(30));

REM *****************
REM The COUNTRY Table
REM *****************

drop table COUNTRY;
create table COUNTRY (
Country      VARCHAR2(30) not null,
Continent    VARCHAR2(30));

REM ******************
REM The CUSTOMER Table
REM ******************

rem  Requires that the ADDRESS_TY and PERSON_TY types already
rem  exist.

drop table CUSTOMER;
create table CUSTOMER (
Customer_ID    NUMBER,
Person         PERSON_TY
);

insert into CUSTOMER VALUES
(1,