--
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--


create table job      (
                         jobcode        NUMERIC (4) UNSIGNED
                                        NO DEFAULT
                                        NOT NULL NOT DROPPABLE
                                        HEADING 'Job/Code'
                        ,jobdesc        VARCHAR (18)
                                        DEFAULT ' '
                                        NOT NULL NOT DROPPABLE
                                        HEADING 'Job Description'
                        ,primary key    (jobcode) NOT DROPPABLE
                      );

create table employee (
                         empnum         NUMERIC (4) UNSIGNED
                                        NO DEFAULT
                                        NOT NULL NOT DROPPABLE
                                        HEADING 'Employee/Number'
                        ,first_name     CHARACTER (15)
                                        DEFAULT ' '
                                        NOT NULL NOT DROPPABLE
                                        HEADING 'First Name'
                        ,last_name      CHARACTER (20)
                                        DEFAULT ' '
                                        NOT NULL NOT DROPPABLE
                                        HEADING 'Last Name'
                        ,deptnum        NUMERIC (4)
                                        UNSIGNED
                                        NO DEFAULT
                                        NOT NULL NOT DROPPABLE
                                        HEADING 'Dept/Num'
                        ,jobcode        NUMERIC (4) UNSIGNED
                                        DEFAULT NULL
                                        HEADING 'Job/Code'
                        ,salary         NUMERIC (8, 2) UNSIGNED
                                        DEFAULT NULL
                                        HEADING 'Salary'
                        ,primary key    (empnum) NOT DROPPABLE
                     );

alter table employee
  add constraint empnum_constrnt
    check (empnum between 0001 and 9999);

create index xempname
  on employee (
                last_name
               ,first_name
              );

create index xempdept
  on employee (
                deptnum
              );

create view emplist
     as select
           empnum
          ,first_name
          ,last_name
          ,deptnum
          ,jobcode
        from employee;
  
create table dept     (
                         deptnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Dept/Num'
                        ,deptname        CHARACTER (12)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Dept/Name'
                        ,manager         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Mgr'
                        ,rptdept         NUMERIC (4) UNSIGNED
                                         DEFAULT 0
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Rpt/Dept'
                        ,location        VARCHAR (18)
                                         DEFAULT ' '
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Location'
                        ,primary key     (deptnum) NOT DROPPABLE
                      );

create index xdeptmgr
  on dept     (
                manager
              );

create index xdeptrpt
  on dept     (
                rptdept
              );

alter table dept
  add constraint mgrnum_constrnt
    check (manager between 0000 and 9999);

alter table dept
  add constraint deptnum_constrnt
    check (deptnum in (
                        1000
                       ,1500
                       ,2000
                       ,2500
                       ,3000
                       ,3100
                       ,3200
                       ,3300
                       ,3500
                       ,4000
                       ,4100
                       ,9000
                     ));

create view mgrlist  (
                        first_name
                       ,last_name
                       ,department
                     )
     as select
          first_name
         ,last_name
         ,deptname
        from
          dept
         ,employee
        where
          dept.manager = employee.empnum;

create table project  (
                         projcode        NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Project/Code'
                        ,empnum          NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Employee/Number'
                        ,projdesc        VARCHAR (18)
                                         DEFAULT ' '
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Project/Description'
                        ,start_date      DATE
                                         DEFAULT DATE '2002-07-01'
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Start/Date'
                        ,ship_timestamp  TIMESTAMP
                                         DEFAULT  TIMESTAMP '2002-08-01:12:00:00.000000'
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Time/Shipped'
                        ,est_complete    INTERVAL DAY
                                         DEFAULT INTERVAL '30'  DAY
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Estimated/Completion'
                        ,primary key     (projcode,empnum) NOT DROPPABLE
                      );

create table customer (
                         custnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Cust/Num'
                        ,custname        CHARACTER (18)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Customer Name'
                        ,street          CHARACTER (22)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Street'
                        ,city            CHARACTER (14)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'City'
                        ,state           CHARACTER (12)
                                         DEFAULT ' '
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'State'
                        ,postcode        CHARACTER (10)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Post Code'
                        ,credit          CHARACTER (2)
                                         DEFAULT 'C1'
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'CR'
                        ,primary key     (custnum) NOT DROPPABLE
                      );

create table orders   (
                         ordernum        NUMERIC (6) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Order/Num'
                        ,order_date      DATE
                                         DEFAULT DATE '2002-07-01'
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Order/Date'
                        ,deliv_date      DATE
                                         DEFAULT DATE '2002-08-01'
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Deliv/Date'
                        ,salesrep        NUMERIC (4) UNSIGNED
                                         DEFAULT 0
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Sales/Rep'
                        ,custnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Cust/Num'
                        ,primary key     (ordernum) NOT DROPPABLE
                      );

alter table orders
  add constraint date_constrnt
    check (deliv_date >= order_date);

create index xordrep
  on orders   (
                salesrep
              );

create index xordcus
  on orders   (
                custnum
              );

