-- ======================== PERSON ==================================
DROP TABLE plsql101_person;
CREATE TABLE plsql101_person (
     person_code VARCHAR2(3),
     first_name  VARCHAR2(15),
     last_name   VARCHAR2(20),
     hire_date   DATE
     )
;

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



-- ================= PRODUCT ========================

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

INSERT INTO plsql101_product VALUES
     ('Small Widget', 99, 1, '15-1月-03');
INSERT INTO plsql101_product VALUES
     ('Medium Wodget', 75, 1000, '15-1月-02');
INSERT INTO plsql101_product VALUES
     ('Chrome Phoobar', 50, 100, '15-1月-03');
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月-04');
INSERT INTO plsql101_product VALUES ('Square Zinculator', 
     45, 1, TO_DATE('12月 31, 2002, 11:30 下午',
                       'Month dd, YYYY, HH:MI P.M.')
     )
;



-- =================== PURCHASE ========================
DROP TABLE plsql101_purchase;
CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;

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

-- Note: The "Round Snaphoo" records in PRODUCT and PURCHASE are
-- out of sync in terms of product name.  Good opportunity to use this
-- later to enforce the importance of referential integrity.


-- =========================== OLD_ITEM ===============================
DROP TABLE plsql101_old_item;
CREATE TABLE plsql101_old_item (
     item_id   CHAR(20),
     item_desc CHAR(25)
     )
;

INSERT INTO plsql101_old_item VALUES
     ('LA-101', 'Can, Small');
INSERT INTO plsql101_old_item VALUES
     ('LA-102', 'Can, Large');
INSERT INTO plsql101_old_item VALUES
     ('LA-103', 'Bottle, Small');
INSERT INTO plsql101_old_item VALUES
     ('LA-104', 'Bottle, Large');
INSERT INTO plsql101_old_item VALUES
     ('NY-101', 'Box, Small');
INSERT INTO plsql101_old_item VALUES
     ('NY-102', 'Box, Large');
INSERT INTO plsql101_old_item VALUES
     ('NY-103', 'Shipping Carton, Small');
INSERT INTO plsql101_old_item VALUES
     ('NY-104', 'Shipping Carton, Large');