tables.txt

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

Tables for the Oracle9i:The Complete Reference, by Kevin Loney and George Koch, 
copyright Osborne/McGraw-Hill

Tables Used in this Book

This file contains the complete listings of all the tables used in the examples 
throughout the book, so you can access these tables without retyping. They can 
be keyed in to a start file "as is" to load into a database using SQLPLUS. If 
these tables are a part of your Oracle database, you can easily work your way 
through the examples in this book and experiment with the many techniques that 
are illustrated.

For ease of use, set up a user named practice, with a password of practice. Give 
this user CONNECT and RESOURCE authority so that new users can access it to learn 
Oracle. If you are a new user on an existing Oracle installation, check with your 
DBA to see if this has already been done. If not, you might request it, or at 
least request that the tables and start files be available to you through public 
synonyms. Whatever "damage" is done on the practice schema can always be repaired 
simply by reloading the tables from their start files.

REM ***************************
REM The activity.sql Start File
REM ***************************

rem  Bookshelf activity report
set headsep !

ttitle 'Checkout Log for 1/1/02-3/31/02'
btitle 'from the Bookshelf'

column Name format a20
column Title format a20 word_wrapped
column DaysOut format 999.99
column DaysOut heading 'Days!Out'

break on Name skip 1 on report
compute avg of DaysOut on Name
compute avg of DaysOut on report

set linesize 80
set pagesize 60
set newpage 0

spool activity.lst

select Name, Title, CheckoutDate, ReturnedDate,
       ReturnedDate-CheckoutDate as DaysOut /*Count days*/
  from BOOKSHELF_CHECKOUT
 order by Name, CheckoutDate;

spool off

REM ***********************
REM The math.sql Start File
REM ***********************

REM The MATH table must be created before this script can be run.

rem         Name: math.sql       Type: start file report
rem   Written by: G. Koch
rem
rem  Description: Illustrates ORACLE math functions. Produces
rem               an output file named MATH.LST
set echo off
set pagesize 32000
set linesize 132
column Plus format 99.999
column Subtr format 999.999
column Times format 9999.999999
column Divided format .999999
column VSIZE(Above) heading 'VSIZE|(Above)'
column VSIZE(Below) heading 'VSIZE|(Below)'
column VSIZE(Empty) heading 'VSIZE|(Empty)'
column NVL(Above,11) heading 'NVL|(Above,11)'
column NVL(Below,11) heading 'NVL|(Below,11)'
column NVL(Empty,11) heading 'NVL|(Empty,11)'
column ROUND(Above,2) heading 'ROUND|(Above,2)'
column ROUND(Below,2) heading 'ROUND|(Below,2)'
column TRUNC(Above,2) heading 'TRUNC|(Above,2)'
column TRUNC(Below,2) heading 'TRUNC|(Below,2)'
column ROUND(Above,0) heading 'ROUND|(Above,0)'
column ROUND(Below,0) heading 'ROUND|(Below,0)'
column TRUNC(Above,0) heading 'TRUNC|(Above,0)'
column TRUNC(Below,0) heading 'TRUNC|(Below,0)'
column ROUND(Above,-1) heading 'ROUND|(Above,-1)'
column ROUND(Below,-1) heading 'ROUND|(Below,-1)'
column TRUNC(Above,-1) heading 'TRUNC|(Above,-1)'
column TRUNC(Below,-1) heading 'TRUNC|(Below,-1)'
set echo on
set numwidth 7
set numformat ""
spool math.lst
select Name, Above, Below, Empty from MATH;

select Name, Above, Below, Empty,
       Above + Below "Plus",
       Above - Below "Subtr",
       Above * Below "Times",
       Above / Below "Divided"
  from MATH where Name = 'HIGH DECIMAL';

select Name, Above, Below, Empty,
       Above + Empty "Plus",
       Above - Empty "Subtr",
       Above * Empty "Times",
       Above / Empty "Divided"
  from MATH where Name = 'HIGH DECIMAL';

select Name, Above, Below, Empty,
       ABS(Above),
       ABS(Below),
       ABS(Empty)
  from MATH where Name = 'HIGH DECIMAL';

select Name, Above, Below, Empty,
       VSIZE(Above),
       VSIZE(Below),
       VSIZE(Empty)
  from MATH;

select Name, Above, Below, Empty,
       NVL(Above,11),
       NVL(Below,11),
       NVL(Empty,11)
  from MATH where Name = 'HIGH DECIMAL';

select Name, Above, Below,
       CEIL(Above),
       CEIL(Below)
  from MATH;

select Name, Above, Below,
       FLOOR(Above),
       FLOOR(Below)
  from MATH;

select Name, Above, Below,
       MOD(Above,11),
       MOD(Below,11)
  from MATH where Name = 'HIGH DECIMAL';

set numformat 9999.999999
select Name, Above, Below,
       POWER(Above,2),
       POWER(Below,2)
  from MATH where Name = 'HIGH DECIMAL';

select Name, Above, Below,
       POWER(Above,2.9),
       POWER(Below,2.9)
  from MATH;

set numformat ""
select Name, Above, Below,
       SQRT(Above),
       SQRT(Below)
  from MATH where Name = 'HIGH DECIMAL';

select Name, Above, Below,
       ROUND(Above,2),
       ROUND(Below,2),
       TRUNC(Above,2),
       TRUNC(Below,2)
  from MATH;

select Name, Above, Below,
       ROUND(Above,0),
       ROUND(Below,0),
       TRUNC(Above,0),
       TRUNC(Below,0)
  from MATH;

select Name, Above, Below,
       ROUND(Above,-1),
       ROUND(Below,-1),
       TRUNC(Above,-1),
       TRUNC(Below,-1)
  from MATH;


select Name, Above, Below,
       SIGN(Above),
       SIGN(Below)
  from MATH where Name = 'HIGH DECIMAL';

spool off

REM *******************
REM The ADDRESS_TY Type
REM *******************
create or replace type ADDRESS_TY as object
(Street  VARCHAR2(50),
 City    VARCHAR2(25),
 State   CHAR(2),
 Zip     NUMBER);
/

REM ******************
REM The ANIMAL_TY Type
REM ******************

create or replace type ANIMAL_TY as object
(Breed      VARCHAR2(25),
 Name       VARCHAR2(25),
 BirthDate  DATE,
member function AGE (BirthDate IN DATE) return NUMBER,
PRAGMA RESTRICT_REFERENCES(AGE, WNDS));
/

create or replace type body ANIMAL_TY as
member function Age (BirthDate DATE) return NUMBER is
begin
  RETURN ROUND(SysDate - BirthDate);
end;
end;
/

rem  For the Nested Table examples:
create or replace type ANIMALS_NT as table of ANIMAL_TY;
/

create table ANIMAL of ANIMAL_TY;

REM ******************
REM The PERSON_TY Type
REM ******************

rem  Requires that the ADDRESS_TY type already exist.

Create or replace type PERSON_TY as object
(Name      VARCHAR2(25),
 Address   ADDRESS_TY);
/

REM *****************
REM The TOOLS_VA Type
REM *****************

rem  Creates a varying array datatype.

create or replace type TOOLS_VA as varray(5) of VARCHAR2(25);
/

REM *****************
REM The ADDRESS Table
REM *****************

drop table ADDRESS;
create table ADDRESS (
LastName   VARCHAR2(25),
FirstName  VARCHAR2(25),
Street     VARCHAR2(50),
City       VARCHAR2(25),
State      CHAR(2),
Zip        NUMBER,
Phone      VARCHAR2(12),
Ext        VARCHAR2(5)
);

insert into ADDRESS values ('BAILEY', 'WILLIAM',
     null,null,null,null,'213-293-0223',null);