create view ordrep
     as select empnum
              ,last_name
              ,ordernum
              ,o.custnum
     from
        employee e
       ,orders o
       ,customer c
     where
         e.empnum = o.salesrep
       and
         o.custnum = c.custnum;

create index xcustnam
  on customer (
                custname
              );

create view custlist
     as select
           custnum
          ,custname
          ,street
          ,city
          ,state
          ,postcode
        from customer;

create table odetail  (
                         ordernum        NUMERIC (6) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Order/Num'
                        ,partnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Part/Num'
                        ,unit_price      NUMERIC (8, 2)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Unit/Price'
                        ,qty_ordered     NUMERIC (5) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Qty/Ord'
                        ,primary key     (ordernum,partnum) NOT DROPPABLE
                      );

create table parts    (
                         partnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Part/Num'
                        ,partdesc        CHARACTER (18)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Part Description'
                        ,price           NUMERIC (8, 2)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Price'
                        ,qty_available   NUMERIC (5)
                                         DEFAULT 0  
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Qty/Avail'
                        ,primary key     (partnum) NOT DROPPABLE
                      );

create index xpartdes
  on parts    (
                partdesc
              );

create table supplier (
                         suppnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Supp/Num'
                        ,suppname        CHARACTER (18)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Supplier Name'
                        ,street          CHARACTER (22)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Street'
                        ,city            CHARACTER (14)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'City'
                        ,state           CHARACTER (12)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'State'
                        ,postcode        CHARACTER (10)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Post Code'
                        ,primary key     (suppnum) NOT DROPPABLE
          );

create index xsuppnam
  on  supplier (
                 suppname
               );

create table partsupp (
                         partnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Part/Num'
                        ,suppnum         NUMERIC (4) UNSIGNED
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Supp/Num'
                        ,partcost        NUMERIC (8, 2)
                                         NO DEFAULT
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Part/Cost'
                        ,qty_received    NUMERIC (5) UNSIGNED
                                         DEFAULT 0
                                         NOT NULL NOT DROPPABLE
                                         HEADING 'Qty/Rec'
                        ,primary key     (partnum,suppnum) NOT DROPPABLE
                      );

create index xsupord
  on partsupp (
                suppnum
              );

create view view207  (
                        partnumber
                       ,partdescrpt
                       ,suppnumber
                       ,supplrname
                       ,partprice
                       ,qtyreceived
                     )
     as select
           x.partnum
          ,partdesc
          ,x.suppnum
          ,suppname
          ,partcost
          ,qty_received
        from
           partsupp x
          ,parts p
          ,supplier s
        where
            x.partnum  = p.partnum
          and
            x.suppnum = s.suppnum;

create view view207n (
                        partnumber
                       ,partdescrpt
                       ,suppnumber
                       ,supplrname
                       ,partprice
                       ,qtyreceived
                     )
     as select
          x.partnum
         ,p.partdesc
         ,s.suppnum
         ,s.suppname
         ,x.partcost
         ,x.qty_received
        from
          supplier s left join partsupp x
                             on s.suppnum = x.suppnum
                      left join parts p
                             on x.partnum = p.partnum;

create view viewcust (
                        custnumber
                       ,cusname
                       ,ordernum
                     )
     as select
          c.custnum
         ,c.custname
         ,o.ordernum
        from
          customer c left join orders o
                             on c.custnum = o.custnum;

create view viewcs
     as select
          custname
        from customer
      union
        select
          suppname
        from supplier;

 create table partloc (
                          loc_code      CHARACTER (3)
                                        NO DEFAULT
                                        NOT NULL
                                        HEADING 'Loc/Code'
                         ,partnum       NUMERIC (4) UNSIGNED
                                        NO DEFAULT
                                        NOT NULL
                                        HEADING 'Part/Num'
                         ,qty_on_hand   NUMERIC (7)
                                        DEFAULT 0
                                        HEADING 'Qty/On/Hand'
                         ,primary key   (loc_code,partnum)
                       );

insert into job 
   values ( 100, 'MANAGER' ),
          ( 200, 'PRODUCTION SUPV' ),
          ( 250, 'ASSEMBLER' ),
          ( 300, 'SALESREP' ),
          ( 400, 'SYSTEM ANALYST' ),
          ( 420, 'ENGINEER' ),
          ( 450, 'PROGRAMMER' ),
          ( 500, 'ACCOUNTANT' ),
          ( 600, 'ADMINISTRATOR' ),
          ( 900, 'SECRETARY' );

