SQL存储过程实例练习.docx
《SQL存储过程实例练习.docx》由会员分享，可在线阅读，更多相关《SQL存储过程实例练习.docx（16页珍藏版）》请在冰豆网上搜索。
SQL存储过程实例练习
Sql存储过程实例练习
题目1
1、学校图书馆借书信息管理系统建立三个表:
学生信息表：
student
字段名称
数据类型
说明
stuID
char（10）
学生编号，主键
stuName
Varchar（10）
学生名称
major
Varchar（50）
专业
图书表：
book
字段名称
数据类型
说明
BID
char（10）
图书编号，主键
title
char（50）
书名
author
char（20）
作者
借书信息表：
borrow
字段名称
数据类型
说明
borrowID
char（10）
借书编号，主键
stuID
char（10）
学生编号，外键
BID
char（10）
图书编号，外键
T_time
datetime
借书日期
B_time
datetime
还书日期
请编写SQL语句完成以下的功能：
1）查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、
学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示:
学生编号
学生名称
图书编号
图书名称
借出日期
|11001
林林
B001
人生若只如初见
2007122600:
00:
00000
F1002
白畅
B0D4
我不是教你洋
2007-12-22OttOftOQOOO
1002
白杨
B003
感谢折磨你的人
2007-12-30OttOttOOOOO
2）查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示:
学生编号
学生名称]
专业]
:
1001
林林
计算机
1002
白杨
计篁机
1004
北漂的雪
工商告理
1005
五月
数学
3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期;
参考查询结果如下图所示：
学生名称
图书名称
惜出日期
国日期J
:
林林五月
人生若只如初见
2CU712-2600:
00:
00,000
NULL
人生若只如初见
2007-1008000000000
2007-12-2500:
00:
00.000
白杨
人生若只如初见
2007-09-11000000000
NULL
4）查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所
示：
附加：
建表语句
USEmaster
GO
/$$$$$$$$$$$$$,xFF/—p$$$$$$$$$$$$$$$$$$$$$$$$/
--检验数据库是否存在，如果为真，删除此数据库-
IFexists（SELECT*FROMsysdatabasesWHEREname='BOOK'）DROPDATABASEBOOK
GO
CREATEDATABASEBOOK
GO
--建数据表--USEBOOK
GO
CREATETABLEstudent-*生信息表（
stuIDCHAR（10）primarykey,一学生编号
stuNameCHAR（10）NOTNULL,-*生名称
majorCHAR（50）NOTNULL—专业）
GO
CREATETABLEbook--图书表
（
BIDCHAR（10）primarykey,―图书编号
titleCHAR（50）NOTNULL,--书名
authorCHAR（20）NOTNULL,--作者
）
GO
CREATETABLEborrow--借书表
（
borrowIDCHAR（10）primarykey,一借书编号
stuIDCHAR（10）foreignkey（stuID）referencesstudent（stuID）,--学生编号
BIDCHAR（10）foreignkey（BID）referencesbook（BID）,--图书编号
T_timedatetimeNOTNULL,--借出日期
B_timedatetime一归还日期
）
GO
--学生信息表中插入数据--
INSERTINTOstudent（stuID,stuName,major）VALUES（'1001',林林','计算机’）
INSERTINTOstudent（stuID,stuName,major）VALUES（'1002',白杨','计算机'）
INSERTINTOstudent（stuID,stuName,major）VALUES（'1003',虎子','英语'）
INSERTINTOstudent（stuID,stuName,major）VALUES（'1004',北漂的雪’,‘工商管理’）
INSERTINTOstudent（stuID,stuName,major）VALUES（'1005',五月','数学'）
-图书信息表中插入数据--
INSERTINTObook（BID,title,author）VALUES（'B001'人生若只如初见’,'安意如'）
INSERTINTObook（BID,title,author）VALUES（'B002'入学那天遇见你’,‘晴空'）
INSERTINTObook（BID,title,author）VALUES（'B003',感谢折磨你的人','如娜'）
INSERTINTObook（BID,title,author）VALUES（'B004',我不是教你诈','刘庸'）
INSERTINTObook（BID,title,author）VALUES（'B005供语四级','白雪'）
--借书信息表中插入数据--
INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T001','1001','B001','2007-12-26',null）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T002','1004','B003','2008-1-5',null）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T003','1005','B001','2007-10-8','2007-12-25'）
INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T004','1005','B002','2007-12-16','2008-1-7'）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T005','1002','B004','2007-12-22',null）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T006','1005','B005','2008-1-6',null）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T007','1002','B001','2007-9-11',null）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T008','1005','B004','2007-12-10',null）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T009','1004','B005','2007-10-16','2007-12-18'）
INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T010','1002','B002','2007-9-15','2008-1-5'）INSERTINTO
borrow（borrowID,stuID,BID,T_time,B_time）VALUES（'T011','1004','B003','2007-12-28',null）INSERTINTO
borrow（borrowID,stuID,BID,Ttime,Btime）VALUES（'T012','1002','B003','2007-12-30',null）
标准答案：
--1）查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学
生名称、图书编号、图书名称、借出日期一
select学生编号=stuID,学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,图书编号=BID,图书名称=（selecttitlefrombookwhereBID=borrow.BID）,借出日期=T_timefromborrowwherestuIDin（selectstuIDfromstudentwheremajor='计算机'）and
T_time>'2007-12-15'andT_time<'2008-1-8'
--2）查询所有借过图书的学生编号、学生名称、专业-
select学生编号=stuID,学生名称=stuName,专业=majorfromstudentwherestuIDin（selectstuIDfromborrow）
--3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期-
select学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,图书名称=（selecttitlefrombookwhereBID=borrow.BID）,借出日期=T_time,归还日期=B_timefromborrowwhereBIDin（selectBIDfrombookwhereauthor='安意如'）
--4）查询目前借书但未归还图书的学生名称及未还图书数量-
select学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,借书数量=count（*）fromborrowwhereB_timeisnullgroupbystuID
题目2
程序员工资表：
ProWage
字段名称
数据类型
说明
ID
int
自动编号，主键
PName
Char（10）
程序员姓名
Wage
int
工资
创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五
十的人薪水不到2000元，给所有人加薪，每次加100,再进行分析，直到有一半以上的人
大于2000元为止，存储过程执行完后，最终加了多少钱？
例如：
如果有百分之五十的人薪水不到2000,给所有人加薪，每次加100元，直到有一半
以上的人工资大于2000元，调用存储过程后的结果如图：
一共加薪;5g元
加薪后的程序员工资列表;
ID
Wage
1
春鸟
2000
Z
张三
1300
3
李四
1900
4
二月
3600
5
蓝天
2880
请编写T-SQL来实现如下功能：
1）创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000
元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200,
3000,3500,4000,5000或6000元。
2）创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元，
至到所有程序员平均工资达到4500元。
建表语句
USEmaster
GO
/$$$$$$$$$$$$$,xFF/—p$$$$$$$$$$$$$$$$$$$$$$$$/
--检验数据库是否存在，如果为真，删除此数据库-
IFexists（SELECT*FROMsysdatabasesWHEREname='Wage'）
DROPDATABASEWage
GO
CREATEDATABASEWage
GO
--建数据表--
USEWage
GO
CREATETABLEProWage--程序员工资表
（
IDintidentity（1,1）primarykey,一工资编号
PNameCHAR（10）NOTNULL,—程序员姓名
WageintNOTNULL—工资
）
GO
--插入数据--
INSERTINTOProWage（PName,Wage）VALUES（鸟',1900）
INSERTINTOProWage（PName,Wage）VALUES（'三',1200）
INSERTINTOProWage（PName,Wage）VALUES（'四',1800）
INSERTINTOProWage（PName,Wage）VALUES（'月',3500）
INSERTINTOProWage（PName,Wage）VALUES（天',2780）
标准答案：
--1、创建存储过程--
ifexists（select*fromsysobjectswherename='Sum_wage'）
dropprocedureSum_wage
GO
createprocedureSumwage
@PWageint,
@AWageint,@totalintas
while（1=1）
begin
if（selectcount（*）fromProWage）>2*（selectcount（*）fromProWagewhereWage>=@PWage）updateProWageset@total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：
'+convert（varchar,@total）+'元'
print'加薪后的程序员工资列表：
’
select*fromProWage
--调用存储过程1--
execSum_wage@PWage=2000,@AWage=100,@total=0
execSum_wage@PWage=2200,@AWage=100,@total=0
execSum_wage@PWage=3000,@AWage=100,@total=0
execSum_wage@PWage=4000,@AWage=100,@total=0
execSum_wage@PWage=5000,@AWage=100,@total=0
execSum_wage@PWage=6000,@AWage=100,@total=0
--2、创建存储过程2--
ifexists（select*fromsysobjectswherename='Avg_wage'）dropprocedureAvg_wage
GO
createprocedureAvg_wage
@PWageint,
@AWageint,@totalintas
while（1=1）
begin
if（（selectAvg（Wage）fromProWage）<=@PWage）
updateProWageset@total=@total+@AWage,Wage=Wage+@AWageelse
breakendprint'一共加薪：
'+convert（varchar,@total）+'元'print'加薪后的程序员工资列表：
’
select*fromProWage
--调用存储过程--
execAvg_wage@PWage=3000,@AWage=200,@total=0
execAvgwage@PWage=4500,@AWage=200,@total=0
题目3:
学生成绩信息三个表，结构如下:
学生表：
Member
字段名称
数据类型
说明
MID
Char（10）
学生号，主键
MName
Char（50）
姓名
课程表:
字段名称
数据类型
说明
FID
Char（10）
课程，主键
FName
Char（50）
课程名
成绩表：
Score
字段名称
数据类型
说明
SID
int
自动编号，主键，成绩记录号
FID
Char（10）
课程号，外键
MID
Char（10）
学生号，外键
Score
int
成绩
请编写T-SQL语句来实现如下功能：
1）查询各个学生语文、数学、英语、历史课程成绩，例如下表:
姓名
数学
英语
历史
张萨
78
67
89
76
王强
89
67
84
96
李三
70
87
92
56
李四
80
78
97
66
2）查询四门课中成绩低于70分的学生及相对应课程名和成绩。
3）统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
4）创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显
示姓名、学号。
建表语句：
USEmaster
GO
/$$$$$$$$$$$$$,xFF/—p$$$$$$$$$$$$$$$$$$$$$$$$/
--检验数据库是否存在，如果为真，删除此数据库-
IFexists（SELECT*FROMsysdatabasesWHEREname='Student'）DROPDATABASEStudent
GO
CREATEDATABASEStudent
GO
--建数据表--
USEStudent
GO
CREATETABLEMember--学生表
（
MIDchar（10）primarykey,--学生号
MNameCHAR（50）NOTNULL—姓名
）
GO
CREATETABLEF--课程表
（
FIDchar（10）primarykey,一课程号
FNameCHAR（50）NOTNULL课程名
）
GO
CREATETABLEscore-浮生成绩表
（
SIDintidentity（1,1）primarykey,--成绩记录号
FIDchar（10）foreignkey（FID）referencesF（FID）,--课程号
MIDchar（10）foreignkey（MID）referencesMember（MID）,--学生号
ScoreintNOTNULL―成绩
）
GO
--课程表中才f入数据--
INSERTINTOF（FID,FName）VALUES（'F00话文'）
INSERTINTOF（FID,FName）VALUES（'F00数学'）
INSERTINTOF（FID,FName）VALUES（'F00英语'）
INSERTINTOF（FID,FName）VALUES（'F004i史'）
--学生表中插入数据--
INSERTINTOMember（MID,MName）VALUES（'M001',张萨'）
INSERTINTOMember（MID,MName）VALUES（'M002','王强'）
INSERTINTOMember（MID,MName）VALUES（'M003',李三'）
INSERTINTOMember（MID,MName）VALUES（'M004',李四'）
INSERTINTOMember（MID,MName）VALUES（'M005',阳阳'）
INSERTINTOMember（MID,MName）VALUES（'M006',虎子'）
INSERTINTOMember（MID,MName）VALUES（'M007',夏雪'）
INSERTINTOMember（MID,MName）VALUES（'M008',璐璐'）
INSERTINTOMember（MID,MName）VALUES（'M009',珊珊'）
INSERTINTOMember（MID,MName）VALUES（'M010',香奈儿'）
--成绩表中插入数据--
INSERTINTOScore（FID,MID,Score）VALUES（'F001','M001',78）
INSERTINTOScore（FID,MID,Score）VALUES（'F002','M001',67）
INSERTINTOScore（FID,MID,Score）VALUES（'F003','M001',89）
INSERTINTOScore（FID,MID,Score）VALUES（'F004','M001',76）
INSERTINTOScore（FID,MID,Score）VALUES（'F001','M002',89）
INSERTINTOScore（FID,MID,Score）VALUES（'F002','M002',67）
INSERTINTOScore（FID,MID,Score）VALUES（'F003','M002',84）
INSERTINTOScore（FID,MID,Score）VALUES（'F004','M002',96）
INSERTINTOScore（FID,MID,Score）VALUES（'F001','M003',70）
INSERTINTOScore（FID,MID,Score）VALUES（'F002','M003',87）
INSERTINTOScore（FID,MID,Score）VALUES（'F003','M003',92）
INSERTINTOScore（FID,MID,Score）VALUES（'F004','M003',56）
INSERTINTOScore（FID,MID,Score）VALUES（'F001','M004',80）
INSERTINTOScore（FID,MID,Score）VALUES（'F002','M004',78）
INSERTINTOScore（FID,MID,Score）VALUES（'F003','M004',97）
INSERTINTOScore（FID
升级会员 