insert into ADDRESS values ('ADAMS', 'JACK',
     null,null,null,null,'415-453-7530',null);
insert into ADDRESS values ('SEP', 'FELICIA',
     null,null,null,null,'214-522-8383',null);
insert into ADDRESS values ('DE MEDICI', 'LEFTY',
     null,null,null,null,'312-736-1166',null);
insert into ADDRESS values ('DEMIURGE', 'FRANK',
     null,null,null,null,'707-767-8900',null);
insert into ADDRESS values ('CASEY', 'WILLIS',
     null,null,null,null,'312-684-1414',null);
insert into ADDRESS values ('ZACK', 'JACK',
     null,null,null,null,'415-620-6842',null);
insert into ADDRESS values ('YARROW', 'MARY',
     null,null,null,949414302,'415-787-2178',null);
insert into ADDRESS values ('WERSCHKY', 'ARNY',
     null,null,null,null,'415-235-7387',null);
insert into ADDRESS values ('BRANT', 'GLEN',
     null,null,null,null,'415-526-7512',null);
insert into ADDRESS values ('EDGAR', 'THEODORE',
     null,null,null,null,'415-525-6252',null);
insert into ADDRESS values ('HARDIN', 'HUGGY',
     null,null,null,null,'617-566-0125',null);
insert into ADDRESS values ('HILD', 'PHIL',
     null,null,null,null,'603-934-2242',null);
insert into ADDRESS values ('LOEBEL', 'FRANK',
     null,null,null,null,'202-456-1414',null);
insert into ADDRESS values ('MOORE', 'MARY',
     null,null,null,601262460,'718-857-1638',null);
insert into ADDRESS values ('SZEP', 'FELICIA',
     null,null,null,null,'214-522-8383',null);
insert into ADDRESS values ('ZIMMERMAN', 'FRED',
     null,null,null,null,'503-234-7491',null);
commit;

REM ***************
REM The AREAS Table
REM ***************

drop table AREAS;
create table AREAS
(Radius      NUMBER(5),
 Area        NUMBER(14,2)
);

REMARK No inserts. This table is empty.

REM ****************
REM The AUTHOR Table
REM ****************

drop table AUTHOR;
create table AUTHOR
(AuthorName  VARCHAR2(50) primary key,
Comments  VARCHAR2(100));

