Wednesday, February 13, 2013

Join


—Join operations bring together two tables and combine their rows and columns in a specific fashion.
—The generic join operator called the Theta Join is 
—It takes as arguments the columns from the two tables that are to be joined.
—When the join condition operator is   =   then we call this an Equipping.



Natural Join

—Notice in the generic (Theta) join operation, any attributes in common (such as dept above) are repeated.
—The Natural Join operation removes these duplicate attributes.
—The natural join operator is: *
—We can also assume using * that the join condition will be = on the two attributes in common.


-- Script to create tables / views 
-- You should have a tablespace USERS, 
-- and the user running this script should have sufficient quota on USERS tablespace.
-- For simplicity data as well as indexes are kept in one tablespace.

DROP TABLE CUST_ORDER;
DROP TABLE CUSTOMER;
DROP TABLE DEPARTMENT;
DROP TABLE EMPLOYEE;
DROP TABLE INVENTORY_CLASS;
DROP TABLE JOB;
DROP TABLE LINE_ITEM;
DROP TABLE LOCATION;
DROP TABLE MONTHS;
DROP TABLE MTD_ORDERS;
DROP TABLE ORDERS;
DROP TABLE PART;
DROP TABLE SALESPERSON;
DROP TABLE SUPPLIER;
DROP TABLE REGION;
DROP TABLE ASSEMBLY;
DROP TABLE RETAILER;