insert into employee 
   values (   1,'ROGER'   ,'GREEN'     ,9000, 100,175500.00 ),
          (  23,'JERRY'   ,'HOWARD'    ,1000, 100,137000.10 ),
          (  29,'JANE'    ,'RAYMOND'   ,3000, 100,136000.00 ),
          (  32,'THOMAS'  ,'RUDLOFF'   ,2000, 100,138000.40 ),
          (  39,'KLAUS '  ,'SAFFERT'   ,3200, 100, 75000.00 ),
          (  43,'PAUL'    ,'WINTER'    ,3100, 100, 90000.00 ),
          (  65,'RACHEL'  ,'MCKAY'     ,4000, 100,118000.00 ),
          (  72,'GLENN'   ,'THOMAS'    ,3300, 100, 80000.00 ),
          (  75,'TIM'     ,'WALKER'    ,3000, 300, 32000.00 ),
          (  87,'ERIC'    ,'BROWN'     ,4000, 400, 89000.00 ),
          (  89,'PETER'   ,'SMITH'     ,3300, 300, 37000.40 ),
          (  93,'DONALD'  ,'TAYLOR'    ,3100, 300, 33000.00 ),
          ( 104,'DAVID'   ,'STRAND'    ,4000, 400, 69000.00 ),
          ( 109,'STEVE'   ,'COOK'      ,4000, 400, 68000.00 ),
          ( 111,'SHERRIE' ,'WONG'      ,3500, 100, 70000.00 ),
          ( 178,'JOHN'    ,'CHOU'      ,3500, 900, 28000.00 ),
          ( 180,'MANFRED' ,'CONRAD'    ,4000, 450, 32000.00 ),
          ( 201,'JIM'     ,'HERMAN'    ,3000, 300, 19000.00 ),
          ( 202,'LARRY'   ,'CLARK'     ,1000, 500, 25000.75 ),
          ( 203,'KATHRYN' ,'HALL'      ,4000, 400, 96000.00 ),
          ( 205,'GINNY'   ,'FOSTER'    ,3300, 900, 30000.00 ),
          ( 206,'DAVE'    ,'FISHER'    ,3200, 900, 25000.00 ),
          ( 207,'MARK'    ,'FOLEY'     ,4000, 420, 33000.00 ),
          ( 208,'SUE'     ,'CRAMER'    ,1000, 900, 19000.00 ),
          ( 209,'SUSAN'   ,'CHAPMAN'   ,1500, 900, 17000.00 ),
          ( 210,'RICHARD' ,'BARTON'    ,1000, 500, 29000.00 ),
          ( 211,'JIMMY'   ,'SCHNEIDER' ,1500, 600, 26000.00 ),
          ( 212,'JONATHAN','MITCHELL'  ,1500, 600, 32000.00 ),
          ( 213,'ROBERT'  ,'WHITE'     ,1500, 100, 90000.00 ),
          ( 214,'JULIA'   ,'KELLY'     ,1000, 500, 50000.00 ),
          ( 215,'Walter'  ,'LANCASTER' ,4000, 450, 33000.50 ),
          ( 216,'JOHN'    ,'JONES'     ,4000, 450, 40000.00 ),
          ( 217,'MARLENE' ,'BONNY'     ,4000, 900, 24000.90 ),
          ( 218,'GEORGE'  ,'FRENCHMAN' ,4000, 420, 36000.00 ),
          ( 219,'DAVID'   ,'TERRY'     ,2000, 250, 27000.12 ),
          ( 220,'JOHN'    ,'HUGHES'    ,3200, 300, 33000.10 ),
          ( 221,'OTTO'    ,'SCHNABL'   ,3200, 300, 33000.00 ),
          ( 222,'MARTIN'  ,'SCHAEFFER' ,3200, 300, 31000.00 ),
          ( 223,'HERBERT' ,'KARAJAN'   ,3200, 300, 29000.00 ),
          ( 224,'MARIA'   ,'JOSEF'     ,4000, 420, 18000.10 ),
          ( 225,'KARL'    ,'HELMSTED'  ,4000, 450, 32000.00 ),
          ( 226,'HEIDI'   ,'WEIGL'     ,3200, 300, 22000.00 ),
          ( 227,'XAVIER'  ,'SEDLEMEYER',3300, 300, 30000.00 ),
          ( 228,'PETE'    ,'WELLINGTON',3100, 300, 32000.20 ),
          ( 229,'GEORGE'  ,'STRICKER'  ,3100, 300, 32222.00 ),
          ( 230,'ROCKY'   ,'LEWIS'     ,2000, 200, 24000.00 ),
          ( 231,'HERB'    ,'ALBERT'    ,3300, 300, 33000.00 ),
          ( 232,'THOMAS'  ,'SPINNER'   ,4000, 450, 45000.00 ),
          ( 233,'TED'     ,'MCDONALD'  ,2000, 250, 29000.00 ),
          ( 234,'MARY'    ,'MILLER'    ,2500, 100, 56000.00 ),
          ( 235,'MIRIAM'  ,'KING'      ,2500, 900, 18000.00 ),
          ( 321,'BILL'    ,'WINN'      ,2000, 900, 32000.00 ),
          ( 337,'DINAH'   ,'CLARK'     ,9000, 900, 37000.00 ),
          ( 343,'ALAN'    ,'TERRY'     ,3000, 900, 39500.00 ),
          ( 557,'BEN'     ,'HENDERSON' ,4000, 400, 65000.00 ),
          ( 568,'JESSICA' ,'CRINER'    ,3500, 300, 39500.00 ),
          ( 990,'thomas'  ,'stibbs'    ,3500,null,null      ),
          ( 991,'Wayne'   ,'O''Neil'   ,3500,null,null      ),
          ( 992,'Barry'   ,'Kinney'    ,3500,null,null      ),
          ( 993,'Paul'    ,'Buskett'   ,3100,null,null      ),
          ( 994,'Emmy'    ,'Buskett'   ,3100,null,null      ),
          ( 995,'Walt'    ,'Farley'    ,3100,null,null      );

