-- Drop the tables
/*
drop table ALL_ORDERS;
drop table ASSEMBLY;
drop table CUSTOMER;
drop table CUST_ORDER;
drop table DEPARTMENT;
drop table EMPLOYEE CASCADE CONSTRAINTS;
drop table EMPLOYEE_EXPENSE;
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 REGION;
drop table RETAILER;
drop table SALESPERSON;
drop table SUPPLIER;
DROP TABLE DISPUTED_ORDERS;
DROP TABLE REPORT;
DROP TABLE MONTHLY_ORDERS;
DROP TABLE SALES_HISTORY;
DROP TABLE MONTHLY_SALES_ADJUSTMENT;
DROP TABLE EMPLOYEE_COMMENT;*/
-- Create the tables
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)
);
CREATE TABLE EMPLOYEE_EXPENSE(
EMP_ID NUMBER(5),
YEAR NUMBER(4),
MONTH NUMBER(2),
EXPENSE_CLAIM NUMBER(7,2),
APPROVED_AMT NUMBER(7,2),
PAID_DATE DATE
);
CREATE TABLE DEPARTMENT(
DEPT_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(20),
LOCATION_ID NUMBER(3),
CONSTRAINT department_pk PRIMARY KEY (DEPT_ID)
);
CREATE TABLE JOB(
JOB_ID NUMBER(3) NOT NULL,
FUNCTION VARCHAR2(30),
CONSTRAINT job_pk PRIMARY KEY (JOB_ID)
);
CREATE TABLE LOCATION(
LOCATION_ID NUMBER(3) NOT NULL,
REGIONAL_GROUP VARCHAR2(20),
CONSTRAINT location_pk PRIMARY KEY (LOCATION_ID)
);
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)
);
CREATE TABLE SUPPLIER(
SUPPLIER_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
CONSTRAINT supplier_pk PRIMARY KEY (SUPPLIER_ID)
);
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)
);
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)
);
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)
);
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)
);
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)
);
CREATE TABLE REGION(
REGION_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(20),
SUPER_REGION_ID NUMBER(5),
CONSTRAINT region_pk PRIMARY KEY (REGION_ID)
);
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)
);
CREATE TABLE MONTHS(
YEAR NUMBER(4) NOT NULL,
MONTH NUMBER(2) NOT NULL,
CONSTRAINT months_pk PRIMARY KEY (YEAR, MONTH)
);
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
);
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)
);
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)
);
CREATE TABLE ALL_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 all_orders_pk PRIMARY KEY (YEAR, MONTH, CUST_NBR, REGION_ID, SALESPERSON_ID)
);
CREATE TABLE DISPUTED_ORDERS(
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,
STATUS VARCHAR2(20),
CONSTRAINT disputed_orders_pk PRIMARY KEY (ORDER_NBR)
);
CREATE TABLE REPORT (
REPORT_ID VARCHAR2(8),
CREATED_BY VARCHAR2(20),
SENT_TO VARCHAR2(20)
);
CREATE TABLE MONTHLY_ORDERS (
MONTH NUMBER(2) NOT NULL,
YEAR NUMBER(4) NOT NULL,
TOT_ORDERS NUMBER,
MAX_ORDER_AMT NUMBER,
MIN_ORDER_AMT NUMBER,
TOT_AMT NUMBER
);
CREATE TABLE MONTHLY_SALES_ADJUSTMENT(
MONTH NUMBER(2) NOT NULL,
FACTOR NUMBER(3,2) NOT NULL,
CONSTRAINT adjust_pl PRIMARY KEY (MONTH)
);
CREATE TABLE employee_comment (
emp_id NUMBER(5),
text VARCHAR2(500));
-- Insert Data into the tables.
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
select e.emp_id, e.fname, e.lname, e.dept_id, e.manager_emp_id, e.salary, TO_DATE(e.hire_date,'DD-MM-YY'), e.job_id
from
(
select 7369 emp_id, 'JOHN' fname, 'SMITH' lname, 20 dept_id, 7902 manager_emp_id, 800 salary, '17-12-80' hire_date, 667 job_id from dual union all
select 7499 emp_id, 'KEVIN' fname, 'ALLEN' lname, 30 dept_id, 7698 manager_emp_id, 1600 salary, '20-02-81' hire_date, 670 job_id from dual union all
select 7521 emp_id, 'CYNTHIA' fname, 'WARD' lname, 30 dept_id, 7698 manager_emp_id, 1250 salary, '22-02-81' hire_date, null job_id
Mastering.Oracle.SQL英文第二版及所用到的示例数据表创建脚本
5星 · 超过95%的资源 需积分: 0 110 浏览量
2010-10-15
18:01:06
上传
评论
收藏 713KB RAR 举报 
Mastering.Oracle.SQL英文第二版及所用到的示例数据表创建脚本.rar （2个子文件） 
Mastering.Oracle.SQL.2nd.sql 290KB Mastering.Oracle.SQL.2nd.chm 712KB
评论2