SQL存储过程实例练习和答案

1、2)3)4)称、图书编号、图书名称、借岀日期；参考查询结果如下图所示:查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示:查询借过作者为“安意如”的图书的学生姓名、图书名称、借岀日期、归还日期；参考查询结果如下图所示:查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示:附加：建表语句：USEmasterGO1、学校图书馆借书信息管理系统建立三个表:学生信息表：stude nt字段名称数据类型说明stuIDchar(10)学生编号，主键stuNameVarchar(10)学生名称majorVarchar(50)专业图书表：book字段名称数据类型说明BIDc

2、har(10)图书编号，主键titlechar(50)书名authorchar(20)作者借书信息表：borrow字段名称数据类型说明borrowIDchar(10)借书编号，主键stuIDchar(10)学生编号，外键BIDchar(10)图书编号，外键T_timedatetime借书日期B_timedatetime还书日期请编写SQL语句完成以下的功能:1)查询“计算机”专业学生在“ 2007-12-15 ”至“ 2008-1-8 ”时间段内借书的学生编号、学生名/ $ 建丿库 $ /-检验数据库是否存在，如果为真，删除此数据库-IFexists(SELECT*FROMsysdatabas

3、esWHERE name='BOOK') DROP DATABASEBOOKGOCREATEDATABASEBOOKGO-建数据表- USEBOOKGOCREATETABLEstude nt-学生信息表(stuIDCHAR(10) pr imarykey,-学生编号 stuNameCHAR(10)NOTNULL,-学生名称 majorCHAR(50)NOTNULL-专业)GOCREATETABLEbook-图书表(BIDCHAR(10) pr imarykey,-图书编号 ti tleCHAR(50)NOTNULL,-书名 authorCHAR(20)NOTNULL,-作者 )

4、GOCREATETABLEborrow-借书表(borrowIDCHAR(10) primarykey,-借书编号 stuIDCHAR(10)foreig nkey(stulD)refere ncesstude nt(stulD),-学生编号BIDCHAR(10)foreig nkey(BID)refere ncesbook(BID),-图书编号T_timedatetimeNOTNULL,-借岀日期B_timedatetime-归还日期)GO-学生信息表中插入数据-INSERTINTOstude nt(stulD,stuName,major)VALUES('1001','

5、;INSERTINTOstude nt(stulD,stuName,major)VALUES('1002','INSERTINTOstude nt(stulD,stuName,major)VALUES('1003','INSERTINTOstude nt(stulD,stuName,major)VALUES('1004','INSERTINTOstude nt(stulD,stuName,major)VALUES('1005','-图书信息表中插入数据-INSERTINTObook(BID,titl

6、e,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','林林','计算机') 白杨

7、9;,'计算机') 虎子','英语')北漂的雪,'工商管理') 五月','数学')人生若只如初见 入学那天遇见你 感谢折磨你的人','安意如')','晴空')','如娜')我不是教你诈','刘庸') 英语四级,'白雪')-借书信息表中插入数据-INSERTINT0borrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T001','1001&

8、#39;,'B001','2007-12-26', null)INSERTINT0borrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5', null)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T003','1005','B001','2007-10-8','

9、;2007-12-25')INSERTINT0borrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2008-1-7')INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T005','1002','B004','2007-12-22', null)INSERTINTObo

10、rrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6', null)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11', null)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(

11、'T008','1005','B004','2007-12-10', null)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18')INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T010','1002

12、9;,'B002','2007-9-15','2008-1- 5')INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28', null)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES('T012','1002','B003','2007-1

13、2-30', null) 标准答案：-1）查询“计算机”专业学生在“2007-12-15 ”至“ 2008-1-8 ”时间段内借书的学生编号、学生名称、图书编号、图书名称、借岀日期一select 学生编号=stulD,学生名称=(selectstuNamefromstudentwherestulD=图书编号=BID,图书名称=(selecttitlefrombookwhereBID=,借岀日期=T_timefromborrowwherestuIDi n(selectstulDfromstude ntwheremajor='计算机')an dT_time>'

14、2007-12-15'a ndT_timev'2008-1-8'-2)查询所有借过图书的学生编号、学生名称、专业-select学生编号=stulD,学生名称=stuName,专业=majorfromstude ntwherestulDi n(selectstulDfromborrow)-3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借岀日期-select 学生名称=(selectstuNamefromstude ntwherestulD=,图书名称=(selecttitlefrombookwhereBID=, 借岀日期=T_time，归还日期=B_timefr

15、omborrowwhereBID in( selectBIDfrombookwhereauthor='安意如')-4)查询目前借书但未归还图书的学生名称及未还图书数量-select 学生名称=(selectstuNamefromstudentwherestulD=,借书数量=cou nt(*)fromborrowwhereB timeis nullgrou pbystulD题目2程序员工资表：P roWage字段名称数据类型说明IDint自动编号，主键PN ameChar(10)程序员姓名Wageint工资创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，

16、如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱?例如：如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程测试。请编写T-SQL来实现如下功能:1)创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000 或 6000 元之上,如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500， 4000，5000 或 6000 元。2)创建存储过程，查询程序员平均