insert into dept 
   values ( 1000,'FINANCE'     ,  23,9000,'CHICAGO'     ),
          ( 1500,'PERSONNEL'   , 213,1000,'CHICAGO'     ),
          ( 2000,'INVENTORY'   ,  32,9000,'LOS ANGELES' ),
          ( 2500,'SHIPPING'    , 234,2000,'PHOENIX'     ),
          ( 3000,'MARKETING'   ,  29,9000,'NEW YORK'    ),
          ( 3100,'CANADA SALES',  43,3000,'TORONTO'     ),
          ( 3200,'GERMNY SALES',  39,3000,'FRANKFURT'   ),
          ( 3300,'ENGLND SALES',  72,3000,'LONDON'      ),
          ( 3500,'ASIA SALES'  , 111,3000,'HONG KONG'   ),
          ( 4000,'RESEARCH'    ,  65,9000,'NEW YORK'    ),
          ( 4100,'PLANNING'    ,  87,4000,'NEW YORK'    ),
          ( 9000,'CORPORATE'   ,   1,9000,'CHICAGO'     );

insert into project 
   values ( 1000,213,'SALT LAKE CITY'
           ,DATE '2002-04-10'
           ,TIMESTAMP '2002-04-21:08:15:00.00'
           ,INTERVAL '15' DAY ),

          ( 1000,211,'SALT LAKE CITY'
           ,DATE '2002-04-10'
           ,TIMESTAMP '2002-04-21:08:15:00.00'
           ,INTERVAL '15' DAY ),

          ( 1000,23,'SALT LAKE CITY'
           ,DATE '2002-04-10'
           ,TIMESTAMP '2002-04-21:08:15:00.00'
           ,INTERVAL '15' DAY ),

          ( 1000,1,'SALT LAKE CITY'
           ,DATE '2002-04-10'
           ,TIMESTAMP '2002-04-21:08:15:00.00'
           ,INTERVAL '15' DAY ),

          ( 2000,227,'ROSS PRODUCTS'
           ,DATE '2002-06-10'
           ,TIMESTAMP '2002-07-21:08:30:00.0000'
           ,INTERVAL '30' DAY ),

          ( 2000,109,'ROSS PRODUCTS'
           ,DATE '2002-06-10'
           ,TIMESTAMP '2002-07-21:08:30:00.0000'
           ,INTERVAL '30' DAY ),

          ( 2000,215,'ROSS PRODUCTS'
           ,DATE '2002-06-10'
           ,TIMESTAMP '2002-07-21:08:30:00.0000'
           ,INTERVAL '30' DAY ),

          ( 2000,65,'ROSS PRODUCTS'
           ,DATE '2002-06-10'
           ,TIMESTAMP '2002-07-21:08:30:00.0000'
           ,INTERVAL '30' DAY ),

          ( 2500,65,'MONTANA TOOLS'
           ,DATE '2002-10-10'
           ,TIMESTAMP '2002-12-21:09:00:00.0000'
           ,INTERVAL '60' DAY ),

          ( 2500,207,'MONTANA TOOLS'
           ,DATE '2002-10-10'
           ,TIMESTAMP '2002-12-21:09:00:00.0000'
           ,INTERVAL '60' DAY ),

          ( 2500,232,'MONTANA TOOLS'
           ,DATE '2002-10-10'
           ,TIMESTAMP '2002-12-21:09:00:00.0000'
           ,INTERVAL '60' DAY ),

          ( 2500,180,'MONTANA TOOLS'
           ,DATE '2002-10-10'
           ,TIMESTAMP '2002-12-21:09:00:00.0000'
           ,INTERVAL '60' DAY ),

          ( 2500,93,'MONTANA TOOLS'
           ,DATE '2002-10-10'
           ,TIMESTAMP '2002-12-21:09:00:00.0000'
           ,INTERVAL '60' DAY ),

          ( 3000,65,'AHAUS TOOL/SUPPLY'
           ,DATE '2002-08-21'
           ,TIMESTAMP '2002-10-21:08:10:00.0000'
           ,INTERVAL '60' DAY ),

          ( 3000,221,'AHAUS TOOL/SUPPLY'
           ,DATE '2002-08-21'
           ,TIMESTAMP '2002-10-21:08:10:00.0000'
           ,INTERVAL '60' DAY ),

          ( 3000,226,'AHAUS TOOL/SUPPLY'
           ,DATE '2002-08-21'
           ,TIMESTAMP '2002-10-21:08:10:00.0000'
           ,INTERVAL '60' DAY ),

          ( 3000,224,'AHAUS TOOL/SUPPLY'
           ,DATE '2002-08-21'
           ,TIMESTAMP '2002-10-21:08:10:00.0000'
           ,INTERVAL '60' DAY ),

          ( 3000,225,'AHAUS TOOL/SUPPLY'
           ,DATE '2002-08-21'
           ,TIMESTAMP '2002-10-21:08:10:00.0000'
           ,INTERVAL '60' DAY ),

          ( 4000,75,'THE WORKS'
           ,DATE '2002-09-21'
           ,TIMESTAMP '2002-10-21:10:15:00.0000'
           ,INTERVAL '30' DAY ),

          ( 4000,29,'THE WORKS'
           ,DATE '2002-09-21'
           ,TIMESTAMP '2002-10-21:10:15:00.0000'
           ,INTERVAL '30' DAY ),

          ( 4000,231,'THE WORKS'
           ,DATE '2002-09-21'
           ,TIMESTAMP '2002-10-21:10:15:00.0000'
           ,INTERVAL '30' DAY ),

          ( 4000,228,'THE WORKS'
           ,DATE '2002-09-21'
           ,TIMESTAMP '2002-10-21:10:15:00.0000'
           ,INTERVAL '30' DAY ),

          ( 4000,223,'THE WORKS'
           ,DATE '2002-09-21'
           ,TIMESTAMP '2002-10-21:10:15:00.0000'
           ,INTERVAL '30' DAY ),

          ( 4000,568,'THE WORKS'
           ,DATE '2002-09-21'
           ,TIMESTAMP '2002-10-21:10:15:00.0000'
           ,INTERVAL '30' DAY ),

          ( 5000,65,'ASIA PROJECT'
           ,DATE '2002-09-28'
           ,TIMESTAMP '2002-10-28:09:25:01.1111'
           ,INTERVAL '30' DAY ),

          ( 5000,568,'ASIA PROJECT'
           ,DATE '2002-09-28'
           ,TIMESTAMP '2002-10-28:09:25:01.1111'
           ,INTERVAL '30' DAY ),

          ( 5000,557,'ASIA PROJECT'
           ,DATE '2002-09-28'
           ,TIMESTAMP '2002-10-28:09:25:01.1111'
           ,INTERVAL '30' DAY ),

          ( 5000,216,'ASIA PROJECT'
           ,DATE '2002-09-28'
           ,TIMESTAMP '2002-10-28:09:25:01.1111'
           ,INTERVAL '30' DAY ),

          ( 5000,203,'ASIA PROJECT'
           ,DATE '2002-09-28'
           ,TIMESTAMP '2002-10-28:09:25:01.1111'
           ,INTERVAL '30' DAY ),

          ( 5000,218,'ASIA PROJECT'
           ,DATE '2002-09-28'
           ,TIMESTAMP '2002-10-28:09:25:01.1111'
           ,INTERVAL '30' DAY );