Insert into AUTHOR values
('DIETRICH BONHOEFFER', 'GERMAN THEOLOGIAN, KILLED IN A WAR CAMP');
Insert into AUTHOR values
('ROBERT BRETALL','KIERKEGAARD ANTHOLOGIST');
Insert into AUTHOR values
('ALEXANDRA DAY','AUTHOR OF PICTURE BOOKS FOR CHILDREN');
Insert into AUTHOR values
('STEPHEN JAY GOULD','SCIENCE COLUMNIST, HARVARD PROFESSOR');
Insert into AUTHOR values
('SOREN KIERKEGAARD','DANISH PHILOSOPHER AND THEOLOGIAN');
Insert into AUTHOR values
('HARPER LEE','AMERICAN NOVELIST, PUBLISHED ONLY ONE NOVEL');
Insert into AUTHOR values
('LUCY MAUD MONTGOMERY','CANADIAN NOVELIST');
Insert into AUTHOR values
('JOHN ALLEN PAULOS','MATHEMATICS PROFESSOR');
Insert into AUTHOR values
('J. RODALE', 'ORGANIC GARDENING EXPERT');
Insert into AUTHOR values
('DANIEL BOORSTIN', 'LIBRARIAN OF CONGRESS');
Insert into AUTHOR values
('CHRIS VAN ALLSBURG', 'ILLUSTRATOR');
Insert into AUTHOR values
('MARGARET WISE BROWN', 'EDITOR AND AUTHOR');
Insert into AUTHOR values
('CLEMENT HURD', 'ILLUSTRATOR');
Insert into AUTHOR values
('G. B. TALBOT', 'FAMOUS LEDGERIST');
Insert into AUTHOR values
('JOHN KEATS', 'ROMANTIC POET');
Insert into AUTHOR values
('JOHN BARNARD', 'KEATS EDITOR');
Insert into AUTHOR values
('CHRISTOPHER NOLAN', 'POET AND AUTHOR');
Insert into AUTHOR values
('DAVID MCCULLOUGH', 'HISTORIAN');
Insert into AUTHOR values
('MERIWETHER LEWIS', 'EXPLORER AND JOURNALIST');
Insert into AUTHOR values
('WILLIAM CLARK', 'EXPLORER AND JOURNALIST');
Insert into AUTHOR values
('STEPHEN AMBROSE',NULL);
Insert into AUTHOR values
('BERNARD DE VOTO', 'EDITOR');
Insert into AUTHOR values
('WILTON BARNHARDT', 'AMERICAN AUTHOR');
Insert into AUTHOR values
('AVI', 'PROLIFIC AUTHOR OF BOOKS FOR CHILDREN');
Insert into AUTHOR values
('J. K. ROWLING', 'AUTHOR OF HARRY POTTER SERIES');
Insert into AUTHOR values
('W. P. KINSELLA', 'AUTHOR AND WRITING PROFESSOR');
Insert into AUTHOR values
('E. B. WHITE', 'AUTHOR AND ESSAYIST');
Insert into AUTHOR values
('BERYL MARKHAM', 'AVIATOR AND ADVENTURESS');
Insert into AUTHOR values
('PETER GOMES', 'HARVARD THEOLOGIAN');
Insert into AUTHOR values
('THOMAS SWEARS', 'PASTOR AND AUTHOR');
Insert into AUTHOR values
('E. ANNIE PROULX',NULL);
commit;

REM ******************
REM The BIRTHDAY Table
REM ******************

drop table BIRTHDAY;
create table BIRTHDAY (
FirstName     VARCHAR2(15),
LastName      VARCHAR2(15),
BirthDate     DATE,
Age           NUMBER,
constraint PK_BIRTHDAY primary key (FirstName, LastName)
);

insert into BIRTHDAY values ('GEORGE','SAND',
        TO_DATE('12-MAY-1946','DD-MON-YYYY'),42);
insert into BIRTHDAY values ('ROBERT','JAMES',
        TO_DATE('23-AUG-1937','DD-MON-YYYY'),52);
insert into BIRTHDAY values ('NANCY','LEE',
        TO_DATE('02-FEB-1947','DD-MON-YYYY'),42);
insert into BIRTHDAY values ('VICTORIA','LYNN',
        TO_DATE('20-MAY-1949 3:27','DD-MON-YYYY HH24:MI'),42);
insert into BIRTHDAY values ('FRANK','PILOT',
        TO_DATE('11-NOV-1942','DD-MON-YYYY'),42);
commit;

REM ********************
REM The BOOK_ORDER Table
REM ********************

drop table BOOK_ORDER;
create table BOOK_ORDER
(Title       VARCHAR2(100) primary key,
Publisher    VARCHAR2(20),
CategoryName VARCHAR2(20));


Insert into BOOK_ORDER values
('SHOELESS JOE','MARINER','ADULTFIC');
Insert into BOOK_ORDER values
('GOSPEL','PICADOR','ADULTFIC');
Insert into BOOK_ORDER values
('SOMETHING SO STRONG','PANDORAS','ADULTNF');
Insert into BOOK_ORDER values
('GALILEO''S DAUGHTER','PENGUIN','ADULTNF');
Insert into BOOK_ORDER values
('LONGITUDE','PENGUIN','ADULTNF');
Insert into BOOK_ORDER values
('ONCE REMOVED','SANCTUARY PUB','ADULTNF');
commit;

REM *****************************
REM The BOOK_REVIEW_CONTEXT Table
REM *****************************

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

insert into BOOK_REVIEW_CONTEXT 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. ');