SQL详解实例(存储过程),SQL server存储过程

("题目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语句完成以下的功能：1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：2)查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示：3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；参考查询结果如下图所示：4)查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示：附加：建表语句:USEmasterGO/$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$/--检验数据库是否存在，如果为真，删除此数据库--IFexists(SELECTFROMsysdatabasesWHEREname='BOOK')DROPDATABASEBOOKGOCREATEDATABASEBOOKGO--建数据表--USEBOOKGOCREATETABLEstudent--学生信息表(stuIDCHAR(10)primarykey,--学生编号stuNameCHAR(10)NOTNULL,--学生名称majorCHAR(50)NOTNULL--专业)GOCREATETABLEbook--图书表(BIDCHAR(10)primarykey,--图书编号titleCHAR(50)NOTNULL,--书名authorCHAR(20)NOTNULL,--作者)GOCREATETABLEborrow--借书表(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','英语四级','白雪')--借书信息表中插入数据--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')INSERTINTOborrow(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,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','2007-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,T_time,B_time)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='计算机')andT_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字段名称数据类型说明IDint自动编号，主键PNameChar(10)程序员姓名Wageint工资创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？例如：如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：请编写T-SQL来实现如下功能：1)创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。2)创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元，至到所有程序员平均工资达到4500元。建表语句USEmasterGO/$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$/--检验数据库是否存在，如果为真，删除此数据库--IFexists(SELECTFROMsysdatabasesWHEREname='Wage')DROPDATABASEWageGOCREATEDATABASEWageGO--建数据表--USEWageGOCREATETABLEProWage--程序员工资表(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(selectfromsysobjectswherename='Sum_wage')dropprocedureSum_wageGOcreateprocedureSum_wage@PWageint,@AWageint,@totalintaswhile(1=1)beginif(selectcount()fromProWage)>2(selectcount()fromProWagewhereWage>=@PWage)updateProWageset@total=@total+@AWage,Wage=Wage+@AWageelsebreakendprint'一共加薪：'+convert(varchar,@total)+'元'print'加薪后的程序员工资列表：'selectfromProWage--调用存储过程1--execSum_wage@PWage=2000,@AWage=100,@total=0execSum_wage@PWage=2200,@AWage=100,@total=0execSum_wage@PWage=3000,@AWage=100,@total=0execSum_wage@PWage=4000,@AWage=100,@total=0execSum_wage@PWage=5000,@AWage=100,@total=0execSum_wage@PWage=6000,@AWage=100,@total=0--2、创建存储过程2--ifexists(selectfromsysobjectswherename='Avg_wage')dropprocedureAvg_wageGOcreateprocedureAvg_wage@PWageint,@AWageint,@totalintaswhile(1=1)beginif((selectAvg(Wage)fromProWage)<=@PWage)updateProWageset@total=@total+@AWage,Wage=Wage+@AWageelsebreakendprint'一共加薪：'+convert(varchar,@total)+'元'print'加薪后的程序员工资列表：'selectfromProWage--调用存储过程--execAvg_wage@PWage=3000,@AWage=200,@total=0execAvg_wage@PWage=4500,@AWage=200,@total=0题目3：学生成绩信息三个表，结构如下：学生表：Member字段名称数据类型说明MIDChar(10)学生号，主键MNameChar(50)姓名课程表：字段名称数据类型说明FIDChar(10)课程，主键FNameChar(50)课程名成绩表：Score字段名称数据类型说明SIDint自动编号，主键，成绩记录号FIDChar(10)课程号，外键MIDChar(10)学生号，外键Scoreint成绩请编写T-SQL语句来实现如下功能：1)查询各个学生语文、数学、英语、历史课程成绩，例如下表：姓名语文数学英语历史张萨78678976王强89678496李三70879256李四807897662)查询四门课中成绩低于70分的学生及相对应课程名和成绩。3)统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。4)创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。建表语句:USEmasterGO/$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$/--检验数据库是否存在，如果为真，删除此数据库--IFexists(SELECTFROMsysdatabasesWHEREname='Student')DROPDATABASEStudentGOCREATEDATABASEStudentGO--建数据表--USEStudentGOCREATETABLEMember--学生表(MIDchar(10)primarykey,--学生号MNameCHAR(50)NOTNULL--姓名)GOCREATETABLEF--课程表(FIDchar(10)primarykey,--课程号FNameCHAR(50)NOTNULL--课程名)GOCREATETABLEscore--学生成绩表(SIDintidentity(1,1)primarykey,--成绩记录号FIDchar(10)foreignkey(FID)referencesF(FID),--课程号MIDchar(10)foreignkey(MID)referencesMember(MID),--学生号ScoreintNOTNULL--成绩)GO--课程表中插入数据--INSERTINTOF(FID,FName)VALUES('F001','语文')INSERTINTOF(FID,FName)VALUES('F002','数学')INSERTINTOF(FID,FName)VALUES('F003','英语')INSERTINTOF(FID,FName)VALUES('F004','历史')--学生表中插入数据--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,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.FNameWHEN'语文'THENScore.ScoreEND),数学=SUM(CASEF.FNameWHEN'数学'THENScore.ScoreEND),语文=SUM(CASEF.FNameWHEN'英语'THENScore.ScoreEND),历史=SUM(CASEF.FNameWHEN'历史'THENScore.ScoreEND)FROMScore,Member,FWHEREF.FID=Score.FIDANDMember.MID=Score.MIDGROUPBYMember.MName--2)查询四门课中成绩低于70分的学生及相对应课程名和成绩--select姓名=(selectMNamefromMemberwhereMID=Score.MID),课程名=(selectFNamefromFwhereFID=Score.FID),成绩=ScorefromScorewhereScore<70--3)统计各个学生四课程的平均分，且按平均分数由高到底排序--select姓名=(selectMNamefromMemberwhereMID=Score.MID),平均分=Avg(Score)fromScoregroupbyMIDorderby平均分desc--4)创建存储过程--ifexists(selectfromsysobjectswherename='P_stu')dropprocedureP_stuGOcreateprocedureP_stu@numintAsprint'参加'+convert(varchar(5),@num)+'门课考试的学生姓名及学号：'select姓名=(selectMNamefromMemberwhereMID=Score.MID),学号=MIDfromScoregroupbyMIDhavingcount()=@num--调用存储过程--execP_stu@num=2",)