tables.txt

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

 PERSON_TY('NEIL MULLANE',
            ADDRESS_TY('57 MT PLEASANT ST',
                       'FINN', 'NH', 11111)));
insert into CUSTOMER VALUES
(2,
 PERSON_TY('SEYMOUR HESTER',
            ADDRESS_TY('1 STEPAHEAD RD',
                       'BRIANT', 'NH', 11111)));
commit;

REM *****************
REM The HOLIDAY Table
REM *****************

drop table HOLIDAY;
create table HOLIDAY (
Holiday        VARCHAR2(25),
ActualDate     DATE,
CelebratedDate DATE
);

insert into HOLIDAY values ('NEW YEAR DAY',
   TO_DATE('01-JAN-2002','DD-MON-YYYY'),
   TO_DATE('01-JAN-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('MARTIN LUTHER KING, JR.',
   TO_DATE('15-JAN-2002','DD-MON-YYYY'),
   TO_DATE('17-JAN-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('LINCOLNS BIRTHDAY',
   TO_DATE('12-FEB-2002','DD-MON-YYYY'),
   TO_DATE('18-FEB-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('WASHINGTONS BIRTHDAY',
   TO_DATE('22-FEB-2002','DD-MON-YYYY'),
   TO_DATE('18-FEB-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('FAST DAY, NEW HAMPSHIRE',
   TO_DATE('22-FEB-2002','DD-MON-YYYY'),
   TO_DATE('22-FEB-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('MEMORIAL DAY',
   TO_DATE('30-MAY-2002','DD-MON-YYYY'),
   TO_DATE('27-MAY-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('INDEPENDENCE DAY',
   TO_DATE('04-JUL-2002','DD-MON-YYYY'),
   TO_DATE('04-JUL-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('LABOR DAY',
   TO_DATE('02-SEP-2002','DD-MON-YYYY'),
   TO_DATE('02-SEP-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('COLUMBUS DAY',
   TO_DATE('12-OCT-2002','DD-MON-YYYY'),
   TO_DATE('14-OCT-2002','DD-MON-YYYY'));
insert into HOLIDAY values ('THANKSGIVING',
   TO_DATE('28-NOV-2002','DD-MON-YYYY'),
   TO_DATE('28-NOV-2002','DD-MON-YYYY'));
commit;

REM ******************
REM The LOCATION Table
REM ******************

drop table LOCATION;
create table LOCATION (
City       VARCHAR2(25),
Country    VARCHAR2(25),
Continent  VARCHAR2(25),
Latitude   NUMBER,
NorthSouth CHAR(1),
Longitude  NUMBER,
EastWest   CHAR(1)
);

insert into LOCATION values (
  'ATHENS','GREECE','EUROPE',37.58,'N',23.43,'E');
insert into LOCATION values (
  'CHICAGO','UNITED STATES','NORTH AMERICA',41.53,'N',87.38,'W');
insert into LOCATION values (
  'CONAKRY','GUINEA','AFRICA',9.31,'N',13.43,'W');
insert into LOCATION values (
  'LIMA','PERU','SOUTH AMERICA',12.03,'S',77.03,'W');
insert into LOCATION values (
  'MADRAS','INDIA','INDIA',13.05,'N',80.17,'E');
insert into LOCATION values (
  'MANCHESTER','ENGLAND','EUROPE',53.30,'N',2.15,'W');
insert into LOCATION values (
  'MOSCOW','RUSSIA','EUROPE',55.45,'N',37.35,'E');
insert into LOCATION values (
  'PARIS','FRANCE','EUROPE',48.52,'N',2.20,'E');
insert into LOCATION values (
  'SHENYANG','CHINA','CHINA',41.48,'N',123.27,'E');
insert into LOCATION values (
  'ROME','ITALY','EUROPE',41.54,'N',12.29,'E');
insert into LOCATION values (
  'TOKYO','JAPAN','ASIA',35.42,'N',139.46,'E');
insert into LOCATION values (
  'SYDNEY','AUSTRALIA','AUSTRALIA',33.52,'S',151.13,'E');
insert into LOCATION values (
  'SPARTA','GREECE','EUROPE',37.05,'N',22.27,'E');
insert into LOCATION values (
  'MADRID','SPAIN','EUROPE',40.24,'N',3.41,'W');
commit;

REM ******************
REM The MAGAZINE Table
REM ******************

drop table magazine;
create table magazine (
Name       VARCHAR2(16),
Title      VARCHAR2(37),
Author     VARCHAR2(25),
IssueDate  DATE,
Page       NUMBER
);

insert into MAGAZINE values (
  'BERTRAND MONTHLY','THE BARBERS WHO SHAVE THEMSELVES.',
  'BONHOEFFER, DIETRICH',
  TO_DATE('23-MAY-1988','DD-MON-YYYY'),70);
insert into MAGAZINE values (
  'LIVE FREE OR DIE','"HUNTING THOREAU IN NEW HAMPSHIRE"',
  'CHESTERTON, G.K.',
  TO_DATE('26-AUG-1981','DD-MON-YYYY'),320);
insert into MAGAZINE values (
  'PSYCHOLOGICA','THE ETHNIC NEIGHBORHOOD',
  'RUTH, GEORGE HERMAN',
  TO_DATE('18-SEP-1919','DD-MON-YYYY'),246);
insert into MAGAZINE values (
  'FADED ISSUES','RELATIONAL DESIGN AND ENTHALPY',
  'WHITEHEAD, ALFRED',
  TO_DATE('20-JUN-1926','DD-MON-YYYY'),279);
insert into MAGAZINE values (
  'ENTROPY WIT','"INTERCONTINENTAL RELATIONS."',
  'CROOKES, WILLIAM',
  TO_DATE('20-SEP-1950','DD-MON-YYYY'),20);
commit;

REM **************
REM The MATH Table
REM **************

drop table MATH;
create table MATH (
Name          VARCHAR2(12),
Above         NUMBER,
Below         NUMBER,
Empty         NUMBER
);

insert into MATH values ('WHOLE NUMBER',11,-22,null);
insert into MATH values ('LOW DECIMAL',33.33,-44.44,null);
insert into MATH values ('MID DECIMAL',55.5,-55.5,null);
insert into MATH values ('HIGH DECIMAL',66.666,-77.777,null);
commit;


REM **************
REM The NAME Table
REM **************

drop table NAME;
create table NAME (
Name         VARCHAR2(25)
);

insert into NAME values ('HORATIO NELSON');
insert into NAME values ('VALDO');
insert into NAME values ('MARIE DE MEDICIS');
insert into NAME values ('FLAVIUS JOSEPHUS');
insert into NAME values ('EDYTHE P. M. GAMMIERE');
commit;

REM *******************
REM The NEWSPAPER Table
REM *******************

drop table NEWSPAPER;
create table NEWSPAPER (
Feature      VARCHAR2(15) not null,
Section      CHAR(1),
Page         NUMBER
);

insert into NEWSPAPER values ('National News', 'A', 1);
insert into NEWSPAPER values ('Sports', 'D', 1);
insert into NEWSPAPER values ('Editorials', 'A', 12);
insert into NEWSPAPER values ('Business', 'E', 1);
insert into NEWSPAPER values ('Weather', 'C', 2);
insert into NEWSPAPER values ('Television', 'B', 7);
insert into NEWSPAPER values ('Births', 'F', 7);
insert into NEWSPAPER values ('Classified', 'F', 8);
insert into NEWSPAPER values ('Modern Life', 'B', 1);
insert into NEWSPAPER values ('Comics', 'C', 4);
insert into NEWSPAPER values ('Movies', 'B', 4);
insert into NEWSPAPER values ('Bridge', 'B', 2);
insert into NEWSPAPER values ('Obituaries', 'F', 6);
insert into NEWSPAPER values ('Doctor Is In', 'F', 6);
commit;

REM ********************
REM The NUMBERTEST Table
REM ********************

drop table NUMBERTEST;
create table NUMBERTEST (
Value1        NUMBER,
Value2        NUMBER,
Value3        NUMBER(10,2)
);

insert into NUMBERTEST values (0,0,0);
insert into NUMBERTEST values (.0001,.0001,.0001);
insert into NUMBERTEST values (1234,1234,1234);
insert into NUMBERTEST values (1234.5,1234.5,1234.5);
insert into NUMBERTEST values (null,null,null);
insert into NUMBERTEST values (1234.56,1234.56,1234.56);
insert into NUMBERTEST values (1234.567,1234.567,1234.567);
insert into NUMBERTEST values
     (98761234.567,98761234.567,98761234.567);
commit;

REM ****************
REM The PAYDAY Table
REM ****************

drop table PAYDAY;
create table PAYDAY (
CycleDate      DATE
);

insert into PAYDAY values (TO_DATE('15-JAN-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-FEB-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-MAR-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-APR-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-MAY-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-JUN-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-JUL-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-AUG-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-SEP-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-OCT-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-NOV-2002','DD-MON-YYYY'));
insert into PAYDAY values (TO_DATE('15-DEC-2002','DD-MON-YYYY'));
commit;

REM ******************
REM The PROPOSAL Table
REM ******************

drop table PROPOSAL;
create table PROPOSAL
(Proposal_ID        NUMBER(10) primary key,
 Recipient_Name     VARCHAR2(25),
 Proposal_Name      VARCHAR2(25),
 Short_Description  VARCHAR2(1000),
 Proposal_Text      CLOB,
 Budget             BLOB,
 Cover_Letter       BFILE);

REM *********************
REM The RADIUS_VALS Table
REM *********************

drop table RADIUS_VALS;
create table RADIUS_VALS
(Radius      NUMBER(5));

REM ****************
REM The RATING Table
REM ****************

drop table RATING;
create table RATING
(Rating  VARCHAR2(2),
RatingDescription VARCHAR2(50));

insert into RATING values ('1','ENTERTAINMENT');
insert into RATING values ('2','BACKGROUND INFORMATION');
insert into RATING values ('3','RECOMMENDED');
insert into RATING values ('4','STRONGLY RECOMMENDED');
insert into RATING values ('5','REQUIRED READING');
commit;

REM ******************
REM The SHIPPING Table
REM ******************

drop table SHIPPING;
create table SHIPPING (
Client        VARCHAR2(13),
Weight        NUMBER
);

insert into SHIPPING values ('JOHNSON TOOL',59);
insert into SHIPPING values ('DAGG SOFTWARE',27);
insert into SHIPPING values ('TULLY ANDOVER',NULL);
commit;

REM ***************
REM The STOCK Table
REM ***************

drop table STOCK;
create table STOCK (
Company      VARCHAR2(20),
Symbol       VARCHAR2(6),
Industry     VARCHAR2(15),
CloseYesterday NUMBER(6,2),
CloseToday     NUMBER(6,2),
Volume         NUMBER
);

insert into STOCK values (
  'AD SPECIALTY', 'ADSP', 'ADVERTISING',  31.75, 31.75,
   18333876);
insert into STOCK values (
  'APPLE CANNERY', 'APCN', 'AGRICULTURE',  33.75, 36.50,
   25787229);
insert into STOCK values (
  'AT SPACE', 'ATR' , 'MEDICAL', 46.75, 48.00,
   11398323);
insert into STOCK values (
  'AUGUST ENTERPRISES', 'AGE', 'MEDICAL', 15.00, 15.00,
   12221711);
insert into STOCK values (
  'BRANDON ELLIPSIS', 'BELP', 'SPACE', 32.75, 33.50,
  25789769);
insert into STOCK values (
  'GENERAL ENTROPY','GENT', 'SPACE', 64.25, 66.00,
   7598562);
insert into STOCK values (
  'GENEVA ROCKETRY', 'GENR', 'SPACE', 22.75, 27.25,
   22533944);
insert into STOCK values (
  'HAYWARD ANTISEPTIC',  'HAYW', 'MEDICAL', 104.25,  106.00,
   3358561);
insert into STOCK values (
  'IDK', 'IDK', 'ELECTRONICS',  95.00, 95.25,
   9443523);
insert into STOCK values (
  'INDIA COSMETICS','INDI', 'COSMETICS', 30.75, 30.75,
   8134878);
insert into STOCK values (
  'ISAIAH JAMES STORAGE', 'IJS', 'TRANSPORTATION', 13.25, 13.75,
   22112171);
insert into STOCK values (
  'KDK AIRLINES',  'KDK', 'TRANSPORTATION', 85.00, 85.25,
   7481566);
insert into STOCK values (
  'KENTGEN BIOPHYSICS',  'KENT', 'MEDICAL', 18.25, 19.50,
   6636863);
insert into STOCK values (
  'LAVAY COSMETICS', 'LAVA', 'COSMETICS', 21.50, 22.00,
   3341542);
insert into STOCK values (
  'LOCAL DEVELOPMENT',  'LOCD', 'AGRICULTURE',  26.75, 27.25,
   2596934);
insert into STOCK values (
  'MAXTIDE'  , 'MAXT', 'TRANSPORTATION', 8.25, 8.00,
   2836893);
insert into STOCK values (
  'MBK COMMUNICATIONS', 'MBK', 'ADVERTISING',  43.25, 41.00,
   10022980);
insert into STOCK values (
  'MEMORY GRAPHICS', 'MEMG', 'ELECTRONICS',  15.50, 14.25,
   4557992);
insert into STOCK values (
'MICRO TOKEN', 'MICT', 'ELECTRONICS',  77.00, 76.50,
   25205667);
insert into STOCK values (
  'NANCY LEE FEATURES', 'NLF', 'ADVERTISING',  13.50, 14.25,
   14222692);
insert into STOCK values (
  'NORTHERN BOREAL', 'NBOR', 'SPACE', 26.75, 28.00,
   1348323);
insert into STOCK values (
  'OCKHAM SYSTEMS', 'OCKS', 'SPACE', 21.50, 22.00,
   7052990);
insert into STOCK values (
  'OSCAR COAL DRAYAGE', 'OCD', 'TRANSPORTATION', 87.00, 88.50,
   25798992);
insert into STOCK values (
  'ROBERT JAMES APPAREL', 'RJAP', 'GARMENT', 23.25, 24.00,
   19032481);
insert into STOCK values (
  'SOUP SENSATIONS','SOUP', 'AGRICULTURE',  16.25, 16.75,
   22574879);
insert into STOCK values (
  'WONDER LABS', 'WOND', 'SPACE', 5.00, 5.00,
   2553712);
commit;

REM *****************
REM The TROUBLE Table
REM *****************

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

insert into TROUBLE values ('PLEASANT LAKE',
  '21-MAR-01', 39.99, -1.31, 3.6);
insert into TROUBLE values ('PLEASANT LAKE',
  '22-JUN-01', 101.44, 86.2, 1.63);
insert into TROUBLE values ('PLEASANT LAKE',
  '23-SEP-01', 92.85, 79.6, 1.00003);
insert into TROUBLE values ('PLEASANT LAKE',
  '22-DEC-01', -17.445, -10.4, 2.4);
commit;

REM *****************
REM The TWONAME Table
REM *****************

drop table TWONAME;
create table TWONAME (
FirstName    VARCHAR2(25),
LastName     VARCHAR2(25)
);

REM *****************
REM The WEATHER Table
REM *****************

drop table WEATHER;
create table WEATHER (
City         VARCHAR2(11),
Temperature  NUMBER,
Humidity     NUMBER,
Condition    VARCHAR2(9)
);

insert into WEATHER values ('LIMA',45,79,'RAIN');
insert into WEATHER values ('PARIS',81,62,'CLOUDY');
insert into WEATHER values ('MANCHESTER',66,98,'FOG');
insert into WEATHER values ('ATHENS',97,89,'SUNNY');
insert into WEATHER values ('CHICAGO',66,88,'RAIN');
insert into WEATHER values ('SYDNEY',29,12,'SNOW');
insert into WEATHER values ('SPARTA',74,63,'CLOUDY');
commit;