create database company go
use company
go
if exists (select * from sysobjects where id = object_id(N’[dbo].[customer]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[customer]
GO
if exists (select * from sysobjects where id = object_id(N’[dbo].[employee]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[employee]
GO
if exists (select * from sysobjects where id = object_id(N’[dbo].[product]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[product]
GO
if exists (select * from sysobjects where id = object_id(N’[dbo].[sale_item]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[sale_item]
GO
if exists (select * from sysobjects where id = object_id(N’[dbo].[sales]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[sales]
GO
/员工人事表/
CREATE TABLE employee(
emp_no char(5)not null primary key,
emp_name char(10) not null,
sex char(2) not null,
dept char(4) not null,
title char(6) not null,
date_hired datetime not null,
birthday datetime null,
salary int not null,
telephone varchar(20) null,
addr char(50) null,
)
/客户表/
create table customer(
cust_id char(5) not null primary key,
cust_name char(20) not null,
addr char(40) not null,
tel_no char(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,
constraint pk_sale_item primary key
(order_no,prod_id )
)
/产品名称表/
create table product(
prod_id char(5) not null primary key,
prod_name char(20) not null
)
/人事表（employee）数据：/
insert employee values(‘E0001’,’王大华’,’男’,’业务’,’经理’,’1976-10-13’,’1951-08-01’,80000,’13107912134’,’上海市’)
insert employee values(‘E0003’,’陈自强’,’男’,’会计’,’科长’,’1986-09-15’,’1963-06-09’,48000,’13307913451’,’南京市’)
insert employee values(‘E0014’,’周小梅’,’女’,’业务’,’职员’,’1996-03-01’,’1970-03-28’,32000,’13579607879’,’上海市’)
insert employee values(‘E0009’,’陈建国’,’男’,’管理’,’科长’,’1987-04-15’,’1967-09-01’,45000,’13879787765’,’天津市’)
insert employee values(‘E0017’,’林光华’,’男’,’业务’,’职员’,’1995-10-13’,’1973-08-17’,30000,’13979765654’,’上海市’)
insert employee values(‘E0006’,’李珠珠’,’女’,’管理’,’经理’,’1988-01-01’,’1961-07-12’,60000,’13679787667’,’北京市’)
insert employee values(‘E0002’,’李庄敬’,’男’,’人事’,’科长’,’1980-09-15’,’1958-05-13’,80000,’13979962335’,’广州市’)
insert employee values(‘E0010’,’王成功’,’男’,’信息’,’职员’,’1993-02-15’,’1969-04-15’,45000,’13723456789’,’北京市’)
insert employee values(‘E0013’,’陈中华’,’男’,’业务’,’职员’,’1993-02-15’,’1966-07-01’,43000,’13978790987’,’天津市’)
insert employee values(‘E0008’,’刘 刚’,’男’,’业务’,’职员’,’1994-11-01’,’1968-08-01’,40000,’13767654543’,’上海市’)
insert employee values(‘E0005’,’李珊珊’,’女’,’会计’,’职员’,’1990-03-20’,’1967-04-25’,38000,’13890987876’,’上海市’)
insert employee values(‘E0011’,’李小蓉’,’女’,’人事’,’职员’,’1994-11-01’,’1970-11-18’,30000,’13345432321’,’重庆市’)
insert employee values(‘E0012’,’蔡文钦’,’男’,’制造’,’厂长’,’1984-08-15’,’1960-07-21’,50000,’13789876766’,’上海市’)
insert employee values(‘E0015’,’张大山’,’男’,’制造’,’职员’,’1993-12-15’,’1968-09-23’,35000,’13567657889’,’上海市’)
insert employee values(‘E0007’,’吴铁雄’,’男’,’信息’,’科长’,’1989-10-01’,’1965-04-18’,50000,’13389876765’,’武汉市’)
insert employee values(‘E0016’,’方美美’,’女’,’业务’,’职员’,’1992-05-20’,’1966-06-23’,40000,’13167688877’,’上海市’)
insert employee values(‘E0004’,’刘中兴’,’男’,’制造’,’经理’,’1984-05-01’,’1960-05-23’,60000,’13541586766’,’上海市’)
insert employee values(‘E0019’,’王仁华’,’男’,’信息’,’经理’,’1985-09-15’,’1959-03-24’,60000,’13122334545’,’上海市’)
insert employee values(‘E0020’,’陈火旺’,’男’,’业务’,’职员’,’1992-08-01’,’1964-05-12’,40000,’13978796622’,’天津市’)
insert employee values(‘E0018’,’林中正’,’男’,’管理’,’总经理’,’1974-10-01’,’1953-05-04’,100000,’13879765667’,’上海市’)
/* 客户（customer）表数据：*/
insert customer values(‘C0001’,’ABC股份有限公司’,’上海市’,’13879765667’,’500100’)
insert customer values(‘C0002’,’客户乙’,’天津市’,’13178788980’,’100300’)
insert customer values(‘C0003’,’客户丙’,’北京市’,’13345566789’,’100100’)
insert customer values(‘C0004’,’客户丁’,’上海市’,’13134455566’,’500210’)
insert customer values(‘C0005’,’客户戊’,’北京市’,’13677889890’,’100120’)
insert customer values(‘C0006’,’客户己’,’上海市’,’13767678899’,’500230’)
insert customer values(‘C0007’,’客户庚’,’上海市’,’13522334567’,’500300’)
/销售（sales）主表数据:/
insert sales values (10002,’C0002’,’E0013’,22700.00,’1996-11-10’)
insert sales values(10003,’C0003’,’E0014’,13960.00,’1996-10-15’)
insert sales values(10004,’C0003’,’E0014’,33000.00,’1996-11-18’)
insert sales values (10001,’C0001’,’E0008’,60000.00,’1996-11-10’)
insert sales values (10007,’C0008’,’E0008’,20000.00,’1996-11-22’)
/销售明细表（sale_item）数据:/
insert sale_item values (10001,’P0001’,5 , 2500.00,’1996-11-10’)
insert sale_item values (10001,’P0002’,3 , 6500.00,’1996-11-10’)
insert sale_item values (10001,’P0003’,2 , 5300.00,’1996-11-10’)
insert sale_item values (10002,’P0004’,2 , 1600.00,’1996-11-10’)
insert sale_item values (10002,’P0001’,3 , 2600.00,’1996-11-10’)
insert sale_item values (10002,’P0003’,1 , 5300.00,’1996-11-10’)
insert sale_item values (10003,’P0008’,2 , 4800.00,’1996-10-15’)
insert sale_item values (10003,’P0001’,4 , 2700.00,’1996-10-15’)
insert sale_item values (10004,’P0004’,2 , 1580.00,’1996-11-18’)
insert sale_item values (10004,’P0007’,4 , 2700.00,’1996-11-18’)
insert sale_item values (10007,’P0004’,2 , 1580.00,’1996-11-22’)
/产品名称(product)表数据：/
insert product values (‘P0001’,’16M DRAM’)
insert product values (‘P0002’,’14寸显示器’)
insert product values (‘P0003’,’1.2GB硬盘’)
insert product values (‘P0004’,’3.5寸软驱’)
insert product values (‘P0005’,’键盘’)
insert product values (‘P0006’,’VGA显示卡’)
insert product values (‘P0007’,’网卡’)
insert product values (‘P0008’,’Pentium100CPU’)
insert product values (‘P0009’,’激光打印机’)
insert product values (‘P0010’,’8倍速光驱’)
insert product values (‘P0011’,’计算机字典’)
insert product values(‘P0012’,’9600bits-s调制解调器’)
insert product values(‘P0013’,’Pentium主板’)
1、查询女职员所销售的订单号，订单金额。
select order_no.tot_amt
from sales left join employee
on sale_id=emp_no
2、查询在一张订单中订购了所有产品的订单号。
select order_no
from sale_item product
where prod_id=product.prod_id
and (select count(order_no) from sale_item)=(select count(*) from product)
3、查询每位顾客的总订购金额，并按总订购金额降序排列。
select cust_id,tit_amt
from sales
order by tott_amt desc
4、查询每种产品的订购数量总和，并显示出产品号、产品名及订单数量总和。
select a.prod_id,prod_name,sum(qty)Sum_product,count(order_no),Sum_order
from sale_item a,product b
where a.prod_id=b.prod_id
group by a.prod_id,prod_name
5、查询工资前五位职工的有关信息。
select top(5)*
from employee
order by salary desc
6、查询没有订购“P0004”产品的订单号。
select distinct order_no
from sale_item
where order_no not in(
select order_no
from sale_item
where prod_id=’P0004’
);
7、建一视图，该视图中包含业务部员工的员工号、员工名、订单号、客户名、订单金额。
create view v_employee
as
select emp_no,emp_name,order_no,custname,tot_amt
from employee,sales,customer
where sale_id=order_no and sales.cust_id=customer.cust_id;
8、对员工表添加一核查约束，限定员工性别只能取“男”或“女”。
alter table employee
add constraint C1 check(sex in(‘男’,’女’))
9、建一存储过程，当删除给定编号的员工时，实现级联删除，即既要删除员工表中的记录，又要删除销售主表及销售明细表的记录。
create procedure cascading_deletion
@Number char(5),
@begin char(5)=’false’
as
begin
if (@begin='true')
begin
alter table sales add constraint name1 foreign key (sale_id) references employee(emp_no) ON DELETE CASCADE
alter table sale_item add constraint name2 foreign key (order_no) references sales (order_no) ON DELETE CASCADE
end
delete from employee where emp_no=@Number;end
10、现有数据库中sales销售主表中的订单金额并不正确，它不是某订单中所有订单明细的商品数量*单价的总和。请创建一个存储过程，修改sales表中的订单金额tot_amt，使之等于各订单对应的所有订单明细的数量与单价的总和。
CREATE PROCEDURE S10 AS BEGIN
UPDATE sales
SET tot_amt = ( SELECT SUM ( qty * unit_price ) FROM sale_item WHERE sales.order_no= sale_item.order_no GROUP BY order_no )
END
11、创建一个触发器，当对sale_item表进行插入、删除或修改商品数量qty及商品单价unit_price时，及时更新sales表中的订单金额tot_amt。
CREATE TRIGGER update_totAmt ON sale_item after INSERT,
DELETE,
UPDATE AS BEGIN
UPDATE sales
SET tot_amt = ( SELECT SUM ( unit_price * qty ) FROM sale_item WHERE order_no = sales.order_no )
END
