-- -------------------------------------------------
-- Filename: 54003.sql
--
-- Script file for PL/SQL 101 for Oracle 10g
-- Chapter 03
-- 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);



-- Figure 3-1 
SELECT * FROM plsql101_product
WHERE  quantity_on_hand = 1;


-- Figure 3-2
SELECT * FROM plsql101_product 
WHERE  quantity_on_hand < 500;


-- Figure 3-3
SELECT * FROM plsql101_product 
WHERE  quantity_on_hand < 1000;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand <= 1000;


-- Figure 3-4
SELECT * FROM plsql101_product
WHERE  product_name = 'Small Widget';


-- Figure 3-5
SELECT * FROM plsql101_product
WHERE  product_name LIKE 'Chrome%';

SELECT * FROM plsql101_product
WHERE  product_name LIKE '%Chrome%';


-- Figure 3-6
SELECT * FROM plsql101_product
WHERE  last_stock_date = '15-JAN-06';


-- Figure 3-7
SELECT * FROM plsql101_product
WHERE  last_stock_date IS NULL;

SELECT * FROM plsql101_product
WHERE  last_stock_date IS NOT NULL;


-- Figure 3-8
SELECT * FROM plsql101_product 
ORDER BY product_price;


-- Figure 3-9
SELECT * FROM plsql101_product 
ORDER BY last_stock_date, product_name;



-- Figure 3-10
SELECT   last_stock_date, 
         product_name, 
         product_price, 
         quantity_on_hand
FROM     plsql101_product
ORDER BY last_stock_date, 
         product_name
;



-- Preparation for section on Showing Only Unique Values
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 
     ('Chrome Phoobar', 'CA', '16-JUL-06', 2);
INSERT INTO plsql101_purchase VALUES 
     ('Round Snaphoo', 'LB', '16-JUL-06', 25);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'BB', '17-JUL-06', 2);


-- Figure 3-11



-- Figure 3-12
SELECT 18*1.05 FROM plsql101_purchase;
DESC DUAL;
SELECT * FROM DUAL;
SELECT 18*1.05 FROM DUAL;


-- Figure 3-13
SELECT * FROM plsql101_purchase;

UPDATE plsql101_purchase
SET    product_name = 'Large Widget'
WHERE  product_name = 'Small Widget';

SELECT * FROM plsql101_purchase;


-- Figure 3-14
SELECT * FROM plsql101_purchase;

DELETE FROM plsql101_purchase
WHERE purchase_date > '15-JUL-06';

SELECT * FROM plsql101_purchase;


-- Figure 3-15
-- prep from earlier in text
TRUNCATE TABLE plsql101_purchase;
-- end of prep from earlier in text


INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'CA', '14-JUL-06', 1);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'BB', '14-JUL-06', 75);

SELECT * FROM plsql101_purchase;

ROLLBACK;

SELECT * FROM plsql101_purchase;


-- Figure 3-16
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'CA', '14-JUL-06', 1);
SAVEPOINT A;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'BB', '14-JUL-06', 75);
SAVEPOINT B;
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'GA', '14-JUL-06', 2);
SAVEPOINT C;
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'GA', '15-JUL-06', 8);
SAVEPOINT D;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'LB', '15-JUL-06', 20);

COMMIT;
ROLLBACK TO D;
SELECT * FROM plsql101_purchase;


-- Figure 3-17
INSERT INTO plsql101_purchase VALUES (
       'Round Snaphoo', 'CA', '16-JUL-06', 5);