DROP TABLE order3;
DROP TABLE customer;
DROP TABLE agent;
DROP TABLE product;

CREATE TABLE customer (
  cid    CHAR(5) PRIMARY KEY,
  cname  VARCHAR(32) NOT NULL,
  city   VARCHAR(32),
  discnt REAL NOT NULL CHECK( discnt >= 0.00 AND discnt <= 20.00 ) );

INSERT INTO customer VALUES( 'c001', 'TipTop', 'Duluth', 10.00 );
INSERT INTO customer VALUES( 'c002', 'Basics', 'Dallas', 12.00 );
INSERT INTO customer VALUES( 'c003', 'Allied', 'Dallas',  8.00 );
INSERT INTO customer VALUES( 'c004', 'ACME',   'Duluth',  8.00 );
INSERT INTO customer VALUES( 'c006', 'ACME',   'Kyoto',   0.00 );

CREATE TABLE agent (
  aid     CHAR(5) PRIMARY KEY,
  aname   VARCHAR(32) NOT NULL,
  city    VARCHAR(32),
  percent INTEGER NOT NULL CHECK( percent >= 0 ) );

INSERT INTO agent VALUES( 'a01', 'Smith', 'New York', 6 );
INSERT INTO agent VALUES( 'a02', 'Jones', 'Newark',   6 );
INSERT INTO agent VALUES( 'a03', 'Brown', 'Tokyo',    7 );
INSERT INTO agent VALUES( 'a04', 'Gray',  'New York', 6 );
INSERT INTO agent VALUES( 'a05', 'Otasi', 'Duluth',   5 );
INSERT INTO agent VALUES( 'a06', 'Smith', 'Dallas',   5 );

CREATE TABLE product (
  pid      CHAR(5) PRIMARY KEY,
  pname    VARCHAR(32) NOT NULL,
  city     VARCHAR(32),
  quantity INTEGER NOT NULL CHECK( quantity >= 0 ),
  price    REAL NOT NULL CHECK( price >= 0.0 ) );

INSERT INTO product VALUES( 'p01', 'comb', 'Dallas', 111400, 0.50 );
INSERT INTO product VALUES( 'p02', 'comb', 'Newark', 203000, 0.50 );
INSERT INTO product VALUES( 'p03', 'comb', 'Duluth', 150600, 1.00 );
INSERT INTO product VALUES( 'p04', 'comb', 'Duluth', 125300, 1.00 );
INSERT INTO product VALUES( 'p05', 'comb', 'Dallas', 221400, 1.00 );
INSERT INTO product VALUES( 'p06', 'comb', 'Dallas', 123100, 2.00 );
INSERT INTO product VALUES( 'p07', 'comb', 'Newark', 100500, 1.00 );

-- order is a SQL reserved word.
CREATE TABLE order3 (
  orderno INTEGER PRIMARY KEY,
  month   CHAR(5),
  cid     CHAR(5) NOT NULL,
  aid     CHAR(5) NOT NULL,
  pid     CHAR(5) NOT NULL,
  qty     INTEGER NOT NULL CHECK( qty >= 0 ),
  dollar  REAL NOT NULL CHECK( dollar >= 0.0 ),
  FOREIGN KEY ( cid ) REFERENCES customer( cid ) ON DELETE CASCADE,
  FOREIGN KEY ( aid ) REFERENCES agent   ( aid ) ON DELETE CASCADE,
  FOREIGN KEY ( pid ) REFERENCES product ( pid ) ON DELETE CASCADE );

INSERT INTO order3 VALUES( 1011, 'jan', 'c001', 'a01', 'p01', 1000,  450.00 );
INSERT INTO order3 VALUES( 1012, 'jan', 'c001', 'a01', 'p01', 1000,  450.00 );
INSERT INTO order3 VALUES( 1019, 'feb', 'c001', 'a02', 'p02',  400,  180.00 );
INSERT INTO order3 VALUES( 1017, 'feb', 'c001', 'a06', 'p03',  600,  540.00 );
INSERT INTO order3 VALUES( 1018, 'feb', 'c001', 'a03', 'p04',  600,  540.00 );
INSERT INTO order3 VALUES( 1023, 'mar', 'c001', 'a04', 'p05',  500,  450.00 );
INSERT INTO order3 VALUES( 1022, 'mar', 'c001', 'a05', 'p06',  400,  720.00 );
INSERT INTO order3 VALUES( 1025, 'apr', 'c001', 'a05', 'p07',  800,  720.00 );
INSERT INTO order3 VALUES( 1013, 'jan', 'c002', 'a03', 'p03', 1000,  880.00 );
INSERT INTO order3 VALUES( 1026, 'may', 'c002', 'a05', 'p03',  800,  704.00 );
INSERT INTO order3 VALUES( 1015, 'jan', 'c003', 'a03', 'p05', 1200, 1104.00 );
INSERT INTO order3 VALUES( 1014, 'jan', 'c003', 'a03', 'p05', 1200, 1104.00 );
INSERT INTO order3 VALUES( 1021, 'feb', 'c004', 'a06', 'p01', 1000,  460.00 );
INSERT INTO order3 VALUES( 1016, 'jan', 'c006', 'a01', 'p01', 1000,  500.00 );
INSERT INTO order3 VALUES( 1020, 'feb', 'c006', 'a03', 'p07',  600,  600.00 );
INSERT INTO order3 VALUES( 1024, 'mar', 'c006', 'a06', 'p01',  800,  400.00 );