SQL存儲過程實例練習和答案

1、1、學校圖書館借書信息管理系統(tǒng)建立三個表:學生信息表：student字段名稱數(shù)據類型說明stuIDchar(10)學生編號，主鍵stuNameVarchar(10)學生名稱majorVarchar(50)專業(yè)圖書表：book字段名稱數(shù)據類型說明BIDchar(10)圖書編號，主鍵titlechar(50)書名authorchar(20)作者借書信息表：borrow字段名稱數(shù)據類型說明borrowIDchar(10)借書編號，主鍵stuIDchar(10)學生編勺,外鍵BIDi i . ,| char(10) 圖書編號，外鍵T_timedatetime借書日期B_timedatetime還書日期

2、請編寫SQL語句完成以下的功能:1）查詢“計算機”專業(yè)學生在“ 2007-12-15”至“2008-1-8”時間段內借書的學生編號、學生 名I稱、圖書編號、圖書名稱、借出日期；參考查詢結果如下圖所示：2）查詢所有借過圖書的學生編號、學生名稱、專業(yè)；參考查詢結果如下圖所示：3）查詢借過作者為“安意如”的圖書的學生姓名、圖書名稱、借出日期、歸還日期；參考查詢結果如下圖所示：4）查詢目前借書但未歸還圖書的學生名稱及未還圖書數(shù)量；參考查詢結果如下圖所示:附加：建表語句：USEmasterGO/mgggCtgCtgA 片/$J牛 $/-檢驗數(shù)據庫是否存在，如果為真，刪除此數(shù)據庫-IFexists(SEL

3、ECT*FROMsysdatabasesWHEREname='BOOK') DROPDATABASEBOOK GO CREATEDATABASEBOOK GO -建數(shù)據表- USEBOOK GO CREATETABLEstudent-學生信息表 ( stuIDCHAR(10)primarykey,-學生編號 stuNameCHAR(10)NOTNULL,-學生名稱 majorCHAR(50)NOTNULL-專業(yè) ) GO CREATETABLEbook-圖書表i ./ ! | ( BIDCHAR(10)primarykey,-圖書編號，- titleCHAR(50)NOTNUL

4、L,-書名 authorCHAR(20)NOTNULL,-作者 ) GO CREATETABLEborrow-借書表 ( borrowIDCHAR(10)primarykey,-借書編號 stuIDCHAR(10)foreignkey(stuID)referencesstudent(stuID),-學生編號 BIDCHAR(10)foreignkey(BID)referencesbook(BID),-圖書編號 T_timedatetimeNOTNULL,-借出日期 B_timedatetime-歸還日期 _)GO -學生信息表中插入數(shù)據- INSERTINTOstudent(stuID,stu

5、Name,major)VALUES('1001','林林','計算機') INSERTINTOstudent(stuID,stuName,major)VALUES('1002','白楊','計算機') INSERTINTOstudent(stuID,stuName,major)VALUES('1003','虎子','英語') INSERTINTOstudent(stuID,stuName,major)VALUES('1004','

6、;北漂的雪,'工商管理') INSERTINTOstudent(stuID,stuName,major)VALUES('1005','五月,'數(shù)學') -圖書信息表中插入數(shù)據- INSERTINTObook(BID,title,author)VALUES('B001','人生若只如初見,'安意如') INSERTINTObook(BID,title,author)VALUES('B002','入學那天遇見你','晴空') INSERTINTObook(

7、BID,title,author)VALUES('B003','感謝折磨你的人','如娜') INSERTINTObook(BID,title,author)VALUES('B004','我不是教你詐','劉庸') INSERTINTObook(BID,title,author)VALUES('B005','英語四級','白雪') -借書信息表中插入數(shù)據- INSERTINTOborrow(borrowID,stuID,BID,T_time,B_tim

8、e)VALUES('T001','1001','B001','2007-12-26',n ull)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','

9、B001','2007-10-8','20 07-12-25')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2 008-1-7')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004',

10、9;2007-12-22',n ull)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null3INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11',nu ll)INSERTINTOborrow(borro

11、wID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',n ull)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2 007-12-18')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_tim