insert into customer 
   values (  21,'CENTRAL UNIVERSITY','UNIVERSITY WAY'
           ,'PHILADELPHIA' ,'PENNSYLVANIA','19104'  ,'A1' ),

          ( 123,'BROWN MEDICAL CO'  ,'100 CALIFORNIA STREET'
           ,'SAN FRANCISCO','CALIFORNIA'  ,'94944'   ,'C2' ),
 
          ( 143,'STEVENS SUPPLY'    ,'2020 HARRIS STREET'
           ,'DENVER'       ,'COLORADO'    ,'80734'   ,'A2' ),
 
          ( 324,'PREMIER INSURANCE' ,'3300 WARBASH'
           ,'LUBBOCK'      ,'TEXAS'       ,'76308'   ,'A1' ),

          ( 543,'FRESNO STATE BANK' ,'2300 BROWN BLVD'
           ,'FRESNO'       ,'CALIFORNIA'  ,'93921'   ,'B3' ),

          ( 926,'METALL-AG.'        ,'12 WAGNERRING'
           ,'FRANKFURT'    ,'WEST GERMANY','34'      ,'D4' ),

          (1234,'DATASPEED'         ,'300 SAN GABRIEL WAY'
           ,'NEW YORK'     ,'NEW YORK'    ,'10014'   ,'C1' ),

          (3210,'BESTFOOD MARKETS'  ,'3333 PHELPS STREET'
           ,'LINCOLN'      ,'NEBRASKA'    ,'68134'   ,'A4' ),

          (3333,'NATIONAL UTILITIES','6500 TRANS-CANADIENNE'
           ,'QUEBEC'       ,'CANADA'      ,'H4T 1X4' ,'A1' ),

          (5635,'ROYAL CHEMICALS'   ,'45 NEW BROAD STREET'
           ,'LONDON'       ,'ENGLAND'     ,'EC2M 1NH','B2' ),

          (7654,'MOTOR DISTRIBUTING','2345 FIRST STREET'
           ,'CHICAGO'      ,'ILLINOIS'    ,'60610'   ,'E4' ),

          (7777,'SLEEPWELL HOTELS'  ,'9000 PETERS AVENUE'
           ,'DALLAS'       ,'TEXAS'       ,'75244'   ,'B1' ),

          (9000,'bunknought inn'    ,'4738 ralph street'
           ,'bayonne'      ,'new jersey'  ,'09520'   ,'c1' ),

          (9010,'hotel oregon'      ,'333 portland ave.'
           ,'medford'      ,'oregon'      ,'97444'   ,'c2' ),

          (9033,'art supplies, inc.','22 sweet st.'
           ,'pittsburgh','penna.'         ,'08333'   ,'c3' );