CREATE TABLE CUST_ORDER(
    ORDER_NBR           NUMBER(7)           NOT NULL,
    CUST_NBR            NUMBER(5)           NOT NULL,
    SALES_EMP_ID        NUMBER(5)           NOT NULL,
    SALE_PRICE          NUMBER(9,2),
    ORDER_DT            DATE                NOT NULL,
    EXPECTED_SHIP_DT    DATE                NOT NULL,
    CANCELLED_DT        DATE,
    SHIP_DT             DATE,
    STATUS              VARCHAR2(20),
    CONSTRAINT cust_order_pk PRIMARY KEY (ORDER_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 20K
                NEXT 20K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 50K
        NEXT 50K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE CUSTOMER(
    CUST_NBR    NUMBER(5)       NOT NULL,
    NAME        VARCHAR2(30)    NOT NULL,
    REGION_ID   NUMBER(5),
    INACTIVE_DT DATE,
    INACTIVE_IND CHAR(1),
    TOT_ORDERS  NUMBER(5),
    LAST_ORDER_DT DATE,
    CONSTRAINT customer_pk PRIMARY KEY (CUST_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE DEPARTMENT(
    DEPT_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    LOCATION_ID NUMBER(3),
    CONSTRAINT department_pk PRIMARY KEY (DEPT_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE EMPLOYEE(
    EMP_ID            NUMBER(5)    NOT NULL,
    FNAME             VARCHAR2(20),
    LNAME             VARCHAR2(20),
    DEPT_ID           NUMBER(5)    NOT NULL,
    MANAGER_EMP_ID    NUMBER(5),
    SALARY            NUMBER(5),
    HIRE_DATE         DATE,
    JOB_ID            NUMBER(3),
    CONSTRAINT employee_pk PRIMARY KEY (EMP_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE INVENTORY_CLASS(
    INV_CLASS    VARCHAR2(3) NOT NULL,
    LOW_COST     NUMBER(8,2),
    HIGH_COST    NUMBER(8,2),
    CONSTRAINT inv_class_pk PRIMARY KEY (INV_CLASS)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE JOB(
    JOB_ID    NUMBER(3) NOT NULL,
    FUNCTION  VARCHAR2(30),
    CONSTRAINT job_pk PRIMARY KEY (JOB_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);
    
CREATE TABLE LINE_ITEM(
    ORDER_NBR     NUMBER(9)      NOT NULL,
    PART_NBR      VARCHAR2(20)   NOT NULL,
    QTY           NUMBER(5)      NOT NULL,
    FILLED_QTY    NUMBER(5),
    CONSTRAINT line_item_pk PRIMARY KEY (ORDER_NBR,PART_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 20K
                NEXT 20K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 50K
        NEXT 50K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE LOCATION(
    LOCATION_ID    NUMBER(3) NOT NULL,
    REGIONAL_GROUP VARCHAR2(20),
    CONSTRAINT location_pk PRIMARY KEY (LOCATION_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE MONTHS(
    YEAR       NUMBER(4) NOT NULL,
    MONTH      NUMBER(2) NOT NULL,
    CONSTRAINT months_pk PRIMARY KEY (YEAR, MONTH)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE MTD_ORDERS(
    TOT_ORDERS                  NUMBER(7) NOT NULL,
    TOT_SALE_PRICE              NUMBER(11,2) NOT NULL,
    MAX_SALE_PRICE              NUMBER(9,2) NOT NULL,
    EUROPE_TOT_ORDERS           NUMBER(7) NOT NULL,
    EUROPE_TOT_SALE_PRICE       NUMBER(11,2) NOT NULL,
    EUROPE_MAX_SALE_PRICE       NUMBER(9,2) NOT NULL,
    NORTHAMERICA_TOT_ORDERS     NUMBER(7) NOT NULL,
    NORTHAMERICA_TOT_SALE_PRICE NUMBER(11,2) NOT NULL,
    NORTHAMERICA_MAX_SALE_PRICE NUMBER(9,2) NOT NULL
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE ORDERS(
    CUST_NBR       NUMBER(5) NOT NULL,
    REGION_ID      NUMBER(5) NOT NULL,
    SALESPERSON_ID NUMBER(5) NOT NULL,
    YEAR           NUMBER(4) NOT NULL,
    MONTH          NUMBER(2) NOT NULL,
    TOT_ORDERS     NUMBER(7) NOT NULL,
    TOT_SALEs      NUMBER(11,2) NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (YEAR, MONTH, CUST_NBR, REGION_ID,
      SALESPERSON_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);
    
CREATE TABLE PART(
    PART_NBR         VARCHAR2(20)        NOT NULL,
    NAME             VARCHAR2(30)        NOT NULL,
    SUPPLIER_ID      NUMBER(5)           NOT NULL,
    STATUS           VARCHAR2(20)        NOT NULL,
    INVENTORY_QTY    NUMBER(6),
    UNIT_COST        NUMBER(8,2),
    RESUPPLY_DATE    DATE,
    CONSTRAINT part_pk PRIMARY KEY (PART_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 25K
        NEXT 25K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE SALESPERSON(
    SALESPERSON_ID    NUMBER(5) NOT NULL,
    NAME              VARCHAR2(50) NOT NULL,
    PRIMARY_REGION_ID NUMBER(5) NOT NULL,
    CONSTRAINT salesperson_pk PRIMARY KEY (SALESPERSON_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE SUPPLIER(
    SUPPLIER_ID    NUMBER(5)     NOT NULL,
    NAME           VARCHAR2(30)  NOT NULL,
    CONSTRAINT supplier_pk PRIMARY KEY (SUPPLIER_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE REGION(
    REGION_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    SUPER_REGION_ID NUMBER(5),
    CONSTRAINT region_pk PRIMARY KEY (REGION_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);
        
        
CREATE TABLE ASSEMBLY (
 ASSEMBLY_TYPE              VARCHAR2(4)     NOT NULL,
 ASSEMBLY_ID                NUMBER(6)         NOT NULL,
 DESCRIPTION                VARCHAR2(20)     NOT NULL,
 PARENT_ASSEMBLY_TYPE       VARCHAR2(4),
 PARENT_ASSEMBLY_ID         NUMBER(6),
     CONSTRAINT assembly_pk PRIMARY KEY (ASSEMBLY_TYPE, ASSEMBLY_ID)
     USING INDEX
         PCTFREE 10
         INITRANS 2
         MAXTRANS 255
         TABLESPACE USERS
         STORAGE(INITIAL 10K
                 NEXT 10K
                 MINEXTENTS 1
                 MAXEXTENTS 121
                 PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);


-- Create table retailer

CREATE TABLE RETAILER(
 RTLR_NBR             NUMBER(6)    NOT NULL ,
 NAME                 VARCHAR2(45),                    
 ADDRESS              VARCHAR2(40),
 CITY                 VARCHAR2(30),
 STATE                VARCHAR2(2),
 ZIP_CODE             VARCHAR2(9),
 AREA_CODE            NUMBER(3),
 PHONE_NUMBER         NUMBER(7),
 SALESPERSON_ID       NUMBER(4),
 CREDIT_LIMIT         NUMBER(9,2),
 COMMENTS             LONG,
    CONSTRAINT RETAILER_PK PRIMARY KEY (RTLR_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
) 
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

-- Create view DEPT

CREATE OR REPLACE VIEW DEPT AS
SELECT * 
FROM DEPARTMENT
WHERE DEPT_ID IN (10,20,30,40);

        
        
         

Outer Join

—In the Join operations so far, only those tuples from both relations that satisfy the join condition are included in the output relation.
—The Outer join includes other rows as well according to a few rules.
—Three types of outer joins:
1.Left Outer Join   includes all rows in the left hand relation and includes only those matching rows from the right hand relation.
2.Right Outer Join   includes all rows in the right hand relation and includes only those matching rows from the left hand relation.
3.Full Outer Join   includes all rows in the left hand relation and from the right hand relation.


--Setup the needed default date format, and language, for the session.
PROMPT Changing date language to 'AMERICAN', and
PROMPT the date format to 'DD-MON-RR'.
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';
PROMPT
PROMPT You may wish to reconnect after this script
PROMPT completes, as reconnecting will restore your
PROMPT date language and date format settings to
PROMPT their defaults.
PROMPT
PAUSE Press ENTER to continue...

-- Delete existing data from tables used in chapter 3.


delete from employee;
delete from location;
delete from department;
delete from job;
delete from supplier;
delete from part;
delete from retailer;
delete from inventory_class;


-- Insert new data relevant to chapter 3.


-- Insert data into employee

insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7369,'JOHN','SMITH',20,7902,800,'17-DEC-80',667);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7499,'KEVIN','ALLEN',30,7698,1600,'20-FEB-81',670);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7521,'CYNTHIA','WARD',30,7698,1250,'22-FEB-81',670);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7566,'TERRY','JONES',20,7839,2000,'02-APR-81',671);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7654,'KENNETH','MARTIN',30,7698,1250,'28-SEP-81',670);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7698,'MARION','BLAKE',30,7839,2850,'01-MAY-80',671);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7782,'CAROL','CLARK',10,7839,2450,'09-JUN-81',671);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7788,'DONALD','SCOTT',20,7566,3000,'19-APR-87',669);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7839,'FRANCIS','KING',10, null,5000,'17-NOV-81',672);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7844,'MARY','TURNER',30,7698,1500,'08-SEP-81',670);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7876,'DIANE','ADAMS',20,7788,1100,'23-MAY-87',667);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7900,'FRED','JAMES',30,7698,950,'03-DEC-81',667);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7902,'JENNIFER','FORD',20,7566,3000,'03-DEC-81',669);
insert into employee 
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7934,'BARBARA','MILLER',10,7782,1300,'23-JAN-82',667);

commit;


-- Insert data into location

insert into location 
(LOCATION_ID,REGIONAL_GROUP)
values
(122,'NEW YORK');
insert into location 
(LOCATION_ID,REGIONAL_GROUP)
values
(124,'DALLAS');
insert into location 
(LOCATION_ID,REGIONAL_GROUP)
values
(123,'CHICAGO');
insert into location 
(LOCATION_ID,REGIONAL_GROUP)
values
(167,'BOSTON');
insert into location 
(LOCATION_ID,REGIONAL_GROUP)
values
(144,'SAN FRANCISCO');

commit;


-- Insert data into department

insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(10,'ACCOUNTING',122);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(20,'RESEARCH',124);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(30,'SALES',123);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(40,'OPERATIONS',167);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(12,'RESEARCH',122);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(13,'SALES',122);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(14,'OPERATIONS',122);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(23,'SALES',124);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(24,'OPERATIONS',124);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(34,'OPERATIONS',123);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(43,'SALES',167);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(50,'MARKETING',null);
insert into department 
(DEPT_ID,NAME,LOCATION_ID)
values
(60,'CONSULTING',null);

commit;


-- Insert data into Job

insert into Job 
(JOB_ID,FUNCTION)
values
(667,'CLERK');
insert into Job 
(JOB_ID,FUNCTION)
values
(668,'STAFF');
insert into Job 
(JOB_ID,FUNCTION)
values
(669,'ANALYST');
insert into Job 
(JOB_ID,FUNCTION)
values
(670,'SALESPERSON');
insert into Job 
(JOB_ID,FUNCTION)
values
(671,'MANAGER');
insert into Job 
(JOB_ID,FUNCTION)
values
(672,'PRESIDENT');
insert into Job 
(JOB_ID,FUNCTION)
values
(665,'SECRETARY');

commit;



-- Insert data into Supplier

insert into Supplier
(SUPPLIER_ID, NAME)
values
(101, 'Pacific Disks, Inc.');


insert into Supplier
(SUPPLIER_ID, NAME)
values
(102, 'Silicon Valley MicroChips');

insert into Supplier
(SUPPLIER_ID, NAME)
values
(103, 'Blue River Electronics');

commit;


-- Insert data into Part

insert into Part
(PART_NBR, NAME, SUPPLIER_ID, STATUS, INVENTORY_QTY, UNIT_COST, RESUPPLY_DATE)
values
('HD211', '20 GB Hard Disk', 101, 'ACTIVE', 5, 2000, '12-DEC-00');

insert into Part
(PART_NBR, NAME, SUPPLIER_ID, STATUS, INVENTORY_QTY, UNIT_COST, RESUPPLY_DATE)
values
('P3000', '3000 MHz Processor', 102, 'ACTIVE', 12, 600, '03-NOV-00');

commit;


-- Insert data into Inventory_class

insert into Inventory_class
(INV_CLASS, LOW_COST, HIGH_COST)
values
('C',0,99);

insert into Inventory_class
(INV_CLASS, LOW_COST, HIGH_COST)
values
('B',100,999);

insert into Inventory_class
(INV_CLASS, LOW_COST, HIGH_COST)
values
('A',1000,9999);

commit;


-- Inserting data into retailer


insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(100,'JOCKSPORTS','345 VIEWRIDGE','BELMONT','CA','96711',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(101,'TKB SPORT SHOP','490 BOLI RD.','REDWOOD CITY','CA','94061',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(102,'VOLLYRITE','9722 HAMILTON','BURLINGAME','CA','95133',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(103,'JUST TENNIS','HILLVIEW MALL','BURLINGAME','CA','97544',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(104,'EVERY MOUNTAIN','574 SURRY RD.','CUPERTINO','CA','93301',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(105,'K + T SPORTS','3476 EL PASEO','SANTA CLARA','CA','91003',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(106,'SHAPE UP','908 SEQUOIA','PALO ALTO','CA','94301',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(107,'WOMENS SPORTS','VALCO VILLAGE','SUNNYVALE','CA','93301',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(201,'STADIUM SPORTS','47 IRVING PL.','NEW YORK','NY','10003',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(202,'HOOPS','2345 ADAMS AVE.','LEICESTER','MA','01524',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(203,'REBOUND SPORTS','2 E. 14TH ST.','NEW YORK','NY','10009',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(204,'THE POWER FORWARD','1 KNOTS LANDING','DALLAS','TX','75248',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(205,'POINT GUARD','20 THURSTON ST.','YONKERS','NY','10956',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(206,'THE COLISEUM','5678 WILBUR PL.','SCARSDALE','NY','10583',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(207,'FAST BREAK','1000 HERBERT LN.','CONCORD','MA','01742',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(208,'AL AND BOB''S SPORTS','260 YORKTOWN CT.','AUSTIN','TX','78731',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(211,'AT BAT','234 BEACHEM ST.','BROOKLINE','MA','02146',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(212,'ALL SPORT','1000 38TH ST.','BROOKLYN','NY','11210',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(213,'GOOD SPORT','400 46TH ST.','SUNNYSIDE','NY','11104',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(214,'AL''S PRO SHOP','45 SPRUCE ST.','SPRING','TX','77388',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(215,'BOB''S FAMILY SPORTS','400 E. 23RD','HOUSTON','TX','77026',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(216,'THE ALL AMERICAN','547 PRENTICE RD.','CHELSEA','MA','02150',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(217,'HIT, THROW, AND RUN','333 WOOD COURT','GRAPEVINE','TX','76051',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(218,'THE OUTFIELD','346 GARDEN BLVD.','FLUSHING','NY','11355',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(221,'WHEELS AND DEALS','2 MEMORIAL DRIVE','HOUSTON','TX','77007',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(222,'JUST BIKES','4000 PARKRIDGE BLVD.','DALLAS','TX','75205',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(223,'VELO SPORTS','23 WHITE ST.','MALDEN','MA','02148',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(224,'JOE''S BIKE SHOP','4500 FOX COURT','GRAND PRARIE','TX','75051',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(225,'BOB''S SWIM, CYCLE, AND RUN','300 HORSECREEK CIRCLE','IRVING','TX','75039',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(226,'CENTURY SHOP','8 DAGMAR DR.','HUNTINGTON','NY','11743',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(227,'THE TOUR','2500 GARDNER RD.','SOMERVILLE','MA','02144',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(228,'FITNESS FIRST','5000 85TH ST.','JACKSON HEIGHTS','NY','11372',7521);

commit;







Posted by OPP(Object Oriented Programming) at 2:08 AM

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Vision