12、e)VALUES('T010','1002','B002','2007-9-15','20 08-1-5')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28',n ull)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','

13、1002','B003','2007-12-30',n ull) 標準答案：:-1)查詢“計算機”專業(yè)學生在“ 2007-12-15”至“2008-1-8”時間段內借書的學生編號、學生名稱、圖書編號、圖書名稱、借出日期一select學生編號=stuID,學生名稱=(selectstuNamefromstudentwherestuID=borrow.stuID)圖書編號=BID,圖書名稱=(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_timefromborrowwherestuIDin(selectstuI

14、Dfromstudentwheremajor='計算機')andT_time>'2007-12-15'andT_time<'2008-1-8'-2)查詢所"借過圖書的學生編號、學生名稱、專業(yè) -select學生編號=stuID,學生名稱=stuName,專業(yè)=majorfromstudentwherestuIDin(selectstuIDfromborrow)-3)查詢借過作者為“安意如”的圖書的學生姓名、圖書名稱、借出日期-select 學生名稱=(selectstuNamefromstudentwherestuID=bo

15、rrow.stuID)圖書名稱 =(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_time,歸還日期 =B_timefromborrowwhereBIDin(selectBIDfrombookwhereauthor='安意女口 ')-4)查詢目前借書但未歸還圖書的學生名稱及未還圖書數(shù)量-select 學生名稱=(selectstuNamefromstudentwherestuID=borrow.stuID淌書數(shù)量 =count(*)fromborrowwhereB timeisnullgroupbystuID題目2程序員工資表：P

16、roWage字段名稱數(shù)據類型說明IDint自動編號，主鍵PNameChar(10)程序員姓名Wageint工資創(chuàng)建一個存儲過程，對程序員的工資進行分析，月薪 1500到10000不等，如果有白分之五十的人薪水不到2000元，給所有人加薪，每次加100,再進行分析，直到有一半以上的人大丁 2000元為 止，存儲過程執(zhí)行完后，最終加了多少錢？例如：如果有白分之五十的人薪水不到2000,給所有人加薪，每次加100元，直到有一半以上的人工資大丁 2000元，調用存儲過程測試。請編寫T-SQL來實現(xiàn)如下功能：1) 創(chuàng)建存儲過程，查詢是否有一半程序員的工資在2200、3000、3500、4000、5000

