SQL存儲過程實(shí)例

題目11、學(xué)校圖書館借書信息管理系統(tǒng)建立三個表：學(xué)生信息表：studentstuNamemajorVarchar(10)Varchar(50)專業(yè)圖書表：book字段名稱char(10)char(50)char(20)author作者借書信息表：borrow字段名稱borrowIDstuIDchar(10)char(10)char(10)datetimedatetimeT_timeB_time還書日期請編寫SQL語句完成以下的功能：1)2007-12-15”時間段內(nèi)借書的學(xué)生編號、學(xué)生名稱、圖書編號、圖書名稱、借出日期；參考查詢結(jié)果如下圖所示：2)查詢所有借過圖書的學(xué)生編號、學(xué)生名稱、專業(yè)；參考查詢結(jié)果如下圖所示：3)查詢借過作者為“安意如”的圖書的學(xué)生姓名、圖書名稱、借出日期、歸還日期；參考查詢結(jié)果如下圖所示：4)查詢目前借書但未歸還圖書的學(xué)生名稱及未還圖書數(shù)量；參考查詢結(jié)果如下圖所示：附加：建表語句:/*$$$$$$$$$$$$$建庫$$$$$$$$$$$$$$$$$$$$$$$$*/檢驗(yàn)數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫--stuNameCHAR(10)NOTNULL,majorCHAR(50)NOTNULLCREATETABLEbook圖書表T_timedatetimeNOTNULL,學(xué)生信息表中插入數(shù)據(jù)--INSERTINTOstudent(stuID,stuName,major)VALUES('1001','林林','計算機(jī)')INSERTINTOstudent(stuID,stuName,major)VALUES('1002','白楊','計算機(jī)')INSERTINTOstudent(stuID,stuName,major)VALUES('1003','虎子','英語')INSERTINTOstudent(stuID,stuName,major)VALUES('1004','北漂的雪','工商管理')INTOINTOINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12-26',null)INSERTborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5',null)INSERTborrow(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')INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004','2007-12-22',null)INTOINTOINTOINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null)borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11',null)borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',null)INSERTborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18')INTOINTOINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T010','1002','B002','2007-9-15','2008-1-5')borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28',null)INSERTborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','1002','B003','2007-12-30',null)標(biāo)準(zhǔn)答案:--1)查詢“計算機(jī)”專業(yè)學(xué)生在“2007-12-15”至“2008-1-8”時間段內(nèi)借書的學(xué)生編號、學(xué)生名稱、圖書編號、圖書名稱、借出日期—select學(xué)生編號=stuID,學(xué)生名稱=(selectstuNamefromstudentwherestuID=borrow.stuID),圖書編號=BID,圖書名稱=(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_timefromborrowwherestuIDin(selectstuIDfromstudentwheremajor='計算機(jī)')and--2)查詢所有借過圖書的學(xué)生編號、學(xué)生名稱、專業(yè)--select學(xué)生編號=stuID,學(xué)生名稱=stuName,專業(yè)=majorfromstudentwherestuIDin(selectstuIDfromborrow)--3)查詢借過作者為“安意如”的圖書的學(xué)生姓名、圖書名稱、借出日期--select學(xué)生名稱=(selectstuNamefromstudentwherestuID=borrow.stuID),圖書名稱=(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_time,歸還日期=B_timefromborrowwhereBIDin(selectBIDfrombookwhere安意如')--4)查詢目前借書但未歸還圖書的學(xué)生名稱及未還圖書數(shù)量--select學(xué)生名稱=(selectstuNamefromstudentwherestuID=borrow.stuID),借書數(shù)量=count(*)fromborrowwhereB_timeisnullgroupbystuID題目2程序員工資表：ProWage說明自動編號，主鍵程序員姓名工資PNameWageChar(10)int創(chuàng)建一個存儲過程，對程序員的工資進(jìn)行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，給所有人加薪，每次加100，再進(jìn)行分析，直到有一半以上的人大于2000元為止，存儲過程執(zhí)行完后，最終加了多少錢？例如：如果有百分之五十的人薪水不到2000，給所有人加薪，每次加100元，直到有一半以上的人工資大于2000元，調(diào)用存儲過程后的結(jié)果如圖：請編寫T-SQL來實(shí)現(xiàn)如下功能：1)創(chuàng)建存儲過程，查詢是否有一半程序員的工資在2200300040005000或60001002200，3000，3500，4000，5000或6000元。2)創(chuàng)建存儲過程，查詢程序員平均工資在4500元，如果不到則每個程序員每次加200元，至到所有程序員平均工資達(dá)到4500元。建表語句/*$$$$$$$$$$$$$建庫$$$$$$$$$$$$$$$$$$$$$$$$*/檢驗(yàn)數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫--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)標(biāo)準(zhǔn)答案：ifexists(select*fromsysobjectswherename='Sum_wage')dropprocedureSum_wageGOcreateprocedureSum_wageif(selectcount(*)fromProWage)>2*(selectcount(*)fromProWagewhereWage>=@PWage)updateProWageset@total=@total+@AWage,Wage=Wage+@AWageelseexecSum_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=0ifexists(select*fromsysobjectswherename='Avg_wage')createprocedureAvg_wageexecAvg_wage@PWage=3000,@AWage=200,@total=0execAvg_wage@PWage=4500,@AWage=200,@total=0題目3：學(xué)生成績信息三個表，結(jié)構(gòu)如下：學(xué)生表：Member數(shù)據(jù)類型Char(10)Char(50)說明說明說明學(xué)生號，主鍵姓名MName課程表：數(shù)據(jù)類型Char(10)Char(50)課程，主鍵課程名FName成績表：Score字段名稱自動編號，主鍵，成績記錄號課程號，外鍵Char(10)Char(10)intScore成績請編寫T-SQL語句來實(shí)現(xiàn)如下功能：1)查詢各個學(xué)生語文、數(shù)學(xué)、英語、歷史課程成績，例如下表：姓名張薩王強(qiáng)李三李四8967849670879256807897662)查詢四門課中成績低于70分的學(xué)生及相對應(yīng)課程名和成績。3)統(tǒng)計各個學(xué)生參加考試課程的平均分，且按平均分?jǐn)?shù)由高到底排序。4)創(chuàng)建存儲過程，分別查詢參加1、2、3、4門考試及沒有參加考試的學(xué)生名單，要求顯示姓名、學(xué)號。建表語句:/*$$$$$$$$$$$$$建庫$$$$$$$$$$$$$$$$$$$$$$$$*/檢驗(yàn)數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫--CREATEDATABASEStudentGOCREATETABLEMember學(xué)生表CREATETABLEF課程表SIDintidentity(1,1)primarykey,成績記錄號FIDchar(10)foreignkey(FID)referencesF(FID),MIDchar(10)foreignkey(MID)referencesMember(MID),ScoreintNOTNULL成績INSERTINTOMember(MID,MName)VALUES('M001','張薩')INSERTINTOMember(MID,MName)VALUES('M002','王強(qiáng)')INSERTINTOMember(MID,MName)VALUES('M003','李三')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)標(biāo)準(zhǔn)答案:--1)查詢各個學(xué)生語文、數(shù)學(xué)、英語、歷史課程成績--SELECTMember.MNameAS姓名,英語=