/*
** (c) Bond & Pollard Ltd 2022
** This software is free to use and modify at your own risk.
** ********************************************************
** **                  W A R N I N G                     **
** ********************************************************
**
**   This script DROPS the user scott and will delete ALL objects  
**   including tables, data, packages, functions etc.
** 
** Module Name   : install_scott_schema.sql
** Description   :
**
**   This script creates the Oracle Scott schema tables and 
**   related objects that are used in training courses.
**   The test data is also loaded into the tables.
**
**  NOTES
**   1. You must be connected to the database as SYS user, as SYSDBA (you will be prompted for the password)
**   2. Alter the Create user/schema code to install the objects
**      in the required schema. This script creates a schema called SCOTT.
** 
**------------------------------------------------------------------------
** Modification History
**  
** Date            Name         Description
**------------------------------------------------------------------------
** 06/08/2017     Ian Bond      Created from original Oracle Scott schema installation script
** 15/08/2021     Ian Bond      Removed newlines on insert statements causing errors in version 19c
** 17/06/2022     Ian Bond      Formatting improved
** 25/06/2022     Ian Bond      Reorganize insert statements
** 30/06/2022     Ian Bond      Group data definitions together for each table
**   
*/

SET TERMOUT ON
SET ECHO OFF

-------------------------------------------------------------------------------------------
-- ***** MODIFY THIS SECTION BEFORE RUNNING SCRIPT *****
-- Create user/schema, grant the required
-- privileges and connect to the database as that
-- user.
-------------------------------------------------------------------------------------------
CONNECT SYS AS SYSDBA
DROP USER scott CASCADE;
CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE VIEW TO scott;
ALTER USER scott QUOTA UNLIMITED ON USERS;
CONNECT scott/tiger


-------------------------------------------------------------------------------------------
-- Create Sequences first as they are required by the 
-- table triggers.
-- Start each sequence with the next value available
-- after data has been inserted into the corresponding table.
-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
--  DDL for Sequence CUSTID_SEQ
-------------------------------------------------------------------------------------------

  CREATE SEQUENCE  CUSTID_SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 109 NOCACHE  NOORDER  NOCYCLE ;

-------------------------------------------------------------------------------------------
--  DDL for Sequence ORDID_SEQ
-------------------------------------------------------------------------------------------

  CREATE SEQUENCE  ORDID_SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 622 NOCACHE  NOORDER  NOCYCLE ;

-------------------------------------------------------------------------------------------
--  DDL for Sequence PRODID_SEQ
-------------------------------------------------------------------------------------------

  CREATE SEQUENCE  PRODID_SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 200381 NOCACHE  NOORDER  NOCYCLE ;


-------------------------------------------------------------------------------------------
-- Create Tables, Indexes, Triggers and Constraints
-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
--  DDL for Table BONUS
-------------------------------------------------------------------------------------------

  CREATE TABLE BONUS 
   (	
    ENAME             VARCHAR2(10 BYTE), 
    JOB               VARCHAR2(9 BYTE), 
    SAL               NUMBER, 
    COMM              NUMBER
   ) ;

-------------------------------------------------------------------------------------------
--  DDL for Table CUSTOMER
-------------------------------------------------------------------------------------------
    
  CREATE TABLE CUSTOMER 
   (	
    CUSTID            NUMBER(6,0), 
    NAME              VARCHAR2(45 BYTE), 
    ADDRESS           VARCHAR2(40 BYTE), 
    CITY              VARCHAR2(30 BYTE), 
    STATE             VARCHAR2(2 BYTE), 
    ZIP               VARCHAR2(9 BYTE), 
    AREA              NUMBER(3,0), 
    PHONE             VARCHAR2(9 BYTE), 
    REPID             NUMBER(4,0), 
    CREDITLIMIT       NUMBER(9,2), 
    COMMENTS          LONG
   ) ;
   
  CREATE UNIQUE INDEX CUSTOMER_PK ON CUSTOMER (CUSTID) ;
  
  ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_CUSTID_CHK CHECK (CUSTID > 0) ENABLE;
  ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTID) ENABLE;
  ALTER TABLE CUSTOMER MODIFY (REPID NOT NULL ENABLE);
  ALTER TABLE CUSTOMER MODIFY (CUSTID NOT NULL ENABLE);

  CREATE OR REPLACE TRIGGER INSERT_CUSTOMER 
   BEFORE INSERT ON CUSTOMER 
   FOR EACH ROW 
   BEGIN  
     IF inserting THEN 
        IF :NEW.custid IS NULL THEN 
           SELECT custid_seq.NEXTVAL INTO :NEW.custid FROM dual; 
        END IF; 
     END IF; 
   END;
   
  /

  ALTER TRIGGER INSERT_CUSTOMER ENABLE;

