SQL存储过程实例

1、题目11、学校图书馆借书信息管理系统建立三个表:学生信息表：student字段名称数据类型说明stuIDchar(10)学生编号，主键stuNameVarchar(10)学生名称majorVarchar(50)专业图书表：book字段名称数据类型说明BIDchar(10)图书编号，主键titlechar(50)书名authorchar(20)作者借书信息表：borrow字段名称数据类型说明borrowIDchar(10)借书编号，主键stuIDchar(10)学生编号，外键BIDchar(10)图书编号，外键T_timedatetime借书日期B_timedatetime还书日期请编写 SQL

2、 语句完成以下的功能：1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：学生漏号学生漏号学生名称学生名称图书编号图书编号图书名称图书名称借出日期借出日期:1001林林林林B001人生若只如初见人生若只如初见2007122600:00:000001002白畅白畅S004我不是教你洋我不是教你洋2007-12-22OOtOftOaOOO1002白杨白杨B003感谢折磨你的人感谢折磨你的人2007-12-30ODOttOCOOO2)查询所有借过图书的学生编号、学生名称、专业；参考查询结果如

3、下图所示:学生编号学生编号 学生名称学生名称专业专业111001林林林林计算机计算机1002白杨白杨计算机计算机11004北漂的雪北漂的雪工商省理工商省理1005五月五月数学数学3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期;参考查询结果如下图所示:学生名称学生名称 图书名称图书名称惜出日期惜出日期日期日期n人人生若只如初见生若只如初见200712-2600:00:00,000NULL同同人生若只如初见人生若只如初见2007-100800:00:000002007-12-2500:00:00.000白杨白杨人生若只如初见人生若只如初见2007-09-1100:000

4、0,000NULL4）查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示：附加：建表语句USEmasterGO/$yFF/-p$/- -检验数据库是否存在，如果为真，删除此数据库-IFexists(SELECT*FROMsysdatabasesWHEREname=BOOK)DROPDATABASEBOOKGOCREATEDATABASEBOOKGO- -建数据表-USEBOOKGOCREATETABLEstudent-学生信息表(stuIDCHAR(10)primarykey,-浮生编号stuNameCHAR(10)NOTNULL,-学生名称majorCHAR(50)NO

5、TNULL-专业)GOCREATETABLEbook-图书表(BIDCHAR(10)primarykey,-图书编号titleCHAR(50)NOTNULL,-书名 authorCHAR(20)NOTNULL,-1 乍者)GOCREATETABLEborrow-借书表(borrowIDCHAR(10)primarykey,-借书编号stuIDCHAR(10)foreignkey(stuID)referencesstudent(stuID),-学生编号BIDCHAR(10)foreignkey(BID)referencesbook(BID),-图书编号 T_timedatetimeNOTNULL

6、,-借出日期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,stuN

7、ame,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(B

8、005英语四级,白雪)- -借书信息表中插入数据-INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T001,1001,B001,2007-12-26,null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T002,1004,B003,2008-1-5,null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T003,1005,B001,2007-10-8,2007-12-25)INSERTIN

9、TOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T004,1005,B002,2007-12-16,2008-1-7)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T005,1002,B004,2007-12-22,null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T006,1005,B005,2008-1-6,null)INSERTINTOborrow(borrowID,stuID,BID,T_time

10、,B_time)VALUES(T007,1002,B001,2007-9-11,null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T008,1005,B004,2007-12-10,null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T009,1004,B005,2007-10-16,2007-12-18)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T010,1002,B002,2

11、007-9-15,2008-1-5)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES(T011,1004,B003,2007-12-28,null)INSERTINTOborrow(borrowID,stuID,BID,Ttime,Btime)VALUES(T012,1002,B003,2007-12-30,null)标准答案：-1 应询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期一select 学生编号=stuID,学生名称=(selectstu

12、NamefromstudentwherestuID=borrow.stuID),图书编号=BID,图书名称=(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_timefromborrowwherestuIDin(selectstuIDfromstudentwheremajor=计算机)andT_time2007-12-15andT_time2*(selectcount(*)fromProWagewhereWage=PWage)updateProWagesettotal=total+AWage,Wage=Wage+AWageelsebreakendp

13、rint一共加薪：+convert(varchar,total)+元print加薪后的程序员工资列表：select*fromProWage- -调用存储过程 1-execSum_wagePWage=2000,AWage=100,total=0execSum_wagePWage=2200,AWage=100,total=0execSum_wagePWage=3000,AWage=100,total=0execSum_wagePWage=4000,AWage=100,total=0execSum_wagePWage=5000,AWage=100,total=0execSum_wagePWage=6

14、000,AWage=100,total=0- -2、创建存储过程 2-ifexists(select*fromsysobjectswherename=Avg_wage)dropprocedureAvg_wageGOcreateprocedureAvg_wagePWageint,AWageint,totalintaswhile(1=1)beginif(selectAvg(Wage)fromProWage)=PWage)updateProWagesettotal=total+AWage,Wage=Wage+AWageelsebreakendprint一共加薪：+convert(varchar,to

15、tal)+元print加薪后的程序员工资列表：select*fromProWage- -调用存储过程-execAvg_wagePWage=3000,AWage=200,total=0execAvgwagePWage=4500,AWage=200,total=0题目3:学生成绩信息三个表，结构如下:学生表：Member字段名称数据类型说明MIDChar(10)学生号，主键MNameChar(50)姓名课程表:字段名称数据类型说明FIDChar(10)课程，主键FNameChar(50)课程名成绩表：Score字段名称数据类型说明SIDint自动编号，主键，成绩记录号FIDChar(10)课程号，

16、外键MIDChar(10)学生号，外键Scoreint成绩请编写 T-SQL 语句来实现如下功能：1）查询各个学生语文、数学、英语、历史课程成绩，例如下表:姓名数学英语历史张萨78678976王强89678496李三70879256李四807897662）查询四门课中成绩低于 70 分的学生及相对应课程名和成绩。3）统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。4）创建存储过程，分别查询参加 1、2、3、4 门考试及没有参加考试的学生名单，要求显示姓名、学号。建表语句：USEmasterGO/$yFF/-p$/- -检验数据库是否存在，如果为真，删除此数据库-IFexists(S

17、ELECT*FROMsysdatabasesWHEREname=Student)DROPDATABASEStudentGOCREATEDATABASEStudentGO- -建数据表-USEStudentGOCREATETABLEMember-学生表(MIDchar(10)primarykey,-浮生号MNameCHAR(50)NOTNULL-姓名)GOCREATETABLEF-课程表(FIDchar(10)primarykey,-课程号FNameCHAR(50)NOTNULL 裸程名)GOCREATETABLEscore-4 生成绩表(SIDintidentity(1,1)primaryke

18、y,-成绩记录号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 史,)- -学生表中插入

19、数据-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,夏雪,

20、)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)INSERTIN

21、TOScore(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)INSERTIN

22、TOScore(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)INSERTIN

23、TOScore(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,Score)VALUES(F004,M006,79)INSERTINTOScore(FID,MID,Score)VALUES(F002,M007,77)INSERTINTOScore(FID,MID,Score)VALUES(F003,M008,65)INSERTINTOScore(FID,MID,Score)VALUES(F004,M007,48)INSERTINTOScore(FID,MID,Score)VALUES(F004,M009,75)INSERTINTOScore(FID,MID,Score)VALUES(F002,M009,88)标准答案：- -1 应询各个学生语文、数学、英语、历史课程成绩-SELECTMember.MNameAS 姓名,英语=SUM(CASEF.FNameWHENg 文