insert into orders 
   values ( 100210,DATE '2003-04-10',DATE '2003-04-10', 220,1234 ),
          ( 100250,DATE '2003-01-23',DATE '2003-06-15', 220,7777 ),
          ( 101220,DATE '2003-07-21',DATE '2003-12-15', 221,5635 ),
          ( 200300,DATE '2003-02-06',DATE '2003-07-01', 222, 926 ),
          ( 200320,DATE '2003-02-17',DATE '2003-07-20', 223,  21 ),
          ( 200490,DATE '2003-03-19',DATE '2003-11-01', 226, 123 ),
          ( 300350,DATE '2003-03-03',DATE '2003-08-10', 231, 543 ),
          ( 300380,DATE '2003-03-19',DATE '2003-08-20', 226, 123 ),
          ( 400410,DATE '2003-03-27',DATE '2003-09-01', 227,7654 ),
          ( 500450,DATE '2003-04-20',DATE '2003-09-15', 220, 324 ),
          ( 600480,DATE '2003-05-12',DATE '2003-10-10', 226,3333 ),
          ( 700510,DATE '2003-06-01',DATE '2003-10-20', 229, 143 ),
          ( 800660,DATE '2003-10-09',DATE '2003-11-01', 568,3210 );

insert into odetail 
   values ( 100210, 244,  3500.00,    3 ),
          ( 100210,2001,  1100.00,    3 ),
          ( 100210,2403,   620.00,    6 ),
          ( 100210,5100,   150.00,   10 ),
          ( 100250, 244,  3500.00,    4 ),
          ( 100250,5103,   400.00,   10 ),
          ( 100250,6301,   245.00,   15 ),
          ( 100250,6500,    95.00,   10 ),
          ( 101220, 255,  3900.00,   10 ),
          ( 101220,5103,   400.00,    3 ),
          ( 101220,7102,   275.00,    7 ),
          ( 101220,7301,   425.00,    8 ),
          ( 200300, 244,  3500.00,    8 ),
          ( 200300,2001,  1000.00,   10 ),
          ( 200300,2002,  1400.00,   10 ),
          ( 200320,5504,   165.00,    5 ),
          ( 200320,6201,   195.00,   16 ),
          ( 200320,6301,   245.00,    6 ),
          ( 200320,6400,   540.00,    7 ),
          ( 200490,3210,   715.00,    1 ),
          ( 200490,5505,   350.00,    1 ),
          ( 300350, 244,  2800.00,   20 ),
          ( 300350,5100,   150.00,    5 ),
          ( 300350,5110,   525.00,   12 ),
          ( 300350,6301,   245.00,    5 ),
          ( 300350,6400,   550.00,    5 ),
          ( 300380, 244,  3000.00,    6 ),
          ( 300380,2402,   320.00,   12 ),
          ( 300380,2405,   760.00,    8 ),
          ( 400410, 212,  2450.00,   12 ),
          ( 400410, 255,  3800.00,   12 ),
          ( 400410,2001,  1000.00,   36 ),
          ( 400410,6301,   240.00,   48 ),
          ( 400410,6400,   500.00,   70 ),
          ( 400410,7301,   415.00,   36 ),
          ( 500450, 212,  2500.00,    8 ),
          ( 500450, 255,  3900.00,   12 ),
          ( 500450,2001,  1100.00,   16 ),
          ( 500450,2002,  1500.00,   16 ),
          ( 500450,2402,   330.00,   48 ),
          ( 600480,2001,  1000.00,   60 ),
          ( 600480,2002,  1450.00,   20 ),
          ( 600480,2003,  1900.00,   40 ),
          ( 600480,3103,  4000.00,   40 ),
          ( 600480,3205,   625.00,   20 ),
          ( 600480,5100,   135.00,   60 ),
          ( 600480,5103,   390.00,   20 ),
          ( 600480,7301,   425.00,   40 ),
          ( 700410,2003,  1900.00,   65 ),
          ( 700410,2403,   650.00,   10 ),
          ( 700510, 255,  4000.00,    4 ),
          ( 700510,6500,    95.00,    8 ),
          ( 700510,7102,   275.00,    5 ),
          ( 800660, 244,  3000.00,    6 ),
          ( 800660,2001,  1000.00,   30 ),
          ( 800660,2403,   600.00,   48 ),
          ( 800660,2405,   795.00,   10 ),
          ( 800660,3201,   525.00,    6 ),
          ( 800660,3205,   600.00,   18 ),
          ( 800660,3210,   715.00,    6 ),
          ( 800660,4102,    26.00,  130 ),
          ( 800660,5100,   150.00,   12 ),
          ( 800660,5101,   200.00,    6 ),
          ( 800660,5110,   490.00,   48 ),
          ( 800660,5504,   165.00,   18 ),
          ( 800660,6201,   195.00,    6 ),
          ( 800660,6301,   235.00,   24 ),
          ( 800660,6400,   525.00,   30 ),
          ( 800660,6401,   700.00,   36 ),
          ( 800660,6500,    95.00,   22 ),
          ( 800660,7102,   275.00,    6 ),
          ( 800660,7301,   425.00,   12 );

