CREATE TABLE trafodion.sales.orders
( ordernum   NUMERIC (6) UNSIGNED NO DEFAULT                NOT NULL
, order_date DATE                 DEFAULT DATE '2011-07-01' NOT NULL
, deliv_date DATE                 DEFAULT DATE '2011-08-01' NOT NULL
, salesrep   NUMERIC (4) UNSIGNED DEFAULT 0                 NOT NULL
, custnum    NUMERIC (4) UNSIGNED NO DEFAULT                NOT NULL
, PRIMARY KEY ( ordernum )
) ;

ALTER TABLE trafodion.sales.orders
   ADD CONSTRAINT trafodion.sales.date_constrnt CHECK ( deliv_date >= order_date )
   ;

CREATE INDEX xordrep ON orders
( salesrep
) ;

CREATE INDEX xordcus ON orders
( custnum
) ;

CREATE VIEW trafodion.sales.ordrep AS SELECT
  empnum
, last_name
, ordernum
, o.custnum
FROM
  trafodion.persnl.employee e
, trafodion.sales.orders o
, trafodion.sales.customer c
WHERE e.empnum = o.salesrep
  AND o.custnum = C.custnum
;

CREATE INDEX xcustnam ON customer
(
custname
) ;

CREATE VIEW trafodion.sales.custlist AS SELECT
  custnum
, custname
, street
, city
, state
, postcode
FROM trafodion.sales.customer
;

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

UPDATE STATISTICS FOR TABLE trafodion.sales.orders ON EVERY COLUMN ;