17、或6000元之上，如果不到分別每次給每個程序員加薪 100元，至之一半程序員的工資達到 2200,3000,3500, I4000, 5000 或 6000 元。2) 創(chuàng)建存儲過程，查詢程序員平均工資在 4500元，如果不到則每個程序員每次加 200元，至到所有程序員平均工資達到4500元。建表語句USEmasterGOFa? ctctctctctctctctctctctctctctctctctctctctctctctct*/$J 牛 $/-檢驗數(shù)據庫是否存在，如果為真，刪除此數(shù)據庫-IFexists(SELECT*FROMsysdatabasesWHEREname='Wage'

18、;)DROPDATABASEWageGO CREATEDATABASEWageGO；-建數(shù)據表-USEWageGOCREATETABLEProWage-程序員工資表(IDintidentity(1,1)primarykey,-工資編號PNameCHAR(10)NOTNULL,-程序員姓名WageintNOTNULL-工資)GO-插入數(shù)據-INSERTINTOProWage(PName,Wage)VALUES('來棱',1900)INSERTINTOProWage(PName,Wage)VALUES('張三',1200)INSERTINTOProWage(PNam

19、e,Wage)VALUES('李四',1800) INSERTINTOProWage(PName,Wage)VALUES('二月,3500) INSERTINTOProWage(PName,Wage)VALUES('藍天',2780) 標準答案： -1、創(chuàng)建存儲過程-ifexists(select*fromsysobjectswherename='Sum_wage') dropprocedureSum_wageGO createprocedureSum_wage PWageint, AWageint, totalint as while(

20、1=1) begin if(selectcount(*)fromProWage)>2*(selectcount(*)fromProWagewhereWage>=PWage) updateProWagesettotal=total+AWage,Wage=Wage+AWage else breakV I/*Jendprint' 共加薪：'+convert(varchar,total)+'元'print '加薪后的程序員工資列表：select*fromProWage-調用存儲過程1-execSum_wagePWage=2000,AWage=100,

21、total=0 execSum_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、創(chuàng)建存儲過程2-ifexists(select*fromsysobjectswherename='Avg_wage') dropprocedureAvg

22、_wageGOcreateprocedureAvg_wage PWageint, AWageint, totalintas while(1=1) begin if(selectAvg(Wage)fromProWage)<=PWage) updateProWagesettotal=total+AWage,Wage=Wage+AWage elsebreak endprint' 共加薪：'+convert(varchar,total)+'元'print '加薪后的程序員工資列表：select*fromProWage-調用存儲過程-execAvg_wage

23、PWage=3000,AWage=200,total=0 execAvg wagePWage=4500,AWage=200,total=0題目3:學生成績信息三個表，結構如下:學生表：Member字段名稱數(shù)據類型說明MIDChar(10)學生號，主鍵MNameChar(50)姓名課程表:字段名稱數(shù)據類型說明FIDChar(10)課程，主鍵FNameChar(50)課程名成績表：Score字段名稱數(shù)據類型說明SIDint自動編號，主鍵，成績記錄號FIDChar(10)課程號，外鍵MID :- .1Char(10)學生號，外鍵Scoreint成績請編寫T-SQL語句來實現(xiàn)如下功能：1）查詢各個學生

24、語文、數(shù)學、英語、歷史課程成績，例如下表:姓名語文數(shù)學英語歷史張薩78678976王強89678496李三70879256李四807897662)查詢四門課中成績低丁 70分的學生及相對應課程名和成績。3) 統(tǒng)計各個學生參加考試課程的平均分，且按平均分數(shù)由高到底排序。4) 創(chuàng)建存儲過程，分別查詢參加1、2、3、4門考試及沒有參加考試的學生名單，要求顯示姓名、 學號。建表語句： USEmasterGOFa? ctctctctctctctctctctctctctctctctctctctctctctctct*/ /$J 牛 $/ -檢驗數(shù)據庫是否存在，如果為真，刪除此數(shù)據庫-IFexists(SEL

25、ECT*FROMsysdatabasesWHEREname='Student') DROPDATABASEStudentGO CREATEDATABASEStudent GO -建數(shù)據表- USEStudent GO CREATETABLEMember-學生表 ( MIDchar(10)primarykey,-學生號 MNameCHAR(50)NOTNULL-姓名 ) GO CREATETABLEF-課程表 :I (FIDchar(10)primarykey,-課程號 FNameCHAR(50)NOTNULL-課程名 ) GO CREATETABLEscore-學生成績表 (

26、SIDintidentity(1,1)primarykey,-成績記錄號 FIDchar(10)foreignkey(FID)referencesF(FID),-課程號 MIDchar(10)foreignkey(MID)referencesMember(MID),-學生號 ScoreintNOTNULL-成績 ) GO-課程表中插入數(shù)據-INSERTINTOF(FID,FName)VALUES('F001','語文') INSERTINTOF(FID,FName)VALUES('F002','數(shù)學') INSERTINTOF(F

27、ID,FName)VALUES('F003','英語') INSERTINTOF(FID,FName)VALUES('F004','歷史') -學生表中插入數(shù)據- INSERTINTOMember(MID,MName)VALUES('M001','張薩')INSERTINTOMember(MID,MName)VALUES('M002','王強')INSERTINTOMember(MID,MName)VALUES('M003','李三')

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

29、9;璐璐')INSERTINTOMember(MID,MName)VALUES('M009','珊珊')INSERTINTOMember(MID,MName)VALUES('M010','香奈兒') -成績表中插入數(shù)據-INSERTINTOScore(FID,MID,Score)VALUES('F001','M00T,78)INSERTINTOScore(FID,MID,Score)VALUES('F002','M001',67)INSERTINTOScore(FID

30、,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(&#

31、39;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','M0

32、03',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)INSERTINT

33、OScore(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