-- -------------------------------------------------
-- Filename: 54007.sql
--
-- Script file for PL/SQL 101 for Oracle 10g
-- Chapter 07
-- Created by Christopher Allen
-- Copyright Oracle Press (c) 2004
-- -------------------------------------------------


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

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-FEB-05');
INSERT INTO plsql101_person VALUES
     ('GA', 'Gary', 'Anderson', '15-FEB-05');
INSERT INTO plsql101_person VALUES
     ('BB', 'Bobby', 'Barkenhagen', '28-FEB-05');
INSERT INTO plsql101_person VALUES
     ('LB', 'Laren', 'Baxter', '01-MAR-05');
INSERT INTO plsql101_person VALUES 
     ('LN', 'Linda', 'Norton', '01-JUN-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-JAN-06');
INSERT INTO plsql101_product VALUES
     ('Medium Wodget', 75, 1000, '15-JAN-05');
INSERT INTO plsql101_product VALUES
     ('Chrome Phoobar', 50, 100, '15-JAN-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-JAN-07');
INSERT INTO plsql101_product VALUES ('Square Zinculator', 
     45, 1, TO_DATE('December 31, 2005, 11:30 P.M.',
                       '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-DEC-04');
INSERT INTO plsql101_product VALUES (
     'Blue Snaphoo', 1.95, 10, '30-DEC-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-JUL-06', 1);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'BB', '14-JUL-06', 75);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'GA', '14-JUL-06', 2);
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'GA', '15-JUL-06', 8);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'LB', '15-JUL-06', 20);
INSERT INTO plsql101_purchase VALUES 
     ('Round Chrome Snaphoo', 'CA', '16-JUL-06', 5);
INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'CA', '17-JUL-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_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');


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-JUN-04', 10);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Large Harflinger', 'GA', '22-JUN-04', 50);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Medium Wodget', 'LB', '23-JUN-04', 20);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Small Widget', 'ZZ', '24-JUN-05', 80);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Chrome Phoobar', 'CA', '25-JUN-05', 2);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Small Widget', 'JT', '26-JUN-05', 50);





-- Preparation for Figure 1
DROP TABLE plsql101_purchase_log;
CREATE TABLE plsql101_purchase_log (
     purchase_date    DATE, 
     product_name     VARCHAR2(25),
     product_price    NUMBER(4,2),
     quantity         NUMBER(4,2),
     first_name VARCHAR2(15),
     last_name  VARCHAR2(20)
     )
;



-- Figure 7-1
INSERT INTO plsql101_purchase_log (
     SELECT purc.purchase_date,
            prod.product_name,
            prod.product_price,
            purc.quantity,
            pers.first_name,
            pers.last_name
     FROM   plsql101_product  prod,
            plsql101_person   pers,
            plsql101_purchase purc
     WHERE  prod.product_name = purc.product_name
            AND
            pers.person_code = purc.salesperson
     )
;


SELECT * FROM plsql101_purchase_log;



-- Figure 7-2
DELETE FROM plsql101_purchase_log;

CREATE TABLE plsql101_salesperson_log (
     first_name VARCHAR2(15),
     last_name  VARCHAR2(20),
     purchase_date    DATE, 
     product_name     VARCHAR2(25),
     quantity         NUMBER(4,2)
     )
;

INSERT ALL
INTO plsql101_purchase_log VALUES (purchase_date, product_name, 
                                   product_price, quantity, 
                                   first_name, last_name)
INTO plsql101_salesperson_log VALUES (first_name, last_name, 
                                   purchase_date, product_name, 
                                   quantity)
SELECT purc.purchase_date,
       prod.product_name,
       prod.product_price,
       purc.quantity,
       pers.first_name,
       pers.last_name
FROM   plsql101_product  prod,
       plsql101_person   pers,
       plsql101_purchase purc
WHERE  prod.product_name = purc.product_name
  AND  pers.person_code = purc.salesperson
;

SELECT * FROM plsql101_purchase_log;
SELECT * FROM plsql101_salesperson_log;



-- Figure 7-3
CREATE TABLE plsql101_purchase_log_small (
     purchase_date    DATE, 
     product_name     VARCHAR2(25),
     quantity         NUMBER(4,2)
     )
;
CREATE TABLE plsql101_purchase_log_large (
     purchase_date    DATE, 
     product_name     VARCHAR2(25),
     quantity         NUMBER(4,2)
     )
;

INSERT ALL
WHEN product_price < 75 THEN
     INTO plsql101_purchase_log_small VALUES (purchase_date, product_name, quantity)
WHEN product_price >= 75 THEN
     INTO plsql101_purchase_log_large VALUES (purchase_date, product_name, quantity)
SELECT purc.purchase_date,
       prod.product_name,
       prod.product_price,
       purc.quantity
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  prod.product_name = purc.product_name
;

SELECT * FROM plsql101_purchase_log_small;
SELECT * FROM plsql101_purchase_log_large;



-- Figure 7-4
CREATE TABLE plsql101_person_change (
     person_code VARCHAR2(3),
     first_name  VARCHAR2(15),
     last_name   VARCHAR2(20),
     hire_date   DATE
     )
