1 create database bookshop
2 go
3
4 use bookshop
5 go
6
7 /*员工人事表*/
8 CREATE TABLE employee(
9 emp_no char(5)not null primary key,
10 emp_name char(10) not null,
11 sex char(2) not null,
12 dept char(4) not null,
13 title char(6) not null,
14 date_hired datetime not null,
15 birthday datetime null,
16 salary int not null,
17 telephone varchar(20) null,
18 addr char(50) null,
19 )
20 /*客户表*/
21 create table customer(
22 cust_name varchar(20) not null primary key,
23 receiver varchar(20) ,
24 tel_no char(20),
25 cust_addr varchar(100)
26 )
27
28 /*产品名称表*/
29 create table books(
30 book_no char(6) not null primary key,
31 book_name varchar(50) not null,
32 price numeric(7,1) not null,
33 book_type varchar(20),
34 ISBN varchar(15)
35 )
36
37 /*销售主表*/
38 create table sales(
39 order_no char(6) not null primary key,
40 cust_name varchar(20) null,
41 total_amt numeric(9,2) null,
42 order_date datetime null,
43 sale_id char(5) null,
44 foreign key (cust_name) references customer(cust_name),
45 foreign key (sale_id) references employee(emp_no)
46 )
47 /*销货明细表*/
48 create table sale_item(
49 order_no char(6) not null,
50 book_no char(6) not null,
51 qty int not null,
52 unit_price numeric(7,1) null,
53 constraint pk_sale_item primary key
54 (order_no,book_no ),
55 foreign key (order_no) references sales(order_no),
56 foreign key (book_no) references books(book_no)
57 )
58
59 /*人事表（employee）数据：*/
60 insert employee values(‘E0001‘,‘王大华‘,‘男‘,‘业务‘,‘经理‘,‘1976-10-13‘,‘1951-08-01‘,80000,‘13107912134‘,‘上海市‘)
61 insert employee values(‘E0003‘,‘陈自强‘,‘男‘,‘会计‘,‘科长‘,‘1986-09-15‘,‘1963-06-09‘,48000,‘13307913451‘,‘南京市‘)
62 insert employee values(‘E0014‘,‘周小梅‘,‘女‘,‘业务‘,‘职员‘,‘1996-03-01‘,‘1970-03-28‘,32000,‘13579607879‘,‘上海市‘)
63 insert employee values(‘E0009‘,‘陈建国‘,‘男‘,‘管理‘,‘科长‘,‘1987-04-15‘,‘1967-09-01‘,45000,‘13879787765‘,‘天津市‘)
64 insert employee values(‘E0017‘,‘林光华‘,‘男‘,‘业务‘,‘职员‘,‘1995-10-13‘,‘1973-08-17‘,30000,‘13979765654‘,‘上海市‘)
65 insert employee values(‘E0006‘,‘李珠珠‘,‘女‘,‘管理‘,‘经理‘,‘1988-01-01‘,‘1961-07-12‘,60000,‘13679787667‘,‘北京市‘)
66 insert employee values(‘E0002‘,‘李庄敬‘,‘男‘,‘人事‘,‘科长‘,‘1980-09-15‘,‘1958-05-13‘,80000,‘13979962335‘,‘广州市‘)
67 insert employee values(‘E0010‘,‘王成功‘,‘男‘,‘信息‘,‘职员‘,‘1993-02-15‘,‘1969-04-15‘,45000,‘13723456789‘,‘北京市‘)
68 insert employee values(‘E0013‘,‘陈中华‘,‘男‘,‘业务‘,‘职员‘,‘1993-02-15‘,‘1966-07-01‘,43000,‘13978790987‘,‘天津市‘)
69 insert employee values(‘E0008‘,‘刘 刚‘,‘男‘,‘业务‘,‘职员‘,‘1994-11-01‘,‘1968-08-01‘,40000,‘13767654543‘,‘上海市‘)
70 insert employee values(‘E0005‘,‘李珊珊‘,‘女‘,‘会计‘,‘职员‘,‘1990-03-20‘,‘1967-04-25‘,38000,‘13890987876‘,‘上海市‘)
71 insert employee values(‘E0011‘,‘李小蓉‘,‘女‘,‘人事‘,‘职员‘,‘1994-11-01‘,‘1970-11-18‘,30000,‘13345432321‘,‘重庆市‘)
72 insert employee values(‘E0012‘,‘蔡文钦‘,‘男‘,‘制造‘,‘厂长‘,‘1984-08-15‘,‘1960-07-21‘,50000,‘13789876766‘,‘上海市‘)
73 insert employee values(‘E0015‘,‘张大山‘,‘男‘,‘制造‘,‘职员‘,‘1993-12-15‘,‘1968-09-23‘,35000,‘13567657889‘,‘上海市‘)
74 insert employee values(‘E0007‘,‘吴铁雄‘,‘男‘,‘信息‘,‘科长‘,‘1989-10-01‘,‘1965-04-18‘,50000,‘13389876765‘,‘武汉市‘)
75 insert employee values(‘E0016‘,‘方美美‘,‘女‘,‘业务‘,‘职员‘,‘1992-05-20‘,‘1966-06-23‘,40000,‘13167688877‘,‘上海市‘)
76 insert employee values(‘E0004‘,‘刘中兴‘,‘男‘,‘制造‘,‘经理‘,‘1984-05-01‘,‘1960-05-23‘,60000,‘13541586766‘,‘上海市‘)
77 insert employee values(‘E0019‘,‘王仁华‘,‘男‘,‘信息‘,‘经理‘,‘1985-09-15‘,‘1959-03-24‘,60000,‘13122334545‘,‘上海市‘)
78 insert employee values(‘E0020‘,‘陈火旺‘,‘男‘,‘业务‘,‘职员‘,‘1992-08-01‘,‘1964-05-12‘,40000,‘13978796622‘,‘天津市‘)
79 insert employee values(‘E0018‘,‘林中正‘,‘男‘,‘管理‘,‘总经理‘,‘1974-10-01‘,‘1953-05-04‘,100000,‘13879765667‘,‘上海市‘)
80
81
82
83 /* 客户（customer）表数据：*/
84 insert into customer values(‘syiyang‘,‘帅宜阳‘,‘0791-88120321‘,‘江西南昌‘)
85 insert into customer values(‘zbowen‘,‘钟博文‘,‘13564796754‘,‘广东广州‘)
86 insert into customer values(‘zhaoyi‘,‘赵毅‘,‘0791-88507321‘,‘湖南长沙‘)
87 insert into customer values(‘zxuda‘,‘张旭达‘,‘13674018787‘,‘广东广州‘)
88 insert into customer values(‘zhangtao‘,‘张淘‘,‘13807917103‘,‘江西南昌‘)
89 insert into customer values(‘zhangyu‘,‘张毓‘,‘13807910876‘,‘江西南昌‘)
90 insert into customer values(‘wming‘,‘王敏‘,null,‘江西南昌‘)
91
92 /*产品名称表*/
93 insert into books values(‘20652‘,‘数据库系统概论‘,39,‘计算机‘,‘978704040661‘)
94 insert into books values(‘22082‘,‘java编程思想‘,108,‘计算机‘,‘978704040324‘)
95 insert into books values(‘22323‘,‘SQLServer入门‘,59.8,‘计算机‘,‘978704040121‘)
96 insert into books values(‘95745‘,‘数据库基础教程‘,38,‘计算机‘,‘978704040001‘)
97 insert into books values(‘95762‘,‘傲慢与偏见‘,33.8,‘文学‘,‘9787532750849‘)
98 insert into books values(‘43748‘,‘哈利波特‘,225,‘文学‘,‘23580693‘)
99 insert into books values(‘43760‘,‘神奇校车‘,132,‘绘本‘,‘21005473‘)
100 insert into books values(‘83431‘,‘数据库_原理‘,28,‘计算机‘,‘978732424321‘)
101 insert into books values(‘83422‘,‘数据库及原理‘,24,‘计算机‘,‘978732424002‘)
102 insert into books values(‘541122‘,‘DBA mananging‘,24,‘计算机‘,‘978710424213‘)
103 /*销售主表*/
104
105 insert into sales values(‘102893‘,‘zbowen‘,300,‘2015-3-16‘,‘E0017‘)
106 insert into sales values(‘102894‘,‘zhaoyi‘,244,‘2015-5-2‘,‘E0016‘)
107 insert into sales values(‘102895‘,‘zhaoyi‘,59,‘2015-8-29‘,‘E0008‘)
108 insert into sales values(‘102896‘,‘zxuda‘,19,‘2015-8-29‘,‘E0017‘)
109 insert into sales values(‘102897‘,‘zxuda‘,19,‘2015-9-15‘,‘E0005‘)
110 insert into sales values(‘102898‘,‘syiyang‘,39,‘2015-8-9‘,‘E0016‘)
--1、利用存储过程，给employee表添加一条业务部门员工的信息。
create proc ins_emp @emp_no char(5),@emp_name char(10),@sex char(2),@dept char(4),@title char(6),@date_hired datetime,@birthday datetime,@salary int,@telephone varchar(20),@addr char(50)
as
insert into employee values(@emp_no ,@emp_name ,@sex ,@dept ,@title ,@date_hired ,@birthday ,@salary ,@telephone ,@addr )
exec ins_emp ‘E0021‘,‘Jack‘,‘男‘,‘业务‘,‘职员‘,‘1990-12-12‘,‘1970-12-11‘,32000,123123445,‘上海市‘
--2、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
create proc join_oper
as
select emp_name,b.cust_name,total_amt
from employee a,sales b,customer c
where a.emp_no=b.sale_id and b.cust_name=c.cust_name
exec join_oper
--3、创建带一个输入参数的存储过程，实现按员工姓名进行模糊查找，查找员工编号、订单编号、销售金额。
create proc find_name @emp_name varchar(10)
as
select sale_id,order_no,total_amt
from employee a,sales b
where a.emp_no=b.sale_id and emp_name like @emp_name
exec find_name ‘刘%‘
--4、创建带两个输入参数的存储过程，查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
create proc find_name_title @emp_name varchar(10) ,@title varchar(6)
as
select sale_id,order_no,total_amt
from employee a,sales b
where a.emp_no=b.sale_id and emp_name like @emp_name and title like @title
exec find_name_title ‘李%‘,‘职员‘
--5、利用存储过程计算出订单编号为102898的订单的销售金额。（带一输入参数和一输出参数）（提示：sales表中的total_amt应该等于sale_item表中的同一张订单的不同销售产品的qty*unit_price之和）
create proc total_amt @order_no int,@sum_amt int output
as
select @sum_amt=SUM(qty*unit_price)
from sale_item
where order_no=@order_no
declare @tot int
exec total_amt ‘102898‘,@tot output
select @tot
--6、创建一存储过程，根据给出的职称，返回该职称的所有员工的平均工资。（带一输入参数和返回值）
create proc avg_salary @title char(6)
as
declare @avg float
select @avg=AVG(salary)
from employee
where title=@title
return @avg
declare @avg float
exec @avg=avg_salary ‘职员‘
select @avg
--7、请创建一个存储过程，修改sales表中的订单金额total_amt，使之等于各订单对应的所有订单明细的数量与单价的总和。
create proc update_sales_totamt
as
update sales
set total_amt=(select sum(qty*unit_price)
from sale_item
where sales.order_no=sale_item.order_no)
exec update_sales_totamt
select * from sales
select * from sale_item