17、工资在4500元，如果不到则每个程序员每次加200元，至到所有程序员平均工资达到 4500元。建表语句USEmasterGO/ $ 建丿库 $ /-检验数据库是否存在，如果为真，删除此数据库-IFexists(SELECT*FROMsysdatabasesWHERE name='Wage')DROP DATABASEWageGOCREATEDATABASEWageGO-建数据表-USEWageGOCREATETABLE ProWage-程序员工资表(IDin tide ntity(1,1) primarykey,-工资编号 PN ameCHAR(10)NOTNULL,-程序员姓

18、名 Wagei ntNOTNULL-工资)GO-插入数据-来棱',1900)张三',1200)李四',1800) 二月',3500)蓝天',2780)INSERTINT OP roWage( PName,Wage)VALUES(' INSERTINT OP roWage( PName,Wage)VALUES(' INSERTINT OP roWage( PName,Wage)VALUES(' INSERTINT OP roWage( PName,Wage)VALUES(' INSERTINT OP roWage( PNam

19、e,Wage)VALUES(' 标准答案：-1、创建存储过程-ifexists(select*fromsysobjectswhere name-Sum_wage') droppr ocedureSum_wageGO create procedureSum_wageP Wagei nt,AWagei nt,totali nt as while(1=1)beginif(selectcou nt(*)fr omP roWage)>2*(selectcou nt(*)fro mP roWagewhereWage>=P Wage) up date ProWagesettota

20、l=total+AWage,Wage=Wage+AWage else break endprint' 共加薪：'+convert(varchar,total)+'元'pri nt'加薪后的程序员工资列表：select*fro mP roWage-调用存储过程1-execSum_wage P Wage=2000,AWage=100,total=0 execSum_wage P Wage=2200,AWage=100,total=0 execSum_wage P Wage=3000,AWage=100,total=0 execSum_wage P Wage=

21、4000,AWage=100,total=0 execSum_wage P Wage=5000,AWage=100,total=0 execSum_wage P Wage=6000,AWage=100,total=0 -2、创建存储过程 2- ifexists(select*fromsysobjectswhere name='Avg_wage') droppr ocedureAvg_wageGO create procedureAvg_wageP Wagei nt,AWagei nt, totali nt as while(1=1)beginif(selectAvg(Wage)

22、fr omP roWage)<=P Wage)up date ProWagesettotal=total+AWage,Wage=Wage+AWage else break end print' 共加薪：'+convert(varchar,total)+'元'pri nt'加薪后的程序员工资列表：select*fro mP roWage-调用存储过程-execAvg_wage P Wage=3000,AWage=200,total=0execAvg_wage P Wage=4500,AWage=200,total=0题目3:学生成绩信息三个表，结构如

23、下:学生表：Member字段名称数据类型说明MIDChar(10)学生号，主键MNameChar(50)姓名课程表:字段名称数据类型说明FIDChar(10)课程，主键FNameChar(50)课程名成绩表：Score字段名称数据类型说明SIDint自动编号，主键，成绩记录号FIDChar(10)课程号，外键MIDChar(10)学生号，外键Scoreint成绩请编写T-SQL语句来实现如下功能:1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表:姓名语文数学英语历史张萨78678976王强89678496李三70879256李四807897662) 查询四门课中成绩低于 70分的学生

24、及相对应课程名和成绩。3)统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。4)创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。建表语句：USEmasterGO-检验数据库是否存在，如果为真，删除此数据库-IFexists(SELECT*FROMsysdatabasesWHERE name='Stude nt')DROP DATABASEStude ntGOCREATEDATABASEStude ntGO-建数据表-USEStude ntGOCREATETABLEMember-学生表(MIDchar(10) pr imary

25、key,-学生号MNameCHAR(50)NOTNULL-姓名)GOCREATETABLEF-课程表(FIDchar(10)primarykey,-课程号FNameCHAR(50)NOTNULL-课程名 )GOCREATETABLEscore-学生成绩表(SIDi ntide ntity(1,1) primarykey,-成绩记录号FIDchar(10)foreig nkey(FID)refere ncesF(FID),-课程号 MIDchar(10)foreig nkey(MID)refere ncesMember(MID),- Scorei ntNOTNULL-成绩学生号)GO-课程表中插

26、入数据INSERTINTOF(FID,FName)VALUESCF001','INSERTINTOF(FID,FName)VALUESCF002','INSERTINTOF(FID,FName)VALUESCF003','INSERTINTOF(FID,FName)VALUESCF004','-学生表中插入数据-语文') 数学') 英语') 历史')INSERTINTOMember(MID,MName)VALUES('M001','INSERTINTOMember(MID,M

27、Name)VALUES('M002','INSERTINTOMember(MID,MName)VALUES('M003','张萨') 王强') 李三')李四') 阳阳') 虎子') 夏雪') 璐璐') 珊珊') 香奈儿')INSERTINTOMember(MID,MName)VALUES('M004','INSERTINTOMember(MID,MName)VALUES('M005','INSERTINTOMember(

28、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(

29、'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','

30、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)INSERTI

31、NTOScore(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,Sco

32、re)VALUES('F002','M004',78)INSERTINTOScore(FID,MID,Score)VALUES('F003','M004',97)INSERTINTOScore(FID,MID,Score)VALUES('F004','M004',66)INSERTINTOScore(FID,MID,Score)VALUES('F001','M006',88)INSERTINTOScore(FID,MID,Score)VALUES('F002','M006',55)INSERTINTOScore(FID,MID,Score)VALUES('F003','M006',86)INSERTINTOScore(FID,MID,Sc