insert into parts 
   values ( 186,'186 MegaByte Disk' ,186186.86,  186 ),
          ( 212,'PC SILVER, 20 MB'  ,  2500.00, 3525 ),
          ( 244,'PC GOLD, 30 MB'    ,  3000.00, 4426 ),
          ( 255,'PC DIAMOND, 60 MB' ,  4000.00, 3321 ),
          (2001,'GRAPHIC PRINTER,M1',  1100.00, 2100 ),
          (2002,'GRAPHIC PRINTER,M2',  1500.00, 3220 ),
          (2003,'GRAPHIC PRINTER,M3',  2000.00, 2200 ),
          (2402,'DAISY PRINTER,T1'  ,   350.00, 4425 ),
          (2403,'DAISY PRINTER,T2'  ,   650.00, 3312 ),
          (2405,'DAISY PRINTER, T3' ,   795.00, 2712 ),
          (3103,'LASER PRINTER, X1' ,  4200.00, 3300 ),
          (3201,'HARD DISK 20 MB'   ,   525.00, 4436 ),
          (3205,'HARD DISK 30 MB'   ,   625.00, 2209 ),
          (3210,'HARD DISK 40 MB'   ,   715.00, 3314 ),
          (4102,'DISKETTE HD, BOX'  ,    28.00, 6540 ),
          (5100,'MONITOR BW, TYPE 1',   150.00, 3237 ),
          (5101,'MONITOR BW, TYPE 2',   200.00, 2400 ),
          (5103,'MONITOR COLOR, M1' ,   400.00, 3328 ),
          (5110,'MONITOR COLOR, M2' ,   525.00, 3236 ),
          (5504,'MEMORY CARD, 512KB',   165.00, 2630 ),
          (5505,'MEMORY CARD, 1 MB' ,   315.00, 3830 ),
          (6201,'GRAPHIC CARD, LR'  ,   195.00, 2306 ),
          (6301,'GRAPHIC CARD, HR'  ,   245.00, 2331 ),
          (6400,'STREAMING TAPE,M20',   550.00, 1268 ),
          (6401,'STREAMING TAPE,M60',   725.00, 1308 ),
          (6500,'DISK CONTROLLER'   ,    95.00, 2532 ),
          (6603,'PRINTER CONTROLLER',    45.00,  430 ),
          (7102,'SMART MODEM, 1200' ,   275.00, 2200 ),
          (7301,'SMART MODEM, 2400' ,   425.00, 2332 );

insert into supplier 
   values (   1,'NEW COMPUTERS INC'   ,'1800 KING ST.'
           ,'SAN FRANCISCO','CALIFORNIA'  ,'94112' ),

          (   2,'DATA TERMINAL INC'   ,'2000 BAKER STREET'
           ,'LAS VEGAS'    ,'NEVADA'      ,'66134' ),

          (   3,'HIGH DENSITY INC'    ,'7600 EMERSON'
           ,'NEW YORK'     ,'NEW YORK'    ,'10230' ),
 
          (   6,'MAGNETICS INC'       ,'1000 INDUSTRY DRIVE'
           ,'LEXINGTON'    ,'MASS'        ,'02159' ),

          (   8,'ATTRACTIVE CORP'     ,'7777 FOUNTAIN WAY'
           ,'CHICAGO'      ,'ILLINOIS'    ,'60610' ),

          (  10,'LEVERAGE INC'        ,'6000 LINCOLN LANE'
           ,'DENVER'       ,'COLORADO'    ,'80712' ),

          (  15,'DATADRIVE CORP'      ,'100 MAC ARTHUR'
           ,'DALLAS'       ,'TEXAS'       ,'75244' ),
 
          (  20,'Macadam''S PC''s'    ,'106 River Road'
           ,'New Orleans'  ,'Louisiana'   ,'67890' ),

          (  25,'Schroeder''s Ltd'    ,'212 Strasse Blvd West'
           ,'Hamburg'      ,'Rhode Island','22222' ),

          (  30,'O''Donnell''s Drives','729 West Palm Beach   '
           ,'San Antonio'  ,'Texas'       ,'78344' ),
        
          (  35,'Mac''Murphys PC''s'  ,'93323 Alemeda'
           ,'Menlo Park'   ,'California'  ,'94025' ),
 
          (  36,'MAC''MURPHYS PCB''s' ,'93323 Alemeda Suite B'
           ,'Menlo Park'   ,'California'  ,'94025' ),

          (  90,'laser jets inc'      ,'284 blue ridge way'
           ,'levittown'    ,'penna.'      ,'09520' ),

          (  92,'watercolors'         ,'84 north grand avenue'
           ,'menlo park'   ,'california'  ,'94025' ),

          (  95,'application do''ers' ,'2846 yellowwood drive'
           ,'wayland'      ,'mass'        ,'02158' ),

          (  99,'terminals, inc.'     ,'2 longfellow way'
           ,'heightstown'  ,'nj'          ,'08520' ),

          ( 186,'186 Disk Makers'     ,'186 Dis Way'
           ,'Dat Way'      ,'Wisconsin'   ,'00186' );

