- RUS means, a union operation on R and S that will produce another table which will have all the rows of R and S except the duplicates
- There are 2 Sally in R and S, so, just take 1 from them
- R-S means, a difference operation on R and S that will produce another table which will have all the rows which is in R but not in S.
- As Sally is in R and in S, so, she has been omitted.
- R∩S means, an intersection operation on R and S that will produce another table which will have all the rows which are in both R and S.
- As Sally is in R and in S, so, she has been selected.
- You can give a table or a column another name by using an alias.
- This can be a good thing to do if you have very long or complex table names or column names.
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name AS alias_name
FROM table_name AS alias_name
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name
FROM table_name
-->
-- 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);
-->
DROP TABLE students;
DROP TABLE english_group;
CREATE TABLE students(
Roll Int,
Name Varchar (20),
Subject Varchar(20)
);
CREATE TABLE english_group(
Roll Int,
Name Varchar (20)
);
INSERT INTO students VALUES (1, 'Ratul', 'Science');
INSERT INTO students VALUES (2, 'Nafee', 'Science');
INSERT INTO students VALUES (3, 'Shawon', 'English');
INSERT INTO students VALUES (4, 'Biva', 'Geography');
INSERT INTO students VALUES (5, 'Farhana', 'English');
COMMIT;
-->
--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;
-->
--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 7.
delete from customer;
delete from cust_order;
-- Insert new data relevant to chapter 7.
-- Insert data into customer
insert into customer (cust_nbr, name, region_id, tot_orders, last_order_dt)
select c.c_nbr, c.c_nm, c.rgn, c.tot_ord, to_date(c.lst_ord, 'DD-MON-RR')
from
(select 1 c_nbr, 'Cooper Industries' c_nm, 5 rgn, 96 tot_ord, '15-JUN-00' lst_ord from dual union all
select 2 c_nbr, 'Emblazon Corp.' c_nm, 5 rgn, 96 tot_ord, '27-JUN-00' lst_ord from dual union all
select 3 c_nbr, 'Ditech Corp.' c_nm, 5 rgn, 96 tot_ord, '07-JUL-00' lst_ord from dual union all
select 4 c_nbr, 'Flowtech Inc.' c_nm, 5 rgn, 96 tot_ord, '15-JUL-00' lst_ord from dual union all
select 5 c_nbr, 'Gentech Industries' c_nm, 5 rgn, 96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 6 c_nbr, 'Spartan Industries' c_nm, 6 rgn, 96 tot_ord, '10-JUN-00' lst_ord from dual union all
select 7 c_nbr, 'Wallace Labs' c_nm, 6 rgn, 101 tot_ord, '17-JUN-00' lst_ord from dual union all
select 8 c_nbr, 'Zantech Inc.' c_nm, 6 rgn, 96 tot_ord, '22-JUN-00' lst_ord from dual union all
select 9 c_nbr, 'Cardinal Technologies' c_nm, 6 rgn, 96 tot_ord, '25-JUN-00' lst_ord from dual union all
select 10 c_nbr, 'Flowrite Corp.' c_nm, 6 rgn, 96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 11 c_nbr, 'Glaven Technologies' c_nm, 7 rgn, 96 tot_ord, '05-JUN-00' lst_ord from dual union all
select 12 c_nbr, 'Johnson Labs' c_nm, 7 rgn, 96 tot_ord, '07-JUN-00' lst_ord from dual union all
select 13 c_nbr, 'Kimball Corp.' c_nm, 7 rgn, 96 tot_ord, '07-JUN-00' lst_ord from dual union all
select 14 c_nbr, 'Madden Industries' c_nm, 7 rgn, 101 tot_ord, '05-JUN-00' lst_ord from dual union all
select 15 c_nbr, 'Turntech Inc.' c_nm, 7 rgn, 96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 16 c_nbr, 'Paulson Labs' c_nm, 8 rgn, 96 tot_ord, '31-MAY-00' lst_ord from dual union all
select 17 c_nbr, 'Evans Supply Corp.' c_nm, 8 rgn, 96 tot_ord, '28-MAY-00' lst_ord from dual union all
select 18 c_nbr, 'Spalding Medical Inc.' c_nm, 8 rgn, 96 tot_ord, '23-MAY-00' lst_ord from dual union all
select 19 c_nbr, 'Kendall-Taylor Corp.' c_nm, 8 rgn, 96 tot_ord, '16-MAY-00' lst_ord from dual union all
select 20 c_nbr, 'Malden Labs' c_nm, 8 rgn, 96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 21 c_nbr, 'Crimson Medical Inc.' c_nm, 9 rgn, 101 tot_ord, '26-MAY-00' lst_ord from dual union all
select 22 c_nbr, 'Nichols Industries' c_nm, 9 rgn, 96 tot_ord, '18-MAY-00' lst_ord from dual union all
select 23 c_nbr, 'Owens-Baxter Corp.' c_nm, 9 rgn, 96 tot_ord, '08-MAY-00' lst_ord from dual union all
select 24 c_nbr, 'Jackson Medical Inc.' c_nm, 9 rgn, 96 tot_ord, '26-APR-00' lst_ord from dual union all
select 25 c_nbr, 'Worcester Technologies' c_nm, 9 rgn, 96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 26 c_nbr, 'Alpha Technologies' c_nm, 10 rgn, 96 tot_ord, '21-MAY-00' lst_ord from dual union all
select 27 c_nbr, 'Phillips Labs' c_nm, 10 rgn, 96 tot_ord, '08-MAY-00' lst_ord from dual union all
select 28 c_nbr, 'Jaztech Corp.' c_nm, 10 rgn, 101 tot_ord, '23-APR-00' lst_ord from dual union all
select 29 c_nbr, 'Madden-Taylor Inc.' c_nm, 10 rgn, 96 tot_ord, '06-APR-00' lst_ord from dual union all
select 30 c_nbr, 'Wallace Industries' c_nm, 10 rgn, 96 tot_ord, '01-JUN-00' lst_ord from dual) c;
commit;
-- Insert data into cust_order
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1001,1,7354,99,'22-JUL-01','23-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1000,1,7354,null,'19-JUL-01','24-JUL-01','21-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1002,5,7368,null,'12-JUL-01','25-JUL-01','14-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1003,4,7654,56,'16-JUL-01','26-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1004,4,7654,34,'18-JUL-01','27-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1005,8,7654,99,'22-JUL-01','24-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1006,1,7354,null,'22-JUL-01','28-JUL-01','24-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1007,5,7368,25,'20-JUL-01','22-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1008,5,7368,25,'21-JUL-01','23-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1009,1,7354,56,'18-JUL-01','22-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1012,1,7354,99,'22-JUL-01','23-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1011,1,7354,null,'19-JUL-01','24-JUL-01','21-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1015,5,7368,null,'12-JUL-01','25-JUL-01','14-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1017,4,7654,56,'16-JUL-01','26-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1019,4,7654,34,'18-JUL-01','27-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1021,8,7654,99,'22-JUL-01','24-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1023,1,7354,null,'22-JUL-01','28-JUL-01','24-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1025,5,7368,25,'20-JUL-01','22-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1027,5,7368,25,'21-JUL-01','23-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1029,1,7354,56,'18-JUL-01','22-JUL-01',null,null,'DELIVERED');
commit;
No comments:
Post a Comment