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


-- Startup text at beginning of chapter
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-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.')
     );

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-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');


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-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 Snaphoo', 'CA', '16-JUL-06',5);


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');



-- Exercise 54: Indexes
/*
drop INDEX t1_index_c1; 

timing start
update t1 set c3='01-JUN-99' where
c1 > 20000;
timing stop

timing start
update t1 set c3='01-JUN-99' where
c1 > 20000;
timing stop

CREATE INDEX t1_index_c1 ON t1(c1); 

timing start
update t1 set c3='01-JUN-99' where
c1 > 20000;
timing stop

timing start
update t1 set c3='01-JUN-99' where
c1 > 20000;
timing stop

-- Start the deletion test
timing start
delete from t1 where c1=20000;
timing stop

timing start
delete from t1 where c1=19999;
timing stop

drop INDEX t1_index_c1; 

timing start
delete from t1 where c1=19998;
timing stop

timing start
delete from t1 where c1=19997;
timing stop


*/


-- Beginning of chapter exercises

CREATE INDEX plsql101_person_code_index 
ON plsql101_person(person_code);

CREATE INDEX plsql101_person_name_index 
ON plsql101_person(last_name, first_name);

CREATE INDEX plsql101_person_caps_index 
ON plsql101_person(UPPER(last_name), 
                   UPPER(first_name)
                   )
;

CREATE INDEX plsql101_purchase_product
ON plsql101_purchase(product_name);

CREATE INDEX plsql101_purchase_salesperson
ON plsql101_purchase(salesperson);


-- Figure 6-5
ALTER TABLE plsql101_person MODIFY (first_name NOT NULL);
ALTER TABLE plsql101_person MODIFY (last_name NOT NULL);


-- Figure 6-6
INSERT INTO plsql101_person VALUES (
     'XL', 'Xaviera', NULL, '15-NOV-06'
     )
;


-- Figure 6-7
ALTER TABLE plsql101_person 
ADD CONSTRAINT plsql101_person_unique UNIQUE (
    first_name,
    last_name,
    hire_date
    )
;

INSERT INTO plsql101_person VALUES (
     'LN', 'Linda', 'Norton', '01-JUN-06');

INSERT INTO plsql101_person VALUES (
     'NL', 'Linda', 'Norton', '01-JUN-06');


-- Figure 6-8
INSERT INTO plsql101_person VALUES (
     'ZZ', 'Linda', 'Norton', '01-JUN-06');

INSERT INTO plsql101_person VALUES (
     'ZZ', 'LINDA', 'Norton', '01-JUN-06');

DELETE FROM plsql101_person 
WHERE first_name = 'LINDA';

ALTER TABLE plsql101_person 
DROP CONSTRAINT plsql101_person_unique;

CREATE UNIQUE INDEX plsql101_person_unique_upper 
ON plsql101_person (
    UPPER(first_name),
    UPPER(last_name),
    hire_date
    )
;

INSERT INTO plsql101_person VALUES (
     'ZZ', 'LINDA', 'Norton', '01-JUN-06');



-- Figure 6-9
ALTER TABLE plsql101_purchase ADD ( 
     CONSTRAINT reasonable_date CHECK(
          purchase_date IS NOT NULL
          AND
          purchase_date >= '2003-06-30'
          )
     )
;

INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'GA', '28-FEB-03', 10);


-- Figure 6-10
ALTER TABLE plsql101_product 
ADD CONSTRAINT reasonable_stock_date CHECK(
          last_stock_date >= '31-DEC-2004'
          )
;

INSERT INTO plsql101_product VALUES (
     'Anodized Framifier', 49, 5, NULL)
;

INSERT INTO plsql101_product VALUES (
     'Spring-Loaded Pit Puller', 49, 5, '30-DEC-04')
;


-- Figure 6-11
INSERT INTO plsql101_product VALUES (
     'Red Snaphoo', 1.95, 10, '30-DEC-04')
;

ALTER TABLE plsql101_product DISABLE CONSTRAINT reasonable_stock_date;

INSERT INTO plsql101_product VALUES (
     'Red Snaphoo', 1.95, 10, '30-DEC-04')
;

ALTER TABLE plsql101_product ENABLE CONSTRAINT reasonable_stock_date;

UPDATE plsql101_product 
SET    last_stock_date = '31-DEC-04' 
WHERE  last_stock_date = '30-DEC-04';

ALTER TABLE plsql101_product ENABLE CONSTRAINT reasonable_stock_date;


-- Figure 6-12
ALTER TABLE plsql101_person MODIFY (first_name NULL);
ALTER TABLE plsql101_person MODIFY (last_name NULL);


-- Figure 6-13
INSERT INTO plsql101_product VALUES (
     'Blue Snaphoo', 1.95, 10, '30-DEC-04')
;

ALTER TABLE plsql101_product DROP CONSTRAINT reasonable_stock_date;

INSERT INTO plsql101_product VALUES (
     'Blue Snaphoo', 1.95, 10, '30-DEC-04')
;  


-- Figure 6-17
ALTER TABLE plsql101_product
ADD PRIMARY KEY (product_name);


-- commands within text - no figure attached
ALTER TABLE plsql101_person
ADD PRIMARY KEY (person_code);

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


-- Figure 6-18
SELECT product_name
FROM   plsql101_product
ORDER BY product_name;

SELECT DISTINCT product_name
FROM   plsql101_purchase
ORDER BY product_name;


-- Figure 6-19
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
               FOREIGN KEY (product_name) 
               REFERENCES plsql101_product
;


-- commands within text - no figure attached
UPDATE plsql101_purchase
SET    product_name = 'Round Chrome Snaphoo'
WHERE  product_name = 'Round Snaphoo';

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


-- Figure 6-20
INSERT INTO plsql101_purchase VALUES (
     'Small Widgee', 'CA', '17-JUL-06', 1)
;


-- commands within text - no figure attached
INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'CA', '17-JUL-06', 1)
;


-- Figure 6-21
SELECT plsql101_purchase.product_name,
       plsql101_person.last_name,
       plsql101_person.first_name,
       plsql101_purchase.quantity
FROM   plsql101_purchase,
       plsql101_person
WHERE  plsql101_person.person_code = plsql101_purchase.salesperson
;


-- Figure 6-22
SELECT plsql101_purchase.product_name,
       plsql101_person.last_name,
       plsql101_person.first_name,
       plsql101_purchase.quantity
FROM   plsql101_purchase,
       plsql101_person
;


-- commands within text - no figure attached
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);


-- Figure 6-23
SELECT product_name FROM plsql101_purchase 
ORDER BY product_name;
SELECT product_name FROM plsql101_purchase_archive 
ORDER BY product_name;

SELECT product_name FROM plsql101_purchase
UNION
SELECT product_name FROM plsql101_purchase_archive
ORDER BY product_name;


-- Figure 6-24
SELECT * 
FROM   plsql101_product
WHERE  last_stock_date = (
       SELECT last_stock_date
       FROM   plsql101_product
       WHERE  product_name = 'Small Widget'
       )
;


-- Figure 6-25
-- Readers are supposed to figure out how to build the following command
SELECT * 
FROM   plsql101_product
WHERE  product_price = (
       SELECT product_price
       FROM   plsql101_product
       WHERE  product_name = 'Red Snaphoo'
       )
;


-- Figure 6-26
SELECT * 
FROM  plsql101_purchase
ORDER BY product_name;

SELECT * 
FROM   plsql101_product
WHERE  product_name NOT IN (
       SELECT DISTINCT product_name
       FROM   plsql101_purchase
       )
ORDER BY product_name
;