-------------------------------------------------------------------------------------------
--  DDL for Table DEPT
-------------------------------------------------------------------------------------------

  CREATE TABLE DEPT 
   (	
    DEPTNO            NUMBER(2,0), 
    DNAME             VARCHAR2(14 BYTE), 
    LOC               VARCHAR2(13 BYTE)
   ) ;
   
  CREATE UNIQUE INDEX DEPT_PK ON DEPT (DEPTNO) ;

  ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO) ENABLE;

  

-------------------------------------------------------------------------------------------
--  DDL for Table DUMMY
-------------------------------------------------------------------------------------------

  CREATE TABLE DUMMY 
   (	
    DUMMY             NUMBER
   ) ;

-------------------------------------------------------------------------------------------
--  DDL for Table EMP
-------------------------------------------------------------------------------------------

  CREATE TABLE EMP 
   (	
    EMPNO             NUMBER(4,0), 
    ENAME             VARCHAR2(10 BYTE), 
    JOB               VARCHAR2(9 BYTE), 
    MGR               NUMBER(4,0), 
    HIREDATE          DATE, 
    SAL               NUMBER(7,2), 
    COMM              NUMBER(7,2), 
    DEPTNO            NUMBER(2,0)
   ) ;

  CREATE UNIQUE INDEX EMP_PK ON EMP (EMPNO) ;
  
  ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMPNO) ENABLE;
  ALTER TABLE EMP ADD CONSTRAINT EMP_MGR_FK FOREIGN KEY (MGR) REFERENCES EMP (EMPNO) ENABLE;
  ALTER TABLE EMP ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ENABLE;

-------------------------------------------------------------------------------------------
--  DDL for Table PRODUCT
-------------------------------------------------------------------------------------------

  CREATE TABLE PRODUCT 
   (	
    PRODID            NUMBER(6,0), 
    DESCRIP           VARCHAR2(30 BYTE)
   ) ;

  CREATE UNIQUE INDEX PRODUCT_PK ON PRODUCT (PRODID) ;
  
  ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PRODID) ENABLE;
  ALTER TABLE PRODUCT MODIFY (PRODID NOT NULL ENABLE);

  CREATE OR REPLACE TRIGGER INSERT_PRODUCT
   BEFORE INSERT ON PRODUCT
   FOR EACH ROW 
   BEGIN  
     IF inserting THEN 
        IF :NEW.prodid IS NULL THEN 
           SELECT prodid_seq.NEXTVAL INTO :NEW.prodid FROM dual; 
        END IF; 
     END IF; 
   END;
   
   /

   ALTER TRIGGER INSERT_PRODUCT ENABLE;
  
-------------------------------------------------------------------------------------------
--  DDL for Table ORD
-------------------------------------------------------------------------------------------

  CREATE TABLE ORD 
   (	
    ORDID             NUMBER(4,0), 
    ORDERDATE         DATE, 
    COMMPLAN          VARCHAR2(1 BYTE), 
    CUSTID            NUMBER(6,0), 
    SHIPDATE          DATE, 
    TOTAL             NUMBER(8,2)
   ) ;

  COMMENT ON COLUMN ORD.ORDID IS 'Primary key uniquely identifies each sales order';
  COMMENT ON TABLE ORD IS 'Sales Order table. Associated details in the ITEMS table';

  CREATE UNIQUE INDEX ORD_PK ON ORD (ORDID) ;
  
  ALTER TABLE ORD MODIFY (ORDID NOT NULL ENABLE);
  ALTER TABLE ORD MODIFY (CUSTID NOT NULL ENABLE);
  ALTER TABLE ORD ADD CONSTRAINT ORD_PK PRIMARY KEY (ORDID) ENABLE;
  ALTER TABLE ORD ADD CONSTRAINT ORD_CUSTID_FK FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID) ENABLE;
   
  CREATE OR REPLACE TRIGGER INSERT_ORD
  BEFORE INSERT ON ORD
  FOR EACH ROW 
  BEGIN  
    IF inserting THEN 
       IF :NEW.ordid IS NULL THEN 
          SELECT ordid_seq.NEXTVAL INTO :NEW.ordid FROM dual; 
       END IF; 
    END IF; 
  END;
  
  /

  ALTER TRIGGER INSERT_ORD ENABLE;
