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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/228990.html
標籤：SQL Server
上一篇：The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION