为方便读者阅读并尽快学会使用 DM 系统，本手册在介绍利用 DM 建立、维护数据库以及对数据库进行的各种操作中，使用了示例库 BOOKSHOP。本章将对该示例库进行说明。
2.1 示例库说明
示例库 BOOKSHOP 模拟武汉代理图书的某销售公司，该公司欲建立在线购物平台来拓展其代理产品的销售渠道，该在线购物平台支持网上产品信息浏览、订购等服务(仅限同城内销售及送货)。该销售公司的雇员、部门信息、业务方案等是所有实例的基础。
安装该示例后，将在数据库中创建 BOOKSHOP 表空间，同时创建 RESOURCES、PERSON、PRODUCTION、PURCHASING、SALES、OTHER 这 6 个模式和相关的表。示例库 BOOKSHOP 的默认数据库名为 DAMENG，默认实例名为 DMSERVER。
示例库 BOOKSHOP 所包含的模式、表如下：
| 模式 | 包含相关对象 | 包含的表 |
|---|---|---|
| RESOURCES | 公司基本信息表 | EMPLOYEE EMPLOYEE_ADDRESS DEPARTMENT EMPLOYEE_DEPARTMENT |
| PERSON | 各个客户、雇员、供应商的名称和地址 | ADDRESS ADDRESS_TYPE PERSON PERSON_TYPE |
| PRODUCTION | 产品销售的信息 | PRODUCT PRODUCT_CATEGORY PRODUCT_SUBCATEGORY PRODUCT_INVENTORY LOCATION PRODUCT_REVIEW PRODUCT_VENDOR |
| PURCHASING | 供应商列表(采购订单等) | PURCHASEORDER_HEADER PURCHASEORDER_DETIAL VENDOR VENDOR_ADDRESS VENDOR_PERSON |
| SALES | 与客户销售相关的数据(销售定单等) | CUSTMER CUSTMER_ADDRESS SALESORDER_HEADER SALESORDER_DETAIL SALESPERSON |
| OTHER | 用到的其他表 | DEPARTMENT EMPSALARY ACCOUNT ACTIONS READER READERAUDIT DEPTTAB EMPTAB SALGRADE COMPANYHOLIDAYS |
EMPLOYEE
该公司的雇员信息表，包含在 RESOURCES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| EMPLOYEEID | INT | 非空 | 主键，自增列 |
| NATIONALNO | VARCHAR(18) | 非空 | 身份证号码 |
| PERSONID | INT | 非空 | 指向 PERSON.PERSONID 的外键 |
| LOGINID | VARCHAR(256) | 非空 | 用户登录 ID |
| TITLE | VARCHAR(50) | 非空 | 职位 |
| MANAGERID | INT | 空 | 直接上司 ID，指 EMPLOYEE.EMPLOYEEID 的外键 |
| BIRTHDATE | DATE | 非空 | 出生日期 |
| MARITALSTATUS | CHAR(1) | 非空 | S=未婚 M=已婚 |
| PHOTO | IMAGE | 空 | 照片 |
| HAIRDATE | DATE | 非空 | 入职时间 |
| SALARY | DEC(19,4) | 非空 | 薪资（元） |
EMPLOYEE_ADDRESS
将雇员映射到 ADDRESS 表中的地址信息，包含在 RESOURCES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| EMPLOYEEID | INT | 非空 | 指向 EMPLOYEE.EMPLOYEEID 的外键 |
| ADDRESSID | INT | 非空 | 指向 ADDRESS.ADDRESSID 的外键 |
EMPLOYEE_DEPARTMENT
将雇员映射到 DEPARTMENT 表中的部门信息，包含在 RESOURCES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| EMPLOYEEID | INT | 非空 | 指向 EMPLOYEE.EMPLOYEEID 的外键 |
| DEPARTMENTID | INT | 非空 | 员工所在部门，指向 DEPARTMENT. DEPARTMENTID 的外键 |
| STARTDATE | DATE | 非空 | 在该部门开始工作的日期 |
| ENDDATE | DATE | 空 | 离开该部门的日期 空=雇员在当前部门 |
DEPARTMENT
该公司的部门信息，包含在 RESOURCES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| DEPARTENTID | INT | 非空 | 主键， 自增列 |
| NAME | VARCHAR(50) | 非空 | 部门名称 |
PERSON
该公司所有雇员、供应商、客户的姓名信息表，包含在 PERSON 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PERSONID | INT | 非空 | 主键， 自增列，聚集索引 |
| NAME | VARCHAR(50) | 非空 | 姓名 |
| SEX | CHAR(1) | 非空 | M=男 F=女 |
| VARCHAR(50) | 空 | 电子邮件地址 | |
| PHONE | VARCHAR(25) | 空 | 电话 |
PERSON_TYPE
存储 PERSON 表中的联系人类型，客户中的联系人类型有采购经理、采购代表，供应商的联系人类型有销售经理、销售代表。包含在 PERSON 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PERSON_TYPEID | INT | 非空 | 主键， 自增列 |
| NAME | VARCHAR(50) | 非空 | 联系人类型说明 |
ADDRESS
雇员、客户、供应商的地址信息，包含在 PERSON 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| ADDRESSID | INT | 非空 | 主键， 自增列 |
| ADDRESS1 | VARCHAR(60) | 非空 | 第一通讯地址 |
| ADDRESS2 | VARCHAR(60) | 空 | 第二通讯地址 |
| CITY | VARCHAR(30) | 非空 | 市/县名称 |
| POSTALCODE | VARCHAR(15) | 非空 | 邮政编码 |
ADDRESS_TYPE
为雇员、客户、供应商定义地址类型的表，包含在 PERSON 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| ADDRESS_TYPEID | INT | 非空 | 主键，自增列 |
| NAME | VARCHAR(50) | 非空 | 地址类型的说明如开票地址、家庭地址、 送货地址、公司地址等 |
CUSTOMER
当前客户信息，包含在 SALES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| CUSTOMERID | INT | 非空 | 主键， 自增列 |
| PERSONID | INT | 非空 | 指向 PERSON.PERSONID 的外键 |
CUSTOMER_ADDRESS
将客户映射到某个地址或多个地址，包含在 SALES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| CUSTOMERID | INT | 非空 | 主键，指向 CUSTOMER.CUSTOMERID 的外键 |
| ADDRESSID | INT | 非空 | 主键，指向 ADDRESS.ADDRESSID 的外键 |
| ADDRESS_TYPEID | INT | 非空 | 地址类型，指向 ADDRESS_TYPE.ADDRESS_TYPEID 的外键 |
SALESPERSON
销售代表的销售统计信息，包含在 SALES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| SALESPERSONID | INT | 非空 | 主键， 自增列 |
| EMPLOYEEID | INT | 非空 | 该销售代表对应的雇员号，指向 EMPLOYEE.EMPLOYEEID 的外键 |
| SALESTHISYEAR | DEC(19,4) | 非空 | 今年到目前为止销售总额(万) |
| SALESLASTYEAR | DEC(19,4) | 非空 | 去年销售总额(万) |
VENDOR
所有供应商公司信息，包含在 PURCHASING 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| VENDORID | INT | 非空 | 主键， 自增列 |
| ACCOUNTNO | VARCHAR(15) | 非空 | 供应商账户号 |
| NAME | VARCHAR(50) | 非空 | 供应商名称 |
| ACTIVEFLAG | BIT | 非空 | 0=不再使用供应商提供的产品 1=正在使用供应商提供的产品 CHECK 约束 |
| WEBURL | VARCHAR(1024) | 空 | 供应商服务 URL |
| CREDIT | INT | 非空 | 1=高级 2=很好 3=较好 4=一般 5=较差 CHECK 约束 |
VENDOR_ADDRESS
所有供应商地址信息，包含在 PURCHASING 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| VENDORID | INT | 非空 | 主键， 指向 VENDOR.VENDORID 的外键 |
| ADDRESSID | INT | 非空 | 主键，指向 ADDRESS.ADDRESSID 外键 |
| ADDRESS_TYPEID | INT | 非空 | 地址类型，指向 ADDRESS_TYPE.ADDRESS_TYPEID 外键 |
VENDOR_PERSON
供应商联系人信息表。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| VENDORID | INT | 非空 | 主键，指向 VENDOR.VENDORID 外键 |
| PERSONID | INT | 非空 | 主键，指向 PERSON.PERSONID 外键 |
| PERSON_TYPEID | INT | 非空 | 联系人的类型，指向 PERSON_TYPE.PERSON_TYPEID 的外键 |
PRODUCT
该公司售出的所有产品(图书)，包含在 PRODUCTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PRODUCTID | INT | 非空 | 主键，自增列 |
| NAME | VARCHAR(50) | 非空 | 产品名称 |
| AUTHOR | VARCHAR(25) | 非空 | 作者 |
| PUBLISHER | VARCHAR(50) | 非空 | 出版社 |
| PUBLISHTIME | DATE | 非空 | 出版时间 |
| PRODUCT_SUBCATEGORYID | INT | 非空 | 产品所属子类别，PRODUCT_SUBCATEGORY .PRODUCT_SUBCATEGORYID 的外键 |
| PRODUCTNO | VARCHAR(25) | 非空 | 唯一产品标识号， unique 约束 |
| DESCRIPTION | TEXT | 空 | 产品的说明\\简介 |
| PHOTO | IMAGE | 空 | 产品的照片 |
| SATETYSTOCKLEVEL | SMALLINT | 非空 | 最小库存量 |
| ORIGINALPRICE | DEC(19,4) | 非空 | 原价(初始价格) |
| NOWPRICE | DEC(19,4) | 非空 | 当前销售价格 |
| DISCOUNT | DECIMAL(2,1) | 非空 | 折扣 |
| TYPE | VARCHAR(5) | 空 | 产品开本规格，如 16 开 |
| PAPERTOTAL | INT | 空 | 总页数 |
| WORDTOTAL | INT | 空 | 总字数 |
| SELLSTARTTIME | DATE | 非空 | 开始销售日期 |
| SELLENDTIME | DATE | 空 | 停止销售的日期 |
PRODUCT_CATEGORY
产品分类表，包含在 PRODUCTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PRODUCT_CATEGORYID | INT | 非空 | 产品类别 ID，主键，自增列 |
| NAME | VARCHAR(50) | 非空 | 产品类别名称 |
PRODUCT_SUBCATEGORY
产品子分类表，包含在 PRODUCTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PRODUCT_SUBCATEGORYID | INT | 非空 | 产品子类别 ID，主键,自增列 |
| PRODUCT_CATEGORYID | INT | 非空 | 产品类别 ID |
| NAME | VARCHAR(50) | 非空 | 产品类别名称 |
PRODUCT_INVENTORY
产品的库存信息，包含在 PRODUCTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PRODUCTID | INT | 非空 | 产品标识，指向 PRODUCT.PRODUCTID 的外键 |
| LOCATIONID | INT | 非空 | 库存位置标识，指向 LOCATION.LOCATIONID 的外键 |
| QUANTITY | INT | 非空 | 库存位置的产品数量 |
PRODUCT_VENDOR
产品分类表，包含在 PRODUCTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PRODUCTID | INT | 非空 | 主键，指向 PRODUCT.PRODUCTID 外键 |
| VENDORID | INT | 非空 | 主键，指向 VENDOR.VENDORID 外键 |
| STANDARDPRICE | DEC(19,4) | 非空 | 通常价格 |
| LASTPRICE | DEC(19,4) | 空 | 上次采购价格 |
| LASTDATE | DATE | 空 | 上次收到供应商产品的日期 |
| MINQTY | INT | 非空 | 应订购的最小定购数量 |
| MAXQTY | INT | 非空 | 应订购的最大定购数量 |
| ONORDERQTY | INT | 空 | 当前定购的数量 |
PRODUCT_REVIEW
已售产品的评论表，包含在 PRODCUTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PRODUCT_REVIEWID | INT | 非空 | 主键，自增列 |
| PRODUCTID | INT | 非空 | 指向 PRODUCT.PRODUCTID 外键 |
| NAME | VARCHAR(50) | 非空 | 评论人姓名 |
| REVIEWDATE | DATE | 非空 | 提交评论的日期 |
| VARCHAR(50) | 非空 | 评论人的 EMAIL 地址 | |
| RATING | INT | 非空 | 评论人给出的产品等级 范围 1-5，5 为最高级，CHECK 约束 |
| COMMENTS | TEXT | 空 | 评论人的注释 |
LOCATION
产品的库存地址信息，包含在 PRODUCTION 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| LOCATIONID | INT | 非空 | 主键，自增列 |
| NAME | VARCHAR(50) | 非空 | 地点说明 |
| PRODUCT_SUBCATEGORYID | INT | 非空 | 指向 PRODUCT_SUBCATEGORY(PRODUCT_SUBCATEGORYID)的外键 |
SALESORDER_HEADER
销售订单常规信息表，包含在 SALES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| SALESORDERID | INT | 非空 | 订单 ID，主键，自增列 |
| ORDERDATE | DATE | 非空 | 创建订单的日期 |
| DUEDATE | DATE | 非空 | 客户订单到期的日期 |
| STATUS | TINYINT | 非空 | 订单状态， CHECK 约束 1=待用户确认 2=待发货 3=已发货 4=已完成 5=意外终结 |
| ONLINEORDERFLAG | BIT | 非空 | 0=销售人员下的订单(包括电话订单) 1=在线订单 |
| CUSTOMERID | INT | 非空 | 客户标识号，指向 CUSTOMER.CUSTOMERID 的外键 |
| SALESPERSONID | INT | 非空 | 销售代表 ID，指向 SALESPERSON.SALESPERSONID 的外键 |
| ADDRESSID | INT | 非空 | 客户收货地址，指向 ADDRESS.ADDRESSID 外键 |
| SHIPMETHOD | BIT | 非空 | 发货方法 0=免费送货(仅限中心城区) 1=有偿送货(>=400 元，免费送货；<400，运费 10 元) |
| SUBTOTAL | DEC(19,4) | 非空 | 销售小计 计算方式:SUM(SALESORDER_DETAIL.LINETOTAL) |
| FREIGHT | DEC(19,4) | 非空 | 运费(非中城区，subtotal>=400，freight=0；<400， freight=10；中心城区， freight=0) |
| TOTAL | DEC(19,4) | 非空 | 应付款总计 SUBTOTAL+FREIGHT |
| COMMENTS | TEXT | 空 | 销售代表备注说明 |
SALESORDER_DETAIL
与特定销售订单关联的各个产品信息表，包含在 SALES 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| SALESORDERID | INT | 非空 | 主键，指向 SALESORDER_HEADER.SALESORDERID 的外键 |
| SALESORDER_DETAILID | INT | 非空 | 主键，确保数据唯一性的连续编号 |
| CARRIERNO | VARCHAR(25) | 非空 | 发货跟踪号 |
| PRODUCTID | INT | 非空 | 定购产品的 ID，指向 PRODUCT.PRODUCTID 的外键 |
| ORDERQTY | INT | 非空 | 每件产品定购数量 |
| LINETOTAL | DEC(19,4) | 非空 | 产品的销售小计 |
PURCHASEORDER_HEADER
采购订单常规信息，包含在 PURCHASING 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PURCHASEORDERID | INT | 非空 | 订单 ID，主键，自增列 |
| ORDERDATE | DATE | 非空 | 创建订单的日期 |
| STATUS | TINYINT | 非空 | 订单状态， CHECK 约束 0=等待批准 1=已批准 2=已拒绝 3=已完成 |
| EMPLOYEEID | INT | 非空 | 创建采购订单的雇员 ID，指向 EMPLOYEE.EMPLOYEEID 的外键 |
| VENDORID | INT | 非空 | 所采购产品的供应商 ID，指向 VENDOR.VENDORID 的外键 |
| SHIPMETHOD | VARCHAR(50) | 非空 | 发货方法 |
| SUBTOTAL | DEC(19,4) | 非空 | 产品价格小计 |
| TAX | DEC(19,4) | 非空 | 税额 |
| FREIGHT | DEC(19,4) | 非空 | 运费 |
| TOTAL | DEC(19,4) | 非空 | 应向供应商付款总计(SUBTOTAL+ TAX+ FREIGHT) |
PURCHASEORDER_DETAIL
与特定采购订单相关联的每个产品的采购信息，包含在 PURCHASING 模式中。
| 列 | 数据类型 | 是否为空 | 说明 |
|---|---|---|---|
| PURCHASEORDERID | INT | 非空 | 主键，指向 PURCHASEORDER_HEADER .PURCHASEORDERID 的外键 |
| PURCHASEORDER_DETAILID | INT | 非空 | 主键，确保数据唯一性的连续编号 |
| DUEDATE | DATE | 非空 | 希望从供应商收到产品的日期 |
| PRODUCTID | INT | 非空 | 定购产品的 ID，指向 PRODUCT.PRODUCTID 的外键 |
| ORDERQTY | INT | 非空 | 定购数量 |
| PRICE | DEC(19,4) | 非空 | 单件产品的价格 |
| SUBTOTAL | DEC(19,4) | 非空 | 产品价格小计(RPICE*ORDERQTY) |
| RECEIVEDQTY | DECIMAL(8,2) | 非空 | 实际从供应商收到的数量 |
| REJECTEDQTY | DECIMAL(8,2) | 非空 | 检查时拒收的数量 |
| STOCKEDQTY | DECIMAL(8,2) | 非空 | 纳入库存的数量 |
2.2 参考脚本
2.2.1 创建示例库
安装 DM 时，如果选择安装示例库，系统会自动安装一个名为 BOOKSHOP 的示例库。该示例库中，默认数据库名称为 DAMENG，默认实例名为 DMSERVER。
如果安装时没有选择安装示例库，可以通过如下 SQL 语句自行创建：
//创建示例库
CREATE TABLESPACE BOOKSHOP DATAFILE 'BOOKSHOP.DBF' size 50;
2.2.2 创建模式及表
//创建模式
CREATE SCHEMA RESOURCES AUTHORIZATION SYSDBA;
/
CREATE SCHEMA PERSON AUTHORIZATION SYSDBA;
/
CREATE SCHEMA SALES AUTHORIZATION SYSDBA;
/
CREATE SCHEMA PRODUCTION AUTHORIZATION SYSDBA;
/
CREATE SCHEMA PURCHASING AUTHORIZATION SYSDBA;
/
CREATE SCHEMA OTHER AUTHORIZATION SYSDBA;
/
//创建表
//CREATE ADDRESS
CREATE TABLE PERSON.ADDRESS
(ADDRESSID INT IDENTITY(1,1) PRIMARY KEY,
ADDRESS1 VARCHAR(60) NOT NULL,
ADDRESS2 VARCHAR(60),
CITY VARCHAR(30) NOT NULL,
POSTALCODE VARCHAR(15) NOT NULL) STORAGE (on BOOKSHOP);
//CREATE ADDRESS_TYPE
CREATE TABLE PERSON.ADDRESS_TYPE
(ADDRESS_TYPEID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PERSON
CREATE TABLE PERSON.PERSON
(
PERSONID INT IDENTITY(1,1) CLUSTER PRIMARY KEY,
SEX CHAR(1) NOT NULL,
NAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(50),
PHONE VARCHAR(25)) STORAGE (ON BOOKSHOP);
//CREATE PERSON_TYPE
CREATE TABLE PERSON.PERSON_TYPE
(PERSON_TYPEID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(256) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE DEPARTMENT
CREATE TABLE RESOURCES.DEPARTMENT(DEPARTMENTID INT IDENTITY(1,1) PRIMARY KEY,NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE EMPLOYEE
CREATE TABLE RESOURCES.EMPLOYEE(
EMPLOYEEID INT IDENTITY(1,1) PRIMARY KEY ,
NATIONALNO VARCHAR(18) NOT NULL,
PERSONID INT NOT NULL REFERENCES PERSON.PERSON(PERSONID),
LOGINID VARCHAR(256) NOT NULL,
TITLE VARCHAR(50) NOT NULL,
MANAGERID INT,
BIRTHDATE DATE NOT NULL,
MARITALSTATUS CHAR(1) NOT NULL,
PHOTO IMAGE,
HAIRDATE DATE NOT NULL,
SALARY DEC(19,4) NOT NULL
) STORAGE (ON BOOKSHOP);
//CREATE EMPLOYEE_ADDRESS
CREATE TABLE RESOURCES.EMPLOYEE_ADDRESS
(ADDRESSID INT NOT NULL REFERENCES PERSON.ADDRESS(ADDRESSID),
EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID)) STORAGE (ON BOOKSHOP);
//CREATE EMPLOYEE_DEPARTMENT
CREATE TABLE RESOURCES.EMPLOYEE_DEPARTMENT
(EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID),
DEPARTMENTID INT NOT NULL REFERENCES RESOURCES.DEPARTMENT(DEPARTMENTID),
STARTDATE DATE NOT NULL,
ENDDATE DATE) STORAGE (ON BOOKSHOP);
//CREATE CUSTOMER
CREATE TABLE SALES.CUSTOMER(CUSTOMERID INT IDENTITY(1,1) PRIMARY KEY ,PERSONID INT NOT NULL REFERENCES PERSON.PERSON(PERSONID)) STORAGE (ON BOOKSHOP);
//CREATE CUSTOMER_ADDRESS
CREATE TABLE SALES.CUSTOMER_ADDRESS
(CUSTOMERID INT REFERENCES SALES.CUSTOMER(CUSTOMERID),
ADDRESSID INT REFERENCES PERSON.ADDRESS(ADDRESSID),
ADDRESS_TYPEID INT NOT NULL REFERENCES PERSON.ADDRESS_TYPE(ADDRESS_TYPEID),
PRIMARY KEY (CUSTOMERID,ADDRESSID)) STORAGE (ON BOOKSHOP);
//CREATE PRODUCT_CATEGORY
CREATE TABLE PRODUCTION.PRODUCT_CATEGORY
(PRODUCT_CATEGORYID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PRODUCT_SUBCATEGORY
CREATE TABLE PRODUCTION.PRODUCT_SUBCATEGORY
(PRODUCT_SUBCATEGORYID INT IDENTITY(1,1) PRIMARY KEY,
PRODUCT_CATEGORYID INT NOT NULL ,
NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PRODUCT
CREATE TABLE PRODUCTION.PRODUCT
(PRODUCTID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
AUTHOR VARCHAR(25) NOT NULL,
PUBLISHER VARCHAR(50) NOT NULL,
PUBLISHTIME DATE NOT NULL,
PRODUCT_SUBCATEGORYID INT NOT NULL REFERENCES PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_SUBCATEGORYID),
PRODUCTNO VARCHAR(25) NOT NULL,
SATETYSTOCKLEVEL SMALLINT NOT NULL,
ORIGINALPRICE DEC(19,4) NOT NULL,
NOWPRICE DEC(19,4) NOT NULL,
DISCOUNT DECIMAL(2,1) NOT NULL,
DESCRIPTION TEXT,
PHOTO IMAGE,
TYPE VARCHAR(5),
PAPERTOTAL INT,
WORDTOTAL INT,
SELLSTARTTIME DATE NOT NULL,
SELLENDTIME DATE,
UNIQUE(PRODUCTNO)) STORAGE (ON BOOKSHOP);
//CREATE LOCATION
CREATE TABLE PRODUCTION.LOCATION
(LOCATIONID INT IDENTITY(1,1) PRIMARY KEY,
PRODUCT_SUBCATEGORYID INT NOT NULL REFERENCES PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_SUBCATEGORYID),
NAME VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PRODUCT_INVENTORY
CREATE TABLE PRODUCTION.PRODUCT_INVENTORY
(PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
LOCATIONID INT NOT NULL REFERENCES PRODUCTION.LOCATION(LOCATIONID),
QUANTITY INT NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PRODUCT_REVIEW
CREATE TABLE PRODUCTION.PRODUCT_REVIEW
(PRODUCT_REVIEWID INT IDENTITY(1,1) PRIMARY KEY,
PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
NAME VARCHAR(50) NOT NULL,
REVIEWDATE DATE NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
RATING INT NOT NULL CHECK(RATING IN(1,2,3,4,5)),
COMMENTS TEXT) STORAGE (ON BOOKSHOP);
//CREATE VENDOR
CREATE TABLE PURCHASING.VENDOR
(VENDORID INT IDENTITY(1,1) PRIMARY KEY,
ACCOUNTNO VARCHAR(15) NOT NULL,
NAME VARCHAR(50) NOT NULL,
ACTIVEFLAG BIT NOT NULL,
WEBURL VARCHAR(1024),
CREDIT INT NOT NULL CHECK(CREDIT IN(1,2,3,4,5))) STORAGE (ON BOOKSHOP);
//CREATE VENDOR_ADDRESS
CREATE TABLE PURCHASING.VENDOR_ADDRESS
(VENDORID INT NOT NULL REFERENCES PURCHASING.VENDOR(VENDORID),
ADDRESSID INT NOT NULL REFERENCES PERSON.ADDRESS(ADDRESSID),
ADDRESS_TYPEID INT NOT NULL REFERENCES PERSON.ADDRESS_TYPE(ADDRESS_TYPEID),
PRIMARY KEY (VENDORID,ADDRESSID)) STORAGE (ON BOOKSHOP);
//CREATE VENDOR_PERSON
CREATE TABLE PURCHASING.VENDOR_PERSON
(VENDORID INT NOT NULL REFERENCES PURCHASING.VENDOR(VENDORID),
PERSONID INT NOT NULL REFERENCES PERSON.PERSON(PERSONID),
PERSON_TYPEID INT NOT NULL REFERENCES PERSON.PERSON_TYPE(PERSON_TYPEID),
PRIMARY KEY (VENDORID,PERSONID)) STORAGE (ON BOOKSHOP);
//CREATE PRODUCT_VENDOR
CREATE TABLE PRODUCTION.PRODUCT_VENDOR
(PRODUCTID INT REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
VENDORID INT REFERENCES PURCHASING.VENDOR(VENDORID),
STANDARDPRICE DEC(19,4) NOT NULL,
LASTPRICE DEC(19,4),
LASTDATE DATE,
MINQTY INT NOT NULL,
MAXQTY INT NOT NULL,
ONORDERQTY INT,
PRIMARY KEY(PRODUCTID,VENDORID)) STORAGE (ON BOOKSHOP);
//CREATE SALESPERSON
CREATE TABLE SALES.SALESPERSON
(SALESPERSONID INT IDENTITY(1,1) PRIMARY KEY,
EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID),
SALESTHISYEAR DEC(19,4) NOT NULL,
SALESLASTYEAR DEC(19,4) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PURCHASEORDER_HEADER
CREATE TABLE PURCHASING.PURCHASEORDER_HEADER
(PURCHASEORDERID INT IDENTITY(1,1) PRIMARY KEY,
ORDERDATE DATE NOT NULL,
STATUS TINYINT NOT NULL CHECK(STATUS IN(0,1,2,3)),
EMPLOYEEID INT NOT NULL REFERENCES RESOURCES.EMPLOYEE(EMPLOYEEID),
VENDORID INT NOT NULL REFERENCES PURCHASING.VENDOR(VENDORID),
SHIPMETHOD VARCHAR(50) NOT NULL,
SUBTOTAL DEC(19,4) NOT NULL,
TAX DEC(19,4) NOT NULL,
FREIGHT DEC(19,4) NOT NULL,
TOTAL DEC(19,4) NOT NULL) STORAGE (ON BOOKSHOP);
//CREATE PURCHASEORDER_DETAIL
CREATE TABLE PURCHASING.PURCHASEORDER_DETAIL
(PURCHASEORDERID INT NOT NULL REFERENCES PURCHASING.PURCHASEORDER_HEADER(PURCHASEORDERID),
PURCHASEORDER_DETAILID INT NOT NULL,
DUEDATE DATE NOT NULL,
PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
ORDERQTY INT NOT NULL,
PRICE DEC(19,4) NOT NULL,
SUBTOTAL DEC(19,4) NOT NULL,
RECEIVEDQTY INT NOT NULL,
REJECTEDQTY INT NOT NULL,
STOCKEDQTY INT NOT NULL,
PRIMARY KEY(PURCHASEORDERID,PURCHASEORDER_DETAILID)) STORAGE (ON BOOKSHOP);
//CREATE SALESORDER_HEADER
CREATE TABLE SALES.SALESORDER_HEADER
(SALESORDERID INT IDENTITY(1,1) PRIMARY KEY,
ORDERDATE DATE NOT NULL,
DUEDATE DATE NOT NULL,
STATUS TINYINT NOT NULL CHECK(STATUS IN(0,1,2,3,4,5)),
ONLINEORDERFLAG BIT NOT NULL,
CUSTOMERID INT NOT NULL REFERENCES SALES.CUSTOMER(CUSTOMERID),
SALESPERSONID INT NOT NULL REFERENCES SALES.SALESPERSON(SALESPERSONID),
ADDRESSID INT NOT NULL REFERENCES PERSON.ADDRESS(ADDRESSID),
SHIPMETHOD BIT NOT NULL,
SUBTOTAL DEC(19,4) NOT NULL,
FREIGHT DEC(19,4) NOT NULL,
TOTAL DEC(19,4) NOT NULL,
COMMENTS TEXT) STORAGE (ON BOOKSHOP);
--CREATE SALESORDER_DETAIL
CREATE TABLE SALES.SALESORDER_DETAIL
(SALESORDERID INT NOT NULL REFERENCES SALES.SALESORDER_HEADER(SALESORDERID),
SALESORDER_DETAILID INT NOT NULL,
CARRIERNO VARCHAR(25) NOT NULL,
PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
ORDERQTY INT NOT NULL,
LINETOTAL DEC(19,4) NOT NULL,
PRIMARY KEY(SALESORDERID,SALESORDER_DETAILID)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.DEPARTMENT
CREATE TABLE OTHER.DEPARTMENT
(
HIGH_DEP VARCHAR(50),
DEP_NAME VARCHAR(50)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.EMPSALARY
CREATE TABLE OTHER.EMPSALARY
(
ENAME CHAR(10),
EMPNO NUMERIC(4),
SAL NUMERIC(4)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.ACCOUNT
CREATE TABLE OTHER.ACCOUNT
(
"ACCOUNT_ID" INTEGER NOT NULL,
"BAL" DEC(10,2),
PRIMARY KEY("ACCOUNT_ID"))STORAGE (ON BOOKSHOP);
//CREATE OTHER.ACTIONS
CREATE TABLE OTHER.ACTIONS
(
"ACCOUNT_ID" INTEGER NOT NULL,
"OPER_TYPE" CHAR(1),
"NEW_VALUE" DEC(10,2),
"STATUS" VARCHAR(50),
PRIMARY KEY("ACCOUNT_ID"))STORAGE (ON BOOKSHOP);
//CREATE OTHER.READER
CREATE TABLE OTHER.READER
(
READER_ID INT PRIMARY KEY,
NAME VARCHAR(30),
AGE SMALLINT,
GENDER CHAR,
MAJOR VARCHAR(30)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.READERAUDIT
CREATE TABLE OTHER.READERAUDIT
(
CHANGE_TYPE CHAR NOT NULL,
CHANGED_BY VARCHAR(8) NOT NULL,
OP_TIMESTAMP DATE NOT NULL,
OLD_READER_ID INT,
OLD_NAME VARCHAR(30),
OLD_AGE SMALLINT,
OLD_GENDER CHAR,
OLD_MAJOR VARCHAR(30),
NEW_READER_ID INT,
NEW_NAME VARCHAR(30),
NEW_AGE SMALLINT,
NEW_GENDER CHAR,
NEW_MAJOR VARCHAR(30)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.DEPTTAB
CREATE TABLE OTHER.DEPTTAB
(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(15),
LOC VARCHAR(25)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.EMPTAB
CREATE TABLE OTHER.EMPTAB
(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(10),
SAL FLOAT,
DEPTNO INT) STORAGE (on BOOKSHOP);
//CREATE OTHER.SALGRADE
CREATE TABLE OTHER.SALGRADE
(
LOSAL FLOAT,
HISAL FLOAT,
JOB_CLASSIFICATION VARCHAR(10)) STORAGE (ON BOOKSHOP);
//CREATE OTHER.COMPANYHOLIDAYS
CREATE TABLE OTHER.COMPANYHOLIDAYS
(
HOLIDAY DATE) STORAGE (ON BOOKSHOP);
2.2.3 插入数据
//插入数据
//INSERT ADDRESS
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区369号金地太阳城56-1-202','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区369号金地太阳城57-2-302','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('青山区青翠苑1号','','武汉市青山区','430080');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('武昌区武船新村115号','','武汉市武昌区','430063');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('汉阳大道熊家湾15号','','武汉市汉阳区','430050');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区保利花园50-1-304','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区保利花园51-1-702','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区关山春晓51-1-702','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('江汉区发展大道561号','','武汉市江汉区','430023');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('江汉区发展大道555号','','武汉市江汉区','430023');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('武昌区武船新村1号','','武汉市武昌区','430063');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('江汉区发展大道423号','','武汉市江汉区','430023');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区关山春晓55-1-202','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区关山春晓10-1-202','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区关山春晓11-1-202','','武汉市洪山区','430073');
INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('洪山区光谷软件园C1_501','','武汉市洪山区','430073');
//INSERT ADDRESS_TYPE
INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('发货地址');
INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('送货地址');
INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('家庭地址');
INSERT INTO PERSON.ADDRESS_TYPE(NAME) VALUES('公司地址');
//INSERT DEPARTMENT
INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('采购部门');
INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('销售部门');
INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('人力资源');
INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('行政部门');
INSERT INTO RESOURCES.DEPARTMENT(NAME) VALUES('广告部');
//INSERT PERSON
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','李丽','lily@sina.com','02788548562');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M','王刚','','02787584562');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M','李勇','','02782585462');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','郭艳','','02787785462');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','孙丽','','13055173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M','黄非','','13355173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','王菲','','13255173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M','张平','','13455173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('M','张红','','13555173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','刘佳','','13955173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','王南','','15955173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','李飞','','15954173012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','张大海','','15955673012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','王宇轩','','15955175012');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','桑泽恩','','15955173024');
INSERT INTO PERSON.PERSON(SEX,NAME,EMAIL,PHONE) VALUES('F','刘青','','15955173055');
INSERT INTO PERSON.PERSON(SEX,NAME,PHONE) VALUES('F','杨凤兰','02785584662');
//INSERT PERSON_TYPE
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('采购经理');
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('采购代表');
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('销售经理');
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('销售代表');
//INSERT CUSTOMER
INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM PERSON.PERSON WHERE NAME='刘青'));
INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM PERSON.PERSON WHERE NAME='桑泽恩'));
INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM PERSON.PERSON WHERE NAME='王宇轩'));
INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM PERSON.PERSON WHERE NAME='张大海'));
INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM PERSON.PERSON WHERE NAME='李飞'));
INSERT INTO SALES.CUSTOMER(PERSONID) VALUES((SELECT PERSONID FROM PERSON.PERSON WHERE NAME='王南'));
//INSERT CUSTOMER_ADDRESS
INSERT INTO SALES.CUSTOMER_ADDRESS(CUSTOMERID,ADDRESSID,ADDRESS_TYPEID)
SELECT CUSTOMERID,11,2 FROM SALES.CUSTOMER;
INSERT INTO SALES.CUSTOMER_ADDRESS(CUSTOMERID,ADDRESSID,ADDRESS_TYPEID)
SELECT CUSTOMERID,12,3 FROM SALES.CUSTOMER;
//INSERT EMPLOYEE
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921197908051523',1,'L1','总经理','','1979-08-05','S','','2002-05-02',40000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921198008051523',2,'L2','销售经理',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='总经理'),'1980-08-05','S','','2002-05-02', 26000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921198408051523',3,'L3','采购经理',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='总经理'),'1981-08-05','S','','2002-05-02',23000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921198208051523',4,'L4','销售代表',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='销售经理'),'1982-08-05','S','','2002-05-02',15000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921198308051523',5,'L5','销售代表',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='销售经理'),'1983-08-05','S','','2002-05-02',16000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921198408051523',6,'L6','采购代表',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='采购经理'),'1984-08-05','S','','2005-05-02',12000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921197708051523',7,'L7','人力资源部经理',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='总经理'),'1977-08-05','M','','2002-05-02',25000);
INSERT INTO RESOURCES.EMPLOYEE(NATIONALNO,PERSONID,LOGINID,TITLE,MANAGERID,BIRTHDATE,MARITALSTATUS,PHOTO,HAIRDATE,SALARY)
VALUES('420921198008071523',8,'L8','系统管理员',(SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE='人力资源部经理'),'1980-08-07','S','','2004-05-02',20000);
//INSERT EMPLOYEE_DEPARTMENT
INSERT INTO RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)
SELECT EMPLOYEEID,'2','2005-02-01',null FROM RESOURCES.EMPLOYEE WHERE RESOURCES.EMPLOYEE.TITLE='销售代表' OR RESOURCES.EMPLOYEE.TITLE='销售经理';
INSERT INTO RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)
SELECT EMPLOYEEID,'1','2005-02-01',null FROM RESOURCES.EMPLOYEE WHERE RESOURCES.EMPLOYEE.TITLE='采购代表' OR RESOURCES.EMPLOYEE.TITLE='采购经理';
INSERT INTO RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)
SELECT EMPLOYEEID,'3','2005-02-01',null FROM RESOURCES.EMPLOYEE WHERE RESOURCES.EMPLOYEE.TITLE='系统管理员';
INSERT INTO RESOURCES.EMPLOYEE_DEPARTMENT(EMPLOYEEID,DEPARTMENTID,STARTDATE,ENDDATE)
SELECT EMPLOYEEID,'4','2001-02-01',null FROM RESOURCES.EMPLOYEE WHERE RESOURCES.EMPLOYEE.TITLE='总经理';
//INSERT EMPLOYEE_ADDRESS
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(1,1);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(2,2);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(3,3);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(4,4);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(5,5);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(6,6);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(7,7);
INSERT INTO RESOURCES.EMPLOYEE_ADDRESS(EMPLOYEEID,ADDRESSID) VALUES(8,8);
//INSERT SALES.SALESPERSON
INSERT INTO SALES.SALESPERSON(EMPLOYEEID,SALESTHISYEAR,SALESLASTYEAR)
SELECT EMPLOYEEID,8,10 FROM RESOURCES.EMPLOYEE WHERE TITLE='销售代表';
//INSERT VENDOR
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','上海画报出版社','1','','2');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','长江文艺出版社','1','','2');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','北京十月文艺出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','人民邮电出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','清华大学出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','中华书局','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','广州出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','上海出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','21世纪出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','外语教学与研究出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','机械工业出版社','1','','1');
INSERT INTO PURCHASING.VENDOR(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT)
VALUES('00','文学出版社','1','','1');
//INSERT VENDOR_ADDRESS
INSERT INTO PURCHASING.VENDOR_ADDRESS(VENDORID,ADDRESSID,ADDRESS_TYPEID)
SELECT VENDORID,9,1 FROM PURCHASING.VENDOR;
INSERT INTO PURCHASING.VENDOR_ADDRESS(VENDORID,ADDRESSID,ADDRESS_TYPEID)
SELECT VENDORID,10,4 FROM PURCHASING.VENDOR;
//INSERT VENDOR_PERSON
INSERT INTO PURCHASING.VENDOR_PERSON(VENDORID,PERSONID,PERSON_TYPEID)
SELECT VENDORID,9,4 FROM PURCHASING.VENDOR;
//INSERT PRODUCT_CATEGORRY
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('小说');
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('文学');
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('计算机');
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('英语');
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('管理');
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('少儿');
INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES('金融');
//INSERT PRODUCT_SUBCATEGORY
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='小说'),'世界名著');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='小说'),'武侠');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='小说'),'科幻');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='小说'),'四大名著');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='小说'),'军事');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='小说'),'社会');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME) VALUES(10,'历史');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'文集');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'纪实文学');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'文学理论');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'中国古诗词');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'中国现当代诗');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'戏剧');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='文学'),'民间文学');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'计算机理论');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'计算机体系结构');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'操作系统');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'程序设计');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'数据库');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'软件工程');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'信息安全');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='计算机'),'多媒体');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='英语'),'英语词汇');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='英语'),'英语语法');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='英语'),'英语听力');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='英语'),'英语口语');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='英语'),'英语阅读');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='英语'),'英语写作');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='管理'),'行政管理');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='管理'),'项目管理');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='管理'),'质量管理与控制');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='管理'),'商业道德');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='管理'),'经营管理');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='管理'),'财务管理');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='少儿'),'幼儿启蒙');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='少儿'),'益智游戏');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='少儿'),'童话');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='少儿'),'卡通');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='少儿'),'励志');
INSERT INTO PRODUCTION.PRODUCT_SUBCATEGORY(PRODUCT_CATEGORYID,NAME)
VALUES((SELECT PRODUCT_CATEGORYID FROM PRODUCTION.PRODUCT_CATEGORY WHERE NAME='少儿'),'少儿英语');
//INSERT PRODUCT
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('红楼梦','曹雪芹,高鹗','中华书局','2005-4-1','9787101046120',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='四大名著'),'10','19','15.2','8.0','曹雪芹，是中国文学史上最伟大也是最复杂的作家，《红楼梦》也是中国文学史上最伟大而又最复杂的作品。《红楼梦》写的是封建贵族的青年贾宝玉、林黛玉、薛宝钗之间的恋爱和婚姻悲剧，而且以此为中心，写出了当时具有代表性的贾、王、史、薛四大家族的兴衰，其中又以贾府为中心，揭露了封建社会后期的种种黑暗和罪恶，及其不可克服的内在矛盾，对腐朽的封建统治阶级和行将崩溃的封建制度作了有力的批判，使读者预感到它必然要走向覆灭的命运。本书是一部具有高度思想性和高度艺术性的伟大作品，从本书反映的思想倾向来看，作者具有初步的民主主义思想，他对现实社会包括宫廷及官场的黑暗，封建贵族阶级及其家庭的腐朽，封建的科举制度、婚姻制度、奴婢制度、等级制度，以及与此相适应的社会统治思想即孔孟之道和程朱理学、社会道德观念等等，都进行了深刻的批判并且提出了朦胧的带有初步民主主义性质的理想和主张。这些理想和主张正是当时正在滋长的资本主义经济萌芽因素的曲折反映。','','16','943','933000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('水浒传','施耐庵，罗贯中','中华书局','2005-4-1','9787101046137',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='四大名著'),'10','19','14.3','7.5','《水浒传》是宋江起义故事在民间长期流传基础上产生出来的，吸收了民间文学的营养。《水浒传》是我国人民最喜爱的古典长篇白话小说之一。它产生于明代，是在宋、元以来有关水浒的故事、话本、戏曲的基础上，由作者加工整理、创作而成的。全书以宋江领导的农民起义为主要题材，艺术地再现了中国古代人民反抗压迫、英勇斗争的悲壮画卷。作品充分暴露了封建统治阶级的腐朽和残暴，揭露了当时尖锐对立的社会矛盾和“官逼民反”的残酷现实，成功地塑造了鲁智深、李逵、武松、林冲、阮小七等一批英雄人物。小说故事情节曲折，语言生动，人物性格鲜明，具有高度的艺术成就。但作品歌颂、美化宋江，鼓吹“忠义”和“替天行道”，表现出严重的思想局限。','','16','922','912000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('老人与海','海明威','上海出版社','2006-8-1','9787532740093',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='世界名著'),'10','10','6.1','6.1','海明威(1899一1961)，美国著名作家、诺贝尔文学奖获得者。《老人与海》是他最具代表性的作品之一。','','16','98','67000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('射雕英雄传(全四册)','金庸 ','广州出版社','2005-12-1','9787807310822',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='武侠'),'10','32','21.7','6.8','自幼家破人亡的郭靖，随母流落蒙古大漠，这傻头傻脑但有情有义的小伙子倒也是有福气，他不但习得了江南六怪的绝艺、全真教马钰的内功、洪七公的隆龙十八掌、双手互博之术、九阴真经等盖世武功，还让古灵精怪的小美女黄蓉这辈子跟定了他。这部原名『大漠英雄传』的小说是金庸小说中最广为普罗大众接受、传颂的一部，其中出了许多有名又奇特的人物，东邪西毒南帝北丐中神通，还有武功灵光、脑袋不灵光的老顽童周伯通，他们有特立独行的性格、作为和人生观，让人叹为观止。书中对历史多有着墨，中原武林及蒙古大漠的生活情形随着人物的生长环境变迁而有不同的叙述，异族统治之下的小老百姓心情写来丝丝入扣，本书对情的感觉是很含蓄的，尤其是郭靖与拖雷、华筝无猜的童年之谊，他与江南六怪的师生之谊等等，还有全真七子中长春子丘处机的侠义行为及其与郭杨二人风雪中的一段情谊，也有很豪气的叙述。神算子瑛姑及一灯大师和周伯通的一场孽恋，是最出乎人意料的一段，成人世界的恋情可比小儿女的青涩恋燕还复杂多了。郭靖以扭胜巧的人生经历和「为国为民，侠之大者」的儒侠风范，也是书中最大要旨。距离这本书完成的时间已有四十年了，书中的单纯诚朴的人物性格还深深的留在读者心中，本书故事也多改编成电影、电视剧等，受欢迎程度可见一斑。','','16','','1153000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('鲁迅文集(小说、散文、杂文)全两册','鲁迅','','2006-9-1','9787509000724',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='文集'),'10','39.8','20','5.0','','','16','684','680000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('长征','王树增 ','人民文学出版社','2006-9-1','9787020057986',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='纪实文学'),'10','53','37.7','6.4','','','16','683','670000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('数据结构(C语言版)(附光盘)','严蔚敏，吴伟民','清华大学出版社 ','2007-3-1','9787302147510',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='计算机理论'),'10','30','25.5','8.5','《数据结构》(C语言版)是为“数据结构”课程编写的教材，也可作为学习数据结构及其算法的C程序设计的参数教材。 本书的前半部分从抽象数据类型的角度讨论各种基本类型的数据结构及其应用','','8','334','493000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('工作中无小事','陈满麒','机械工业出版社','2006-1-1','9787111182252',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='行政管理'),'10','16.8','11.4','6.8','本书立足于当今企业中常见的轻视小事，做事浮躁等现象，从人性的弱点这一独特角度，挖掘出员工轻视小事的根本原因，具有深厚的人文关怀，极易引起员工的共鸣。它有助于员工端正心态，摒弃做事贪大的浮躁心理，把小事做好做到位，从而提高整个企业的工作质量。当重视小事成为员工的一种习惯，当责任感成为一种生活态度，他们将会与胜任、优秀、成功同行，责任、忠诚、敬业也将不再是一句空洞的企业宣传口号。本书是一本提升企业竞争力、建设企业文化的指导手册，一本员工素质培训的完美读本，一本所有公务员、公司职员的必读书。','','8','152','70000','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('突破英文基础词汇','刘毅','外语教学与研究出版社','2003-8-1','9787560035024',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语词汇'),'10','15.9','11.1','7.0','本书所列单词共计1300个，加上各词的衍生词、同义词及反义词，则实际收录约3000词，均为平时最常用、最容易接触到的单词。详细列出各词的国际音标、词性说明及中文解释，省却查字典的麻烦。每一课分为五个部分，以便于分段记忆。在课前有预备测验，每一部分之后有习题，课后有效果检测，可借助于重复测验来加深对单词的印象，并学习如何活用单词。','','8','350','','2006-03-20','');
INSERT INTO PRODUCTION.PRODUCT(NAME,AUTHOR,PUBLISHER,PUBLISHTIME,PRODUCTNO,PRODUCT_SUBCATEGORYID,SATETYSTOCKLEVEL,ORIGINALPRICE,NOWPRICE,DISCOUNT,DESCRIPTION,PHOTO,TYPE,PAPERTOTAL,WORDTOTAL,SELLSTARTTIME,SELLENDTIME)
VALUES('噼里啪啦丛书(全7册)','(日)佐佐木洋子','21世纪出版社','1901-01-01','9787539125992',(SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='幼儿启蒙'),'10','58','42','6.1','噼里啪啦系列丛书包括：《我要拉巴巴》《我去刷牙》《我要洗澡》《你好》《草莓点心》《车来了》《我喜欢游泳》共7册。 这是日本画家佐佐木洋子编绘的，分别描绘孩子在刷牙、洗澡、游玩、吃点心等各种时候所碰到的问题，以风趣的方式教会他们人生的最初的知识。书中的图形不仅夸张诱人，而且采用了一些局部折叠的方式，在书页中可以不时翻开一些折叠面，让人看到图画内部的东西，这是很符合低幼儿童的阅读心理的。','','8','','','2006-03-20','');
//INSERT LOCATION
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='世界名著'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='武侠'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='科幻'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='军事'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='社会'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='文集'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='纪实文学'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='文学理论'),'库存1-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='中国古诗词'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='中国现当代诗'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='戏剧'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='民间文学'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='计算机理论'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='计算机体系结构'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='操作系统'),'库存1-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='程序设计'),'库存1-货架3');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='数据库'),'库存1-货架3');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='软件工程'),'库存1-货架3');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='信息安全'),'库存1-货架3');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='多媒体'),'库存1-货架3');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语词汇'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语语法'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语听力'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语口语'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语阅读'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='英语写作'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='行政管理'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='项目管理'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='质量管理与控制'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='商业道德'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='经营管理'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='财务管理'),'库存1-货架4');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='幼儿启蒙'),'库存2-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='益智游戏'),'库存2-货架1');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='童话'),'库存2-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='卡通'),'库存2-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='励志'),'库存2-货架2');
INSERT INTO PRODUCTION.LOCATION(PRODUCT_SUBCATEGORYID,NAME)
VALUES((SELECT PRODUCT_SUBCATEGORYID FROM PRODUCTION.PRODUCT_SUBCATEGORY WHERE NAME='少儿英语'),'库存2-货架2');
//INSERT PRODUCT_INVENTORY
INSERT INTO PRODUCTION.PRODUCT_INVENTORY(PRODUCTID,LOCATIONID,QUANTITY)
SELECT T1.PRODUCTID,T2.LOCATIONID,100 FROM PRODUCTION.PRODUCT T1,PRODUCTION.LOCATION T2
WHERE T1.PRODUCT_SUBCATEGORYID=T2.PRODUCT_SUBCATEGORYID;
//INSERT PRODUCT_REVIEW
INSERT INTO PRODUCTION.PRODUCT_REVIEW(PRODUCTID,NAME,REVIEWDATE,EMAIL,RATING,COMMENTS)
SELECT PRODUCTID,'刘青','2007-05-06','zhangping@sina.com','1','送货快' from PRODUCTION.PRODUCT;
INSERT INTO PRODUCTION.PRODUCT_REVIEW(PRODUCTID,NAME,REVIEWDATE,EMAIL,RATING,COMMENTS)
SELECT PRODUCTID,'桑泽恩','2007-05-06','zhangping@sina.com','1','服务态度好' from PRODUCTION.PRODUCT;
//INSERT PRODUCT_VENDOR
INSERT INTO PRODUCTION.PRODUCT_VENDOR(PRODUCTID,VENDORID,STANDARDPRICE,LASTPRICE,LASTDATE,MINQTY,MAXQTY,ONORDERQTY)
SELECT PRODUCTID,VENDORID,25,'','','10','100','' FROM PRODUCTION.PRODUCT,PURCHASING.VENDOR WHERE PRODUCTION.PRODUCT.PUBLISHER=PURCHASING.VENDOR.NAME;
//INSER SALESORDER_HEADER
INSERT INTO SALES.SALESORDER_HEADER(ORDERDATE,DUEDATE,STATUS,ONLINEORDERFLAG,CUSTOMERID,SALESPERSONID,ADDRESSID,SHIPMETHOD,SUBTOTAL,FREIGHT,TOTAL,COMMENTS)
VALUES ('2007-05-06','2007-5-07',2,1,1,2,3,0,36.9,0,36.9,'上午送到');
INSERT INTO SALES.SALESORDER_HEADER(ORDERDATE,DUEDATE,STATUS,ONLINEORDERFLAG,CUSTOMERID,SALESPERSONID,ADDRESSID,SHIPMETHOD,SUBTOTAL,FREIGHT,TOTAL,COMMENTS)
VALUES ('2007-05-07','2007-5-07',1,1,1,1,1,0,36.9,0,36.9,'上午送到');
//INSERT SALESORDER_DETAIL
INSERT INTO SALES.SALESORDER_DETAIL(SALESORDERID,SALESORDER_DETAILID,CARRIERNO,PRODUCTID,ORDERQTY,LINETOTAL)
SELECT SALESORDERID,'1','2007052',1,1,15.2 FROM SALES.SALESORDER_HEADER;
INSERT INTO SALES.SALESORDER_DETAIL(SALESORDERID,SALESORDER_DETAILID,CARRIERNO,PRODUCTID,ORDERQTY,LINETOTAL)
SELECT SALESORDERID,'2','2007053',3,1,21.7 FROM SALES.SALESORDER_HEADER;
UPDATE SALES.SALESPERSON SET SALESLASTYEAR = 20.0000 WHERE SALESPERSONID = 2;
INSERT INTO PURCHASING.PURCHASEORDER_HEADER(ORDERDATE,STATUS,EMPLOYEEID,VENDORID,SHIPMETHOD,SUBTOTAL,TAX,FREIGHT,TOTAL)
VALUES('2006-7-21',1,6,5,'快递',5000.00,600.00,800.00,6400.00);
//INSERT DEPARTMENT
INSERT INTO OTHER.DEPARTMENT VALUES(NULL, '总公司');
INSERT INTO OTHER.DEPARTMENT VALUES('总公司', '服务部');
INSERT INTO OTHER.DEPARTMENT VALUES('总公司', '采购部');
INSERT INTO OTHER.DEPARTMENT VALUES('总公司', '财务部');
INSERT INTO OTHER.DEPARTMENT VALUES('服务部', '网络服务部');
INSERT INTO OTHER.DEPARTMENT VALUES('服务部', '读者服务部');
INSERT INTO OTHER.DEPARTMENT VALUES('服务部', '企业服务部');
INSERT INTO OTHER.DEPARTMENT VALUES('读者服务部', '书籍借阅服务部');
INSERT INTO OTHER.DEPARTMENT VALUES('读者服务部', '书籍阅览服务部');
//INSERT EMPSALARY
INSERT INTO OTHER.EMPSALARY VALUES ('KING',7839,5000);
INSERT INTO OTHER.EMPSALARY VALUES ('SCOTT',7788,3000);
INSERT INTO OTHER.EMPSALARY VALUES ('FORD',7902,3000);
INSERT INTO OTHER.EMPSALARY VALUES ('JONES',7566,2975);
INSERT INTO OTHER.EMPSALARY VALUES ('BLAKE',7698,2850);
INSERT INTO OTHER.EMPSALARY VALUES ('CLARK',7782,2450);
INSERT INTO OTHER.EMPSALARY VALUES ('ALLEN',7499,1600);
INSERT INTO OTHER.EMPSALARY VALUES ('TURNER',7844,1500);
INSERT INTO OTHER.EMPSALARY VALUES ('MILLER',7934,1300);
INSERT INTO OTHER.EMPSALARY VALUES ('WARD',7521,1250);
INSERT INTO OTHER.EMPSALARY VALUES ('MARTIN',7654,1250);
INSERT INTO OTHER.EMPSALARY VALUES ('ADAMS',7876,1100);
INSERT INTO OTHER.EMPSALARY VALUES ('JAMES',7900,950);
INSERT INTO OTHER.EMPSALARY VALUES ('SMITH',7369,800);
//INSERT ACCOUNT
INSERT INTO OTHER.ACCOUNT VALUES(1,1000);
INSERT INTO OTHER.ACCOUNT VALUES(2,2000);
INSERT INTO OTHER.ACCOUNT VALUES(3,1500);
INSERT INTO OTHER.ACCOUNT VALUES(4,6500);
INSERT INTO OTHER.ACCOUNT VALUES(5,500);
//INSERT ACTIONS
INSERT INTO OTHER.ACTIONS VALUES(3,'U',599,NULL);
INSERT INTO OTHER.ACTIONS VALUES(6,'I',20099,NULL);
INSERT INTO OTHER.ACTIONS VALUES(5,'D',NULL,NULL);
INSERT INTO OTHER.ACTIONS VALUES(7,'U',1599,NULL);
INSERT INTO OTHER.ACTIONS VALUES(1,'I',399,NULL);
INSERT INTO OTHER.ACTIONS VALUES(9,'D',NULL,NULL);
INSERT INTO OTHER.ACTIONS VALUES(10,'X',NULL,NULL);
//INSERT READER
INSERT INTO OTHER.READER VALUES(10, 'Bill', 19, 'M', 'Computer');
INSERT INTO OTHER.READER VALUES(11, 'Susan', 18, 'F', 'History');
INSERT INTO OTHER.READER VALUES(12, 'John', 19, 'M', 'Computer');