insert into partsupp 
   values ( 212,   1,  2000.00,   20 ),
          ( 212,   3,  1900.00,   35 ),
          ( 244,   1,  2400.00,   50 ),
          ( 244,   2,  2200.00,   66 ),
          ( 255,   1,  3300.00,   35 ),
          ( 255,   3,  3000.00,   46 ),
          (2001,   1,   700.00,  100 ),
          (2001,   2,   750.00,   55 ),
          (2002,   1,  1000.00,  120 ),
          (2002,   6,  1100.00,   20 ),
          (2003,   1,  1300.00,  100 ),
          (2003,   2,  1400.00,   50 ),
          (2003,  10,  1450.00,   50 ),
          (2402,   1,   200.00,   35 ),
          (2403,   1,   300.00,  200 ),
          (2405,   1,   500.00,   40 ),
          (2405,   6,   450.00,   50 ),
          (3103,   1,  3200.00,  200 ),
          (3103,  15,  3300.00,  100 ),
          (3201,   1,   380.00,   36 ),
          (3205,   1,   425.00,  150 ),
          (3210,   6,   470.00,   10 ),
          (3210,  15,   450.00,   25 ),
          (4102,   6,    20.00,  115 ),
          (4102,   8,    19.00,  140 ),
          (4102,  15,    21.00,   30 ),
          (5100,   6,   100.00,   50 ),
          (5100,   8,   105.00,   40 ),
          (5100,  15,    95.00,   60 ),
          (5101,   8,   135.00,   33 ),
          (5101,  15,   125.00,   43 ),
          (5103,   8,   265.00,   20 ),
          (5103,  15,   250.00,   58 ),
          (5110,   1,   335.00,  100 ),
          (5110,   2,   350.00,   36 ),
          (5504,   2,    85.00,   10 ),
          (5504,   6,    75.00,   10 ),
          (5504,  15,    78.00,   10 ),
          (5505,  15,   200.00,  100 ),
          (6201,   1,   100.00,  110 ),
          (6301,   1,   150.00,  230 ),
          (6400,   1,   390.00,   50 ),
          (6401,   2,   500.00,   20 ),
          (6401,   3,   480.00,   38 ),
          (6500,   2,    60.00,  140 ),
          (6500,   3,    65.00,   32 ),
          (6603,   2,    25.00,  150 ),
          (7102,  10,   165.00,  100 ),
          (7301,   1,   300.00,   32 );

insert into partloc 
   values ('A10',2001,    800 ),
          ('A21', 255,     21 ),
          ('A34',6201,      0 ),
          ('A35',6301,      0 ),
          ('A36',6400,     34 ),
          ('A66',6603,    300 ),
          ('A67',6401,    454 ),
          ('A78', 244,     43 ),
          ('A78',5505,    100 ),
          ('A87', 212,     18 ),
          ('A88',2403,    735 ),
          ('A88',5504,     30 ),
          ('A94',3205,    200 ),
          ('A98',5110,    510 ),
          ('G11',2002,     20 ),
          ('G34',6201,    106 ),
          ('G35',6301,    331 ),
          ('G36',6400,   1034 ),
          ('G43',5100,     77 ),
          ('G45',4102,     69 ),
          ('G65',3201,     36 ),
          ('G68',6500,   1132 ),
          ('G76',2405,     42 ),
          ('G76',7301,     32 ),
          ('G78',5505,      0 ),
          ('G87', 212,     20 ),
          ('G87',3103,      0 ),
          ('G87',3210,     44 ),
          ('G87',2402,      0 ),
          ('G88',2403,     32 ),
          ('G88',5504,      0 ),
          ('G89',5101,     86 ),
          ('G94',3205,     59 ),
          ('G98',5103,     28 ),
          ('G98',5110,    136 ),
          ('P10',2001,      0 ),
          ('P12',2002,    200 ),
          ('P12',2003,      0 ),
          ('P15',2003,    200 ),
          ('P66',6603,     40 ),
          ('P67',6401,     54 ),
          ('P68',6500,      0 ),
          ('P76',7102,    200 ),
          ('P78', 244,     23 ),
          ('P87',3103,    300 );

