实验室3 sql server 产品销售数据库
某公司的产品销售数据库company，company数据库中存在人事表employee、客户表customer、销售表sales、销售明细表sale_item、产品表product。各表的结构如下：
员工人事表employee
emp_no | char(5) | Not null | primary key | 员工编号 |
|---|---|---|---|---|
emp_name | varchar(10) | Not null | 员工姓名 | |
sex | char(2) | Not null | 性别 | |
dept | varchar(10) | Not null | 所属部门 | |
title | varchar(10) | Not null | 职称 | |
date_hired | datetime | Not null | 雇佣日 | |
birthday | datetime | Null | 生日 | |
salary | int | Not null | 薪水 | |
telephone | varchar(20) | Null | 电话 | |
addr | varchar(50) | null | 住址 |
客户表customer
cust_id | char(5) | Not null | primary key | 客户号 |
|---|---|---|---|---|
cust_name | varchar(20) | Not null | 客户名称 | |
addr | varchar(40) | Not null | 客户住址 | |
tel_no | varchar(20) | Not null | 客户电话 | |
zip | char(6) | null | 邮政编码 |
销售主表sales
order_no | int | Not null | primary key | 订单编号 |
|---|---|---|---|---|
cust_id | char(5) | Not null | 客户号 | |
sale_id | char(5) | Not null | 业务员编号 | |
tot_amt | numeric(9,2) | Not null | 订单金额 | |
order_date | datetime | Not null | 订货日期 |
销货明细表sale_item
order_no | int | Not null | 订单编号 | |
|---|---|---|---|---|
prod_id | char(5) | Not null | 产品编号 | |
qty | int | Not null | 销售数量 | |
unit_price | numeric(7,2) | Not null | 单价 | |
order_date | datetime | null | 订单日期 |
产品名称表product
prod_id | char(5) | Not null | primary key | 产品编号 |
|---|---|---|---|---|
prod_name | varchar(20) | Not null | 产品名称 |
分别在SQL SERVER 2000企业管理器和在查询分析分析器中使用T—SQL语句完成以下操作：
1．在数据库company中创建以上五张表，并设置各表的主键。
操作步骤：
/*员工人事表*/
create table employee (
emp_no char(5) Not null primary key,
emp_name varchar(10) Not null,
sex char(2) Not null,
dept varchar(10) Not null,
title varchar(10) Not null,
date_hired datetime Not null,
birthday datetime Null,
salary int Not null,
telephone varchar(20) Null,
addr varchar(50) null
)
/*客户表*/
create table customer(
cust_id char(5) Not null primary key,
cust_name varchar(20) Not null,
addr varchar(40) Not null,
tel_no varchar(20) Not null,
zip char(6) null
)
/*销售主表*/
create table sales(
order_no int Not null primary key,
cust_id char(5) Not null ,
sale_id char(5) Not null ,
tot_amt numeric(9,2) Not null,
order_date datetime Not null
)
/*销货明细表*/
create table sale_item(
order_no int Not null,
prod_id char(5) Not null,
qty int Not null,
unit_price numeric(7,2) Not null,
order_date datetime null,
)
/*产品名称表*/
create table product(
prod_id char(5) Not null primary key,
prod_name varchar(20) Not null,
)/*人事表（employee）数据：*/
insert employeevalues('E0001','王大华','男','业务','经理','1976-10-13','1951-08-01',8000,'0218120440','上海市')
insert employeevalues('E0003','陈自强','男','会计','科长','1986-09-15','1963-06-09',4800,'0255344441','南京市')
insert employeevalues('E0014','周小梅','女','业务','职员','1996-03-01','1970-03-28',3200,'0218128079','上海市')
insert employeevalues('E0009','陈建国','男','管理','科长','1987-04-15','1967-09-01',4500,'0224507863','天津市')
insert employeevalues('E0017','林光华','男','业务','职员','1995-10-13','1973-08-17',3000,'0218344560','上海市')
insert employeevalues('E0006','李珠珠','女','管理','经理','1988-01-01','1961-07-12',6000,'0106750321','北京市')
insert employeevalues('E0002','李庄敬','男','人事','科长','1980-09-15','1958-05-13',8000,'0207180787','广州市')
insert employeevalues('E0010','王成功','男','信息','职员','1993-02-15','1969-04-15',4500,'0106543475','北京市')
insert employeevalues('E0013','陈中华','男','业务','职员','1993-02-15','1966-07-01',4300,'0224506541','天津市')
insert employeevalues('E0008','刘 刚','男','业务','职员','1994-11-01','1968-08-01',4000,'0218128727','上海市')
insert employeevalues('E0005','李珊珊','女','会计','职员','1990-03-20','1967-04-25',3800,'0218344787','上海市')
insert employeevalues('E0011','李小蓉','女','人事','职员','1994-11-01','1970-11-18',3000,'0811545412','重庆市')
insert employeevalues('E0012','蔡文钦','男','制造','厂长','1984-08-15','1960-07-21',5000,'0218120636','上海市')
insert employeevalues('E0015','张大山','男','制造','职员','1993-12-15','1968-09-23',3500,'0218344472','上海市')
insert employeevalues('E0007','吴铁雄','男','信息','科长','1989-10-01','1965-04-18',5000,'0277758521','武汉市')
insert employeevalues('E0016','方美美','女','业务','职员','1992-05-20','1966-06-23',4000,'0218507470','上海市')
insert employeevalues('E0004','刘中兴','男','制造','经理','1984-05-01','1960-05-23',6000,'0218506110','上海市')
insert employeevalues('E0019','王仁华','男','信息','经理','1985-09-15','1959-03-24',6000,'0218128091','上海市')
insert employeevalues('E0020','陈火旺','男','业务','职员','1992-08-01','1964-05-12',4000,'0224120477','天津市')
insert employeevalues('E0018','林中正','男','管理','总经理','1974-10-01','1953-05-04',10000,'0218120564','上海市')
/* 客户（customer）表数据：*/
insert customervalues('C0001','ABC股份有限公司','上海市','电话甲','100')
insert customervalues('C0002','客户乙','天津市','电话乙','400')
insert customervalues('C0003','客户丙','北京市','电话丙','800')
insert customervalues('C0004','客户丁','上海市','电话丁','104')
insert customervalues('C0005','客户戊','北京市','电话戊','803')
insert customervalues('C0006','客户己','上海市','电话己','103')
insert customervalues('C0007','客户庚','上海市','电话庚','102')
/*销售（sales）主表数据:*/
insertsales values (10002,'C0002','E0013',22700.00,'1996-11-10','I000000002')
insertsales values(10003,'C0003','E0014',13960.00,'1996-10-15','I000000003')
insertsales values(10004,'C0003','E0014',33000.00,'1996-11-10','I000000004')
insertsales values (10001,'C0001','E0008',60000.00,'1996-11-10','I000000001')
insertsales values (10007,'C0007','E0008',20000.00,'1996-11-10','I000000008')
/*产品名称(product)表数据：*/
insertproduct values ('P0001','16M DRAM')
insertproduct values ('P0002','14寸显示器')
insertproduct values ('P0003','1.2GB硬盘')
insertproduct values ('P0004','3.5寸软驱')
insertproduct values ('P0005','键盘')
insertproduct values ('P0006','VGA显示卡')
insertproduct values ('P0007','网卡')
insertproduct values ('P0008','Pentium100CPU')
insertproduct values ('P0009','激光打印机')
insertproduct values ('P0010','8倍速光驱')
insertproduct values ('P0011','计算机字典')
insertproduct values('P0012','9600bits-s调制解调器')
insertproduct values('P0013','Pentium主板')
/*销售明细表（sale_item）数据:*/
insertsale_item values (10001,'P0001',5 , 2500.00,'1996-10-22')
insertsale_item values (10001,'P0002',3 , 6500.00,'1996-10-22')
insertsale_item values (10001,'P0003',2 , 5300.00,'1996-10-22')
insertsale_item values (10001,'P0004',2 , 1600.00,'1996-10-22')
insertsale_item values (10002,'P0001',3 , 2600.00,'1996-11-10')
insertsale_item values (10002,'P0003',1 , 5300.00,'1996-11-10')
insertsale_item values (10002,'P0008',2 , 4800.00,'1996-11-10')
insertsale_item values (10003,'P0001',4 , 2700.00,'1996-10-15')
insertsale_item values (10003,'P0004',2 , 1580.00,'1996-10-15')
2．在销售主表sales中添加字段“发票号码” invoice_no，char(10)，NOT NULL。
alter table sales add invoice_no char(10) NOT NULL;
3．添加外键约束：
a) 在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为FK_sale_id。
alter table sales add foreign key (sale_id) references employee(emp_no);
b) 在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为FK_cust_id。
alter table sales add foreign key (cust_id) references customer(cust_id);
c) 在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照字段为销售主表sales中的字段订单编号order_no，约束名为FK_order_no。
alter table sale_item add constraint FK_order_no foreign key(order_no) references sales(order_no)
d) 在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照字段为产品名称表product中的产品编号字段prod_id，约束名为FK_prod_id。
alter table sale_item add constraint FK_order_no foreign key(prod_id) references product(prod_id)
4．添加核查约束：
a) 将员工表employee中的薪水字段salary的值限定在1000至10000间，约束名为CK_salary。
alter table employee add constraint CK_salary check(salary between 1000 and 10000);
b) 将员工表employee中的员工编号字段emp_no设定为以“E”字母开头, 后面跟4位数的编号，约束名为CK_emp_no。
alter table employee add constraint CK_emp_no check(emp_nolike'E____')
c) 将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为CK_sex。
alter table employee add constraint CK_sex check(sexin('男','女'))
d) 将销售主表sales中的发票号码字段invoice_no设定为以“I”字母开头，后面跟9位数的编号，约束名为CK_inno。
alter table sales add constraintCK_innocheck(invoice_nolike'I_________')
5．为销售主表sales中的字段发票号码invoice_no设置为唯一约束，约束名为UN_inno。
alter table sales add constraint UN_inno unique(invoice_no)