DROP TABLE shipped CASCADE;
DROP TABLE receipt CASCADE;
DROP TABLE product CASCADE;
DROP TABLE badguy CASCADE;
CREATE TABLE product
  (id CHAR(4),
   description CHAR(30),
   price DECIMAL(10,2),
   PRIMARY KEY (id));
CREATE TABLE badguy
  (id CHAR(4),
   name CHAR(20),
   address CHAR(20),
   PRIMARY KEY (id));
CREATE TABLE receipt
  (badguy CHAR(4),
   rdate  DATE,
   notes CHAR(20),
   amount DECIMAL(10,2),
   FOREIGN KEY(badguy) REFERENCES badguy(id),
   PRIMARY KEY(badguy,rdate));

CREATE TABLE shipped
  (badguy CHAR(4),
   sdate DATE,
   product CHAR(4),
   quantity DECIMAL(6),
   FOREIGN KEY(product) REFERENCES product(id),
   PRIMARY KEY(badguy, sdate, product));

INSERT INTO product VALUES ('P001', 'Anvil', 75.00);
INSERT INTO product VALUES ('P002', 'Portable holes', 5.00);
INSERT INTO product VALUES ('P007', 'Rocket roller skates', 80.00);
INSERT INTO product VALUES ('P004', 'TNT', 50.00);
INSERT INTO product VALUES ('P005', 'Bomb', 50.00);
INSERT INTO product VALUES ('P006', 'Elastic band', 2.00);
INSERT INTO product VALUES ('P003', 'Horseshoe magnet', 80.00);
INSERT INTO product VALUES ('P008', 'Space ship', 60000000.00);
INSERT INTO product VALUES ('P009', 'Road sign "Diversion Left"', 30.00);
INSERT INTO product VALUES ('P010', 'Road sign "Diversion Right"', 30.00);

INSERT INTO badguy VALUES ('C001', 'Wile E Coyote', 'www.warnerbros.com');
INSERT INTO badguy VALUES ('C002', 'Sylvester', 'www.warnerbros.com');
INSERT INTO badguy VALUES ('C003', 'Tom', 'www.mgm.com');
INSERT INTO badguy VALUES ('C004', 'Elmer Fudd', 'www.warnerbros.com');
INSERT INTO badguy VALUES ('C005', 'Dick Dastardly', 'www.warnerbros.com');

INSERT INTO shipped VALUES ('C001','10-JUL-1998','P003',1);
INSERT INTO shipped VALUES ('C001','22-JUL-1998','P004',2);
INSERT INTO shipped VALUES ('C001','22-JUL-1998','P005',12);
INSERT INTO shipped VALUES ('C001','23-JUL-1998','P005',12);
INSERT INTO shipped VALUES ('C001','23-JUL-1998','P006',1);
INSERT INTO shipped VALUES ('C001','23-JUL-1998','P007',1);
INSERT INTO shipped VALUES ('C005','01-JAN-1998','P008',1);

INSERT INTO receipt VALUES ('C005','10-FEB-1998','DD', 5.00);
INSERT INTO receipt VALUES ('C005','10-MAR-1998','DD', 5.00);
INSERT INTO receipt VALUES ('C005','10-APR-1998','DD', 5.00);
INSERT INTO receipt VALUES ('C005','10-MAY-1998','DD', 5.00);
INSERT INTO receipt VALUES ('C005','10-JUN-1998','DD', 5.00);
INSERT INTO receipt VALUES ('C005','10-JUL-1998','DD', 5.00);
INSERT INTO receipt VALUES ('C001','15-JUL-1998','Cheque',80.00);
INSERT INTO receipt VALUES ('C001','23-JUL-1998','Cash',700.00);

INSERT INTO shipped VALUES ('C001',DATE '1998-07-10','P003',1);
INSERT INTO shipped VALUES ('C001',DATE '1998-07-22','P004',2);
INSERT INTO shipped VALUES ('C001',DATE '1998-07-22','P005',12);
INSERT INTO shipped VALUES ('C001',DATE '1998-07-23','P005',12);
INSERT INTO shipped VALUES ('C001',DATE '1998-07-23','P006',1);
INSERT INTO shipped VALUES ('C001',DATE '1998-07-23','P007',1);
INSERT INTO shipped VALUES ('C005',DATE '1998-01-01','P008',1);

INSERT INTO receipt VALUES ('C005',DATE '1998-02-10','DD', 5.00);
INSERT INTO receipt VALUES ('C005',DATE '1998-03-10','DD', 5.00);
INSERT INTO receipt VALUES ('C005',DATE '1998-04-10','DD', 5.00);
INSERT INTO receipt VALUES ('C005',DATE '1998-05-10','DD', 5.00);
INSERT INTO receipt VALUES ('C005',DATE '1998-06-10','DD', 5.00);
INSERT INTO receipt VALUES ('C005',DATE '1998-07-10','DD', 5.00);
INSERT INTO receipt VALUES ('C001',DATE '1998-07-15','Cheque',100.00);
INSERT INTO receipt VALUES ('C001',DATE '1998-07-23','Cash',255.00);
COMMIT;

