SQL存储过程实例

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

2、编写SQL语句完成以下的功能:1）查询“计算机”专业学生在“ 2007-12-15”至“ 2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：2）查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示：3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；参考查 询结果如下图所示：4）查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示:附加：建表语句：USEmasterGO/ $建丿库 $ /-检验数据库是否存在，如果为真，删除此数据库 -IFexists(SELECT*FROMsys

3、databasesWHERE name=BOOK)DROPDATABASEBOOKGOCREATEDATABASEBOOKGO-建数据表-USEBOOKGOCREATETABLEstudent-学生信息表(stulDCHAR(10)primarykey,-学生编号stuNameCHAR(1O)NOTNULL,-学生名称majorCHAR(50)NOTNULL-专业)GOCREATETABLEbook-图书表! |(BIDCHAR(10)primarykey,-图书编号titleCHAR(50)NOTNULL,-书名authorCHAR(20)NOTNULL,-作者)GOCREATETABLEb

4、orrow-借书表(borrowIDCHAR(10)primarykey,-借书编号stuIDCHAR(10)foreig nkey(stuID)refere ncesstude nt(stuID),-学生编号BIDCHAR(10)foreignkey(BID)referencesbook(BID),-图书编号T_timedatetimeNOTNULL,-借出日期B_timedatetime-归还日期_ )GO-学生信息表中插入数据-INSERTINTOstude nt(stulD,stuName,major)VALUES(1001,林林,计算机)INSERTINTOstudent(stulD

5、,stuName,major)VALUES(1002,白杨,计算机)INSERTINTOstude nt(stulD,stuName,major)VALUES(1003,虎子,英语)INSERTINTOstudent(stulD,stuName,major)VALUES(1004,北漂的雪，工商管理)INSERTINTOstudent(stuID,stuName,major)VALUES(1005,五月,数学)-图书信息表中插入数据-INSERTINTObook(BID,title,author)VALUES(B001,人生若只如初见,安意如)INSERTINTObook(BID,title,

6、author)VALUES(B002,入学那天遇见你,晴空)INSERTINTObook(BID,title,author)VALUES(B003,感谢折磨你的人,如娜)INSERTINTObook(BID,title,author)VALUES(B004,我不是教你诈,刘庸)INSERTINTObook(BID,title,author)VALUES(B005,英语四级,白雪)-借书信息表中插入数据-INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T001,1001,B001,2007-12-26,null)INSERTINT

7、Oborrow(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,20 07-12-25)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T004,1005,B002,2007-12-16,2 008-1-7)INSERTINTOborrow(borrowlD,stulD,BI

8、D,T_time,B_time)VALUES(T005,1002,B004,2007-12-22,n ull)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T006,1005,B005,2008-1-6, null jINSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T007,1002,B001,2007-9-11, nu II)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T008,1005,B

9、004,2007-12-10,n ull)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T009,1004,B005,2007-10-16,2 007-12-18)iINSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T010,1002,B002,2007-9-15,20 08-1-5)INSERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T011,1004,B003,2007-12-28,n ull)IN

10、SERTINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T012,1002,B003,2007-12-30,n ull)标准答案：J -1)查询“计算机”专业学生在“ 2007-12-15”至“ 2008-1-8”时间段内借书的学 生编号、学生名称、图书编号、图书名称、借出日期一select学生编号=stuID,学生名称=(selectstuNamefromstudentwherestulD=borrow.stulD)图书编号=BID,图书名称=(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_

11、timefromborrowwherestulDi n(selectstulDfromstude ntwheremajor=计算机)a ndT_time2007-12-15a ndT_time2*(selectco un t(*)fromProWagewhereWage=PWage) updateProWagesettotal=total+AWage,Wage=Wage+AWageelsebreak end print共加薪：+co nvert(varchar,total)+元 print加薪后的程序员工资列表： select*fromProWage-调用存储过程1-execSum_wageP

12、Wage=2000,AWage=100,total=0execSum_wagePWage=2200,AWage=100,total=0 execSum_wagePWage=3000,AWage=100,total=0 execSum_wagePWage=4000,AWage=100,total=0 execSum_wagePWage=5000,AWage=100,total=0 execSum_wagePWage=6000,AWage=100,total=0-2、创建存储过程2- ifexists(select*fromsysobjectswhere name=Avg_wage) droppr

13、ocedureAvg_wage11GOcreateprocedureAvg_wagePWagei nt,AWagei nt,totali ntas while(1=1) begi n if(selectAvg(Wage)fromProWage)v=PWage) updateProWagesettotal=total+AWage,Wage=Wage+AWage else break endprint共加薪：+co nvert(varchar,total)+元print加薪后的程序员工资列表：select*fromProWage-调用存储过程-execAvg_wagePWage=3000,AWag

14、e=200,total=0execAvg wagePWage=4500,AWage=200,total=0题目3:学生成绩信息三个表，结构如下:学生表：Member字段名称数据类型说明MIDChar(10)学生号，主键MNameChar(50)姓名课程表:字段名称数据类型说明FIDChar(10)课程，主键FNameChar(50)课程名成绩表：Score字段名称数据类型说明SIDint自动编号，主键，成绩记录号FIDChar(10)课程号，外键MID:- . 1Char(10)学生号，外键Scoreint成绩请编写T-SQL语句来实现如下功能：1）查询各个学生语文、数学、英语、历史课程成绩，

15、例如下表:姓名语文数学英语历史张萨78678976王强89678496李三70879256李四807897662）查询四门课中成绩低于70分的学生及相对应课程名和成绩。3）统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。4）创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。建表语句：USEmasterGO/ $建丿库 $ /-检验数据库是否存在，如果为真，删除此数据库-IFexists(SELECT*FROMsysdatabasesWHERE name=Stude nt)DROPDATABASEStude ntGO CREATEDATABA

16、SEStude ntGO-建数据表-USEStude ntGOCREATETABLEMember-学生表(MIDchar(10)primarykey,-学生号MNameCHAR(5O)NOTNULL-姓名) GO CREATETABLEF-课程表；I (FIDchar(10)primarykey,-课程号FNameCHAR(50)NOTNULL-课程名)GOCREATETABLEscore-学生成绩表(SIDintidentity(1,1)primarykey,-成绩记录号FIDchar(10)foreig nkey(FID)refere ncesF(FID),-课程号MIDchar(10)f

17、oreignkey(MID)referencesMember(MID),-学生号ScoreintNOTNULL-成绩)GO-课程表中插入数据-INSERTINTOF(FID,FName)VALUES(FOO1,语文)INSERTINTOF(FID,FName)VALUES(FOO2,数学)INSERTINTOF(FID,FName)VALUES(FOO3,英语) INSERTINTOF(FID,FName)VALUES(FOO4,历史)-学生表中插入数据-INSERTINTOMember(MID,MName)VALUES(MOO1,张萨)INSERTINTOMember(MID,MName)V

18、ALUES(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(

19、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(F0

20、01,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(F0

21、03,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,MID,Score)VALUES(F004,M004,66)INSERTINTOScore(FID,MID,Score)VALUES(F0

22、01,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)查询各个学生语文、数学、英语、历史课程成绩-SELECTMem