;
INSERT INTO plsql101_person_change VALUES 
       ('ZA', 'Zelda', 'Armstrong', '28-APR-2006');
INSERT INTO plsql101_person_change VALUES 
       ('LN', NULL, 'Norton-Smith', NULL);

MERGE INTO plsql101_person   p
USING plsql101_person_change pc
ON (p.person_code = pc.person_code)
WHEN MATCHED THEN
     UPDATE SET p.last_name = pc.last_name
WHEN NOT MATCHED THEN
     INSERT (person_code, first_name, last_name, hire_date)
     VALUES (pc.person_code, pc.first_name, pc.last_name, pc.hire_date);

SELECT * FROM plsql101_person;



-- Figure 7-5
CREATE TABLE plsql101_purchase_log2 AS 
     SELECT purc.purchase_date,
            prod.product_name,
            prod.product_price,
            purc.quantity,
            pers.first_name,
            pers.last_name
     FROM   plsql101_product  prod,
            plsql101_person   pers,
            plsql101_purchase purc
     WHERE  prod.product_name = purc.product_name
            AND
            pers.person_code = purc.salesperson
;

SELECT * FROM plsql101_purchase_log2;




-- Command entered in text, with no screen shot
RENAME plsql101_purchase_log2 TO plsql101_log;



-- Figure 7-6
DESC plsql101_log

ALTER TABLE plsql101_log
ADD data_load_date VARCHAR2(8);

DESC plsql101_log



-- Figure 7-7
ALTER TABLE plsql101_log
ADD temp_column CHAR(1);

ALTER TABLE plsql101_log
RENAME COLUMN temp_column TO temp_col;

DESC plsql101_log



-- Figure 7-8
ALTER TABLE plsql101_log
DROP COLUMN temp_col;

DESC plsql101_log



-- Figure 7-9
DESC plsql101_log

ALTER TABLE plsql101_log
MODIFY data_load_date DATE;

DESC plsql101_log



-- Figure 7-10
UPDATE plsql101_log SET data_load_date = '15-DEC-2006';

DESC plsql101_log

ALTER TABLE plsql101_log MODIFY data_load_date NOT NULL;

DESC plsql101_log



-- Figure 7-11
SELECT * FROM plsql101_purchase;

CREATE OR REPLACE VIEW plsql101_sales_by_atlas_v AS
SELECT * 
FROM   plsql101_purchase
WHERE  salesperson = 'CA'
;

SELECT * FROM plsql101_sales_by_atlas_v;



-- Command in text, with no screen shot
CREATE OR REPLACE VIEW plsql101_sales_per_person_v AS
SELECT pers.first_name || ' ' || pers.last_name SALESPERSON,
       purc.product_name, 
       purc.purchase_date,
       purc.quantity
FROM   plsql101_person   pers,
       plsql101_purchase purc
WHERE  pers.person_code = purc.salesperson (+)
;

SELECT * FROM plsql101_sales_per_person_v
ORDER BY salesperson, product_name, purchase_date;


DROP VIEW plsql101_sales_per_person_v;



-- Figure 7-12
CREATE OR REPLACE VIEW plsql101_overstocked_items AS
SELECT *
FROM   (SELECT product_name, quantity_on_hand
        FROM   plsql101_product
        ORDER BY quantity_on_hand DESC
       )
WHERE ROWNUM <= 3
;

SELECT * FROM plsql101_overstocked_items;



-- Command in text, with no screen shot
CREATE SEQUENCE plsql101_test_seq;



-- Figure 7-13
SELECT plsql101_test_seq.nextval FROM DUAL;
SELECT plsql101_test_seq.nextval FROM DUAL;
SELECT plsql101_test_seq.nextval FROM DUAL;



-- Figure 7-14
CREATE TABLE plsql101_test (
       record_id   NUMBER(18,0),
       record_text VARCHAR2(10)
);

INSERT INTO plsql101_test VALUES (
     plsql101_test_seq.nextval, 'Record A'
);

INSERT INTO plsql101_test VALUES (
     plsql101_test_seq.nextval, 'Record B'
);

SELECT * FROM plsql101_test;



-- Figure 7-15
ALTER SEQUENCE plsql101_test_seq
MAXVALUE 8
;

SELECT plsql101_test_seq.nextval FROM DUAL;
SELECT plsql101_test_seq.nextval FROM DUAL;
SELECT plsql101_test_seq.nextval FROM DUAL;
SELECT plsql101_test_seq.nextval FROM DUAL;



-- 7-16
SELECT * FROM prod;

CREATE SYNONYM prod FOR plsql101_product;

SELECT * FROM prod;



-- Command in text, with no screen shot
DROP SYNONYM prod;



-- Figure 7-17
SELECT table_name, SUBSTR(comments, 1, 45)
FROM   dict
WHERE  SUBSTR(comments, 1, 7) <> 'Synonym'
;



-- Clean up tables that won't be used in subsequent chapters
DROP TABLE plsql101_purchase_log;
DROP TABLE plsql101_salesperson_log;
DROP TABLE plsql101_purchase_log_small;
DROP TABLE plsql101_purchase_log_large;
DROP TABLE plsql101_person_change;