-------------------------------------------------------------------------------------------
--  DDL for Table ITEM
-------------------------------------------------------------------------------------------

  CREATE TABLE ITEM 
   (	
    ORDID             NUMBER(4,0), 
    ITEMID            NUMBER(4,0), 
    PRODID            NUMBER(6,0), 
    ACTUALPRICE       NUMBER(8,2), 
    QTY               NUMBER(8,0), 
    ITEMTOT           NUMBER(8,2)
   ) ;

  CREATE UNIQUE INDEX ITEM_PK ON ITEM (ORDID, ITEMID) ;
  
  ALTER TABLE ITEM ADD CONSTRAINT ITEM_PK PRIMARY KEY (ORDID, ITEMID) ENABLE;
  ALTER TABLE ITEM MODIFY (ITEMID NOT NULL ENABLE);
  ALTER TABLE ITEM MODIFY (ORDID NOT NULL ENABLE);
  ALTER TABLE ITEM ADD CONSTRAINT ITEM_ORDID_FK FOREIGN KEY (ORDID) REFERENCES ORD (ORDID) ENABLE;
  ALTER TABLE ITEM ADD CONSTRAINT ITEM_PRODID_FK FOREIGN KEY (PRODID) REFERENCES PRODUCT (PRODID) ENABLE;
  
-------------------------------------------------------------------------------------------
--  DDL for Table PRICE
-------------------------------------------------------------------------------------------

  CREATE TABLE PRICE 
   (	
    PRODID            NUMBER(6,0), 
    STDPRICE          NUMBER(8,2), 
    MINPRICE          NUMBER(8,2), 
    STARTDATE         DATE, 
    ENDDATE           DATE
   ) ;

  CREATE UNIQUE INDEX PRICE_PK ON PRICE (PRODID, STARTDATE);
  
  ALTER TABLE PRICE ADD CONSTRAINT PRICE_PK PRIMARY KEY (PRODID, STARTDATE) ENABLE;
  ALTER TABLE PRICE MODIFY (STARTDATE NOT NULL ENABLE);
  ALTER TABLE PRICE MODIFY (PRODID NOT NULL ENABLE);
  ALTER TABLE PRICE ADD CONSTRAINT PRICE_PRODID_FK FOREIGN KEY (PRODID) REFERENCES PRODUCT (PRODID) ENABLE;

-------------------------------------------------------------------------------------------
--  DDL for Table SALGRADE
-------------------------------------------------------------------------------------------

  CREATE TABLE SALGRADE 
   (	
    GRADE             NUMBER, 
    LOSAL             NUMBER, 
    HISAL             NUMBER
   ) ;

-------------------------------------------------------------------------------------------
-- Create Views
-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
--  DDL for View SALES
-------------------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW SALES (repid, custid, custname, prodid, prodname, amount) AS 
  SELECT repid, ord.custid, customer.NAME custname, product.prodid,
         descrip prodname, SUM(itemtot) amount
  FROM ord, item, customer, product
  WHERE ord.ordid = item.ordid
    AND ord.custid = customer.custid
    AND item.prodid = product.prodid
  GROUP BY repid, ord.custid, NAME, product.prodid, descrip;



--------------------------------------------------------
-- Load data into tables
--------------------------------------------------------

--------------------------------------------------------
-- DEPT table data load
--------------------------------------------------------

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');


--------------------------------------------------------
-- EMP table data load
--------------------------------------------------------

INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);

INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30);

INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10);

INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);

INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);

INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);

INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);

INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);

INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);

INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);

INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);

INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);

INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);

  
--------------------------------------------------------
-- SALGRADE table data load
--------------------------------------------------------

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);


--------------------------------------------------------
-- CUSTOMER table data load
--------------------------------------------------------


INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('96711', 'CA', '7844', '598-6609', 'JOCKSPORTS', '100', '5000', 'BELMONT', '415', '345 VIEWRIDGE', 'Very friendly people to work with -- sales rep likes to be called Mike.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('94061', 'CA', '7521', '368-1223', 'TKB SPORT SHOP','101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.', 'Rep called 5/8 about change in order - contact shipping.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('95133', 'CA', '7654', '644-3341','VOLLYRITE', '102','7000', 'BURLINGAME', '415', '9722 HAMILTON','Company doing heavy promotion beginning 10/89. Prepare for large orders during winter.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('97544', 'CA', '7521', '677-9312', 'JUST TENNIS','103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL', 'Contact rep about new line of tennis rackets.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('93301', 'CA', '7499', '996-2323','EVERY MOUNTAIN','104', '10000', 'CUPERTINO', '408', '574 SURRY RD.', 'Customer with high market share (23%) due to aggressive advertising.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('91003', 'CA', '7844', '376-9966', 'K + T SPORTS','105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO','Tends to order large amounts of merchandise at once. Accounting is considering raising their credit limit. Usually pays on time.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('94301', 'CA', '7521', '364-9777','SHAPE UP','106','6000', 'PALO ALTO', '415', '908 SEQUOIA','Support intensive. Orders small amounts (< 800) of merchandise at a time.');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('93301', 'CA', '7499', '967-4398','WOMENS SPORTS','107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE','First sporting goods store geared exclusively towards women. Unusual promotional style and very willing to take chances towards new products!');

INSERT INTO customer (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('55649', 'MN', '7844', '566-9123','NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER','108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', '');


--------------------------------------------------------
-- PRODUCT table data load
--------------------------------------------------------

INSERT INTO product (prodid, descrip) VALUES ('100860', 'ACE TENNIS RACKET I');

INSERT INTO product (prodid, descrip) VALUES ('100861', 'ACE TENNIS RACKET II');

INSERT INTO product (prodid, descrip) VALUES ('100870', 'ACE TENNIS BALLS-3 PACK');

INSERT INTO product (prodid, descrip) VALUES ('100871', 'ACE TENNIS BALLS-6 PACK');

INSERT INTO product (prodid, descrip) VALUES ('100890', 'ACE TENNIS NET');

INSERT INTO product (prodid, descrip) VALUES ('101860', 'SP TENNIS RACKET');

INSERT INTO product (prodid, descrip) VALUES ('101863', 'SP JUNIOR RACKET');

INSERT INTO product (prodid, descrip) VALUES ('102130', 'RH: "GUIDE TO TENNIS"');

INSERT INTO product (prodid, descrip) VALUES ('200376', 'SB ENERGY BAR-6 PACK');

INSERT INTO product (prodid, descrip) VALUES ('200380', 'SB VITA SNACK-6 PACK');


--------------------------------------------------------
-- ORD table data load
--------------------------------------------------------
INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('2.4', '30-MAY-1986', '601', '01-MAY-1986', '106', 'A');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('56', '20-JUN-1986', '602', '05-JUN-1986', '102', 'B');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('224', '05-JUN-1986', '603', '05-JUN-1986', '102', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('698', '30-JUN-1986', '604', '15-JUN-1986', '106', 'A');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('8324', '30-JUL-1986', '605', '14-JUL-1986', '106', 'A');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('3.4', '30-JUL-1986', '606', '14-JUL-1986', '100', 'A');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('5.6', '18-JUL-1986', '607', '18-JUL-1986', '104', 'C');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('35.2', '25-JUL-1986', '608', '25-JUL-1986', '104', 'C');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('97.5', '15-AUG-1986', '609', '01-AUG-1986', '100', 'B');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('101.4', '08-JAN-1987', '610', '07-JAN-1987', '101', 'A');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('45', '11-JAN-1987', '611', '11-JAN-1987', '102', 'B');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('5860', '20-JAN-1987', '612', '15-JAN-1987', '104', 'C');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('6400', '01-FEB-1987', '613', '01-FEB-1987', '108', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('23940', '05-FEB-1987', '614', '01-FEB-1987', '102', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('710', '06-FEB-1987', '615', '01-FEB-1987', '107', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('764', '10-FEB-1987', '616', '03-FEB-1987', '103', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('46370', '03-MAR-1987', '617', '05-FEB-1987', '105', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('3510.5', '06-MAR-1987', '618', '15-FEB-1987', '102', 'A');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('1260', '04-FEB-1987', '619', '22-FEB-1987', '104', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('4450', '12-MAR-1987', '620', '12-MAR-1987', '100', '');

INSERT INTO ord (total, shipdate, ordid, orderdate, custid, commplan) VALUES ('730', '01-JAN-1987', '621', '15-MAR-1987', '100', 'A');


--------------------------------------------------------
-- ITEM table data load
--------------------------------------------------------

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '1', '200376', '601', '2.4', '1', '2.4');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '20', '100870', '602', '56', '1', '2.8');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '4', '100860', '603', '224', '2', '56');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '3', '100890', '604', '174', '1', '58');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '2', '100861', '604', '84', '2', '42');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '10', '100860', '604', '440', '3', '44');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '100861', '605', '4500', '1', '45');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('500', '100870', '605', '1400', '2', '2.8');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('5', '100890', '605', '290', '3', '58');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('50', '101860', '605', '1200', '4', '24');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '101863', '605', '900', '5', '9');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '102130', '605', '34', '6', '3.4');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1', '102130', '606', '3.4', '1', '3.4');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1', '100871', '607', '5.6', '1', '5.6');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1', '101860', '608', '24', '1', '24');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('2', '100871', '608', '11.2', '2', '5.6');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1', '100861', '609', '35', '1', '35');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('5', '100870', '609', '12.5', '2', '2.5');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1', '100890', '609', '50', '3', '50');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '1', '100860', '610', '35', '1', '35');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '3', '100870', '610', '8.4', '2', '2.8');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '1', '100890', '610', '58', '3', '58');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '1', '100861', '611', '45', '1', '45');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '100', '100860', '612', '3000', '1', '30');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '20', '100861', '612', '810', '2', '40.5');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('150', '101863', '612', '1500', '3', '10');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '100871', '612', '550', '4', '5.5');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '100', '100871', '613', '560', '1', '5.6');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('200', '101860', '613', '4800', '2', '24');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('150', '200380', '613', '600', '3', '4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '200', '200376', '613', '440', '4', '2.2');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '444', '100860', '614', '15540', '1', '35');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ( '1000', '100870', '614', '2800', '2', '2.8');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1000', '100871', '614', '5600', '3', '5.6');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('4', '100861', '615', '180', '1', '45');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '100870', '615', '280', '2', '2.8');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('50', '100871', '615', '250', '3', '5');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '100861', '616', '450', '1', '45');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('50', '100870', '616', '140', '2', '2.8');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('2', '100890', '616', '116', '3', '58');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '102130', '616', '34', '4', '3.4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '200376' , '616', '24', '5', '2.4');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('50', '100860', '617', '1750', '1', '35');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '100861', '617', '4500', '2', '45');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('500', '100870', '617', '1400', '3', '2.8');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('500', '100871', '617', '2800', '4', '5.6');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('500', '100890', '617', '29000', '5', '58');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '101860', '617', '2400', '6', '24');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('200', '101863', '617', '2500', '7', '12.5');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '102130', '617', '340', '8', '3.4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('200', '200376', '617', '480', '9', '2.4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('300', '200380', '617', '1200', '10', '4');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('23', '100860', '618', '805', '1', '35');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('50', '100861', '618', '2255.5', '2', '45.11');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '100870', '618', '450', '3', '45');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '200380', '619', '400', '1', '4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '200376', '619', '240', '2', '2.4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '102130', '619', '340', '3', '3.4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('50', '100871', '619', '280', '4', '5.6');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '100860', '620', '350', '1', '35');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('1000', '200376', '620', '2400', '2', '2.4');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('500', '102130', '620', '1700', '3', '3.4');

INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('10', '100861', '621', '450', '1', '45');
INSERT INTO item ( qty , prodid , ordid , itemtot , itemid , actualprice) VALUES ('100', '100870', '621', '280', '2', '2.8');


--------------------------------------------------------
-- PRICE table data load
--------------------------------------------------------


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('30', '01-JAN-1985', '100860', '24', '31-DEC-1985');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('32', '01-JAN-1986', '100860', '25.6', '31-MAY-1986');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('35', '01-JUN-1986', '100860', '28', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('39', '01-JAN-1985', '100861', '31.2', '31-DEC-1985');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('42', '01-JAN-1986', '100861', '33.6', '31-MAY-1986');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('45', '01-JUN-1986', '100861', '36', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('2.4', '01-JAN-1985', '100870', '1.9', '01-DEC-1985');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('2.8', '01-JAN-1986', '100870', '2.4', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('4.8', '01-JAN-1985', '100871', '3.2', '01-DEC-1985');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('5.6', '01-JAN-1986', '100871', '4.8', '');


-- NB: Deliberate error in the prices for prodid 100890. Enddate precedes startdate 1 Jun 1984. Prices valid only from 1 Jan 85
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('54', '01-JUN-1984', '100890', '40.5', '31-MAY-1984');
INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('58', '01-JAN-1985', '100890', '46.4', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('24', '15-FEB-1985', '101860', '18', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('12.5', '15-FEB-1985', '101863', '9.4', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('3.4', '18-AUG-1985', '102130', '2.8', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('2.4', '15-NOV-1986', '200376', '1.75', '');


INSERT INTO price (stdprice, startdate, prodid, minprice, enddate) VALUES ('4', '15-NOV-1986', '200380', '3.2', '');


--------------------------------------------------------
-- Commit database changes
--------------------------------------------------------

COMMIT;

--------------------------------------------------------
-- End of script
--------------------------------------------------------
EXIT
