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