-- Startup text at beginning of chapter
DROP TABLE plsql101_purchase;
DROP TABLE plsql101_product;
DROP TABLE plsql101_person CASCADE CONSTRAINTS;
DROP TABLE plsql101_old_item;
DROP TABLE plsql101_purchase_archive;
DROP TABLE plsql101_audit;

CREATE TABLE plsql101_person (
     person_code VARCHAR2(3) PRIMARY KEY,
     first_name  VARCHAR2(15),
     last_name   VARCHAR2(20),
     hire_date   DATE
     )
;

CREATE INDEX plsql101_person_name_index 
ON plsql101_person(last_name, first_name);

ALTER TABLE plsql101_person 
ADD CONSTRAINT plsql101_person_unique UNIQUE (
     first_name,
     last_name,
     hire_date
     )
;

INSERT INTO plsql101_person VALUES
     ('CA', 'Charlene', 'Atlas', '01-2月-05');
INSERT INTO plsql101_person VALUES
     ('GA', 'Gary', 'Anderson', '15-2月-05');
INSERT INTO plsql101_person VALUES
     ('BB', 'Bobby', 'Barkenhagen', '28-2月-05');
INSERT INTO plsql101_person VALUES
     ('LB', 'Laren', 'Baxter', '01-5月-05');
INSERT INTO plsql101_person VALUES 
     ('LN', 'Linda', 'Norton', '01-1月-06');

CREATE TABLE plsql101_product (
     product_name     VARCHAR2(25) PRIMARY KEY,
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE
     )
;

ALTER TABLE plsql101_product ADD CONSTRAINT positive_quantity CHECK(
     quantity_on_hand IS NOT NULL
     AND
     quantity_on_hand >=0
     )
;

INSERT INTO plsql101_product VALUES
     ('Small Widget', 99, 1, '15-1月-06');
INSERT INTO plsql101_product VALUES
     ('Medium Wodget', 75, 1000, '15-1月-05');
INSERT INTO plsql101_product VALUES
     ('Chrome Phoobar', 50, 100, '15-1月-06');
INSERT INTO plsql101_product VALUES
     ('Round Chrome Snaphoo', 25, 10000, null);
INSERT INTO plsql101_product VALUES
     ('Extra Huge Mega Phoobar +',9.95,1234,'15-1月-07');
INSERT INTO plsql101_product VALUES ('Square Zinculator', 
     45, 1, TO_DATE('12月 31, 2005, 11:30 下午.',
                       'Month dd, YYYY, HH:MI P.M.')
     )
;
INSERT INTO plsql101_product VALUES (
     'Anodized Framifier', 49, 5, NULL);
INSERT INTO plsql101_product VALUES (
     'Red Snaphoo', 1.95, 10, '31-12月-04');
INSERT INTO plsql101_product VALUES (
     'Blue Snaphoo', 1.95, 10, '30-12月-04')
;
CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;

ALTER TABLE plsql101_purchase
ADD PRIMARY KEY (product_name,
                 salesperson,
                 purchase_date
                 )
;

ALTER TABLE plsql101_purchase 
ADD CONSTRAINT reasonable_date CHECK(
     purchase_date IS NOT NULL
     AND
     TO_CHAR(purchase_date, 'YYYY-MM-DD') >= '2003-06-30'
     )
;

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product FOREIGN KEY 
     (product_name) REFERENCES plsql101_product;

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_person FOREIGN KEY 
     (salesperson) REFERENCES plsql101_person;

CREATE INDEX plsql101_purchase_product
ON plsql101_purchase(product_name);

CREATE INDEX plsql101_purchase_salesperson
ON plsql101_purchase(salesperson);

INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'CA', '14-7月-06', 1);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'BB', '14-7月-06', 75);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'GA', '14-7月-06', 2);
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'GA', '15-7月-06', 8);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'LB', '15-7月-06', 20);
INSERT INTO plsql101_purchase VALUES 
     ('Round Chrome Snaphoo', 'CA', '16-7月-06', 5);
INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'CA', '17-7月-06', 1)
;

UPDATE plsql101_product
SET    product_price = product_price * .9
WHERE  product_name NOT IN (
       SELECT DISTINCT product_name
       FROM   plsql101_purchase
       )
;

CREATE TABLE plsql101_purchase_archive ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;


INSERT INTO plsql101_purchase_archive VALUES 
     ('Round Snaphoo', 'BB', '21-6月-04', 10);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Large Harflinger', 'GA', '22-6月-04', 50);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Medium Wodget', 'LB', '23-6月-04', 20);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Small Widget', 'ZZ', '24-6月-05', 80);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Chrome Phoobar', 'CA', '25-6月-05', 2);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Small Widget', 'JT', '26-6月-05', 50);