計(jì)科09-數(shù)據(jù)庫(kù)技術(shù)實(shí)踐-第三部分

./實(shí)驗(yàn)報(bào)告課程名稱數(shù)據(jù)庫(kù)技術(shù)實(shí)踐實(shí)驗(yàn)項(xiàng)目存儲(chǔ)過(guò)程、觸發(fā)器、用戶自定義函數(shù)與游標(biāo)實(shí)驗(yàn)儀器SQLServer2008系別____計(jì)算機(jī)科學(xué)與技術(shù)系_專業(yè)____計(jì)算機(jī)科學(xué)與技術(shù)____班級(jí)/學(xué)號(hào)_______________________學(xué)生_______________________實(shí)驗(yàn)日期__________成績(jī)_______________________指導(dǎo)教師_________________[在容說(shuō)明部分請(qǐng)總體說(shuō)明在本部分實(shí)踐過(guò)程中,具體都完成了哪些容]一．容說(shuō)明[請(qǐng)按照下面練習(xí)題的要求,完成各項(xiàng)容,并說(shuō)明每個(gè)題目完成的情況,是否存在問(wèn)題,如何解決等]二.實(shí)驗(yàn)步驟與容如無(wú)特別說(shuō)明,以下各題均利用之前建立的Students數(shù)據(jù)庫(kù)以及Student、Course和SC表實(shí)現(xiàn)。創(chuàng)建滿足下述要求的存儲(chǔ)過(guò)程,并查看存儲(chǔ)過(guò)程的執(zhí)行結(jié)果。查詢每個(gè)學(xué)生的修課總學(xué)分,要求列出學(xué)生學(xué)號(hào)及總學(xué)分。createprocSumCreditasselectsno學(xué)號(hào),sum<credit>總學(xué)分o=ogroupbysnogoexecSumCredit查詢學(xué)生的學(xué)號(hào)、、修的課程號(hào)、課程名、課程學(xué)分,將學(xué)生所在系作為輸入?yún)?shù),默認(rèn)值為"計(jì)算機(jī)系"。執(zhí)行此存儲(chǔ)過(guò)程,并分別指定一些不同的輸入?yún)?shù)值,查看執(zhí)行結(jié)果。createprocStudentInformationdeptvarchar<50>='計(jì)算機(jī)系',snamechar<50>asselectsc.sno學(xué)號(hào),sname,o課程號(hào),cname課程名,credit學(xué)分fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheredept=deptandSname=snamegoexecStudentInformation'信息管理系','吳賓'execStudentInformationsname='勇'查詢指定系的男生人數(shù),其中系為輸入?yún)?shù),人數(shù)為輸出參數(shù)。createprocMan_Numdeptvarchar<50>,rsintoutputasselectrs=COUNT<*>fromStudentwhereDept=deptandSex='男'godeclarersintexecMan_Num'信息管理系',rsoutputselectrsas人數(shù)查詢考試平均成績(jī)超過(guò)指定分值的學(xué)生學(xué)號(hào)和平均成績(jī)。createprocAvgGradegradeintasselectsno,AVG<grade>as'平均成績(jī)'fromSCgroupbySnohavingAVG<grade>>gradegoexecAvgGrade60查詢查詢指定系的學(xué)生中,選課門數(shù)最多的學(xué)生的選課門數(shù)和平均成績(jī),要求系為輸入?yún)?shù),選課門數(shù)和平均成績(jī)用輸出參數(shù)返回。createprocChoose_Coursedeptvarchar<50>,rsintoutput,avgintoutputasselecttop1rs=COUNT<*>,avg=avg<grade>fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheredept=deptgroupbysc.Snogodeclarersint,avgintexecChoose_Course'信息管理系',rsoutput,avgoutputselectrsas選課門數(shù),avgas平均成績(jī)刪除指定學(xué)生的修課記錄,其中學(xué)號(hào)為輸入?yún)?shù)。createprocDel_Coursesnochar<50>asdeletefromSCwhereSno=snogoexecDel_Course'0831102'修改指定課程的開(kāi)課學(xué)期。輸入?yún)?shù)為：課程號(hào)和修改后的開(kāi)課學(xué)期,開(kāi)課學(xué)期的默認(rèn)值為2。如果指定的開(kāi)課學(xué)期不在1~8圍,則不進(jìn)行修改。利用SSMS工具查看在students數(shù)據(jù)庫(kù)中創(chuàng)建的全部存儲(chǔ)過(guò)程。Students=>可編程性=>存儲(chǔ)過(guò)程修改第1題〔1的存儲(chǔ)過(guò)程,使之能夠查詢指定系中,每個(gè)學(xué)生選課總門數(shù)、總學(xué)分和考試平均成績(jī)。alterprocSumCreditdeptnvarchar<20>asselectCOUNT<SC.Cno>總門數(shù),sum<credit>總學(xué)分,AVG<Grade>平均成績(jī)fromstudentsleftjoinscons.sno=o=owheredept=deptgroupbysc.SnogoexecSumCredit'信息管理系'創(chuàng)建滿足下述要求的觸發(fā)器〔前觸發(fā)器、后觸發(fā)器均可,并驗(yàn)證觸發(fā)器執(zhí)行情況。限制每個(gè)學(xué)期開(kāi)設(shè)的課程總學(xué)分在20~30圍。altertriggerTR_SumGradeoncourseafterinsertasdeclaresint,xint,yintsets=<selectsum<Credit>fromcoursewheresemesterin<selectsemesterfrominserted>>if<20<sands<30>beginprint'課程總學(xué)分沒(méi)有超出圍!!!'printsendelsebeginprint'課程總學(xué)分超出圍!!!'print's='printsrollbackendinsertintocoursevalues<'C010','匯編語(yǔ)言',200,1>限制每個(gè)學(xué)生每學(xué)期選課門數(shù)不能超過(guò)5門〔設(shè)只針對(duì)插入操作。ALTERtriggerTR_MENonscafterinsertasdeclarexintsetx=<selectcount<*>o=owheresemesterin<selectsemesterfromcoursewhereoin<selectofrominserted>>andsc.snoin<selectsnofrominserted>>if<x>5>beginselect*o=oselect*frominsertedprintxprint'選課門數(shù)超過(guò)門'rollbackend在Students數(shù)據(jù)庫(kù)建立如下所示的工作表和職工表CREATETABLE工作表<工作號(hào)CHAR<8>PRIMARYKEY,最低工資SMALLINT,最高工資SMALLINT>CREATETABLE職工表<職工號(hào)CHAR<7>PRIMARYKEY,職工名CHAR<10>NOTNULL,工作號(hào)CHAR<8>REFERENCES工作表<工作號(hào)>,基本工資SMALLINT,浮動(dòng)工資SMALLINT>利用這兩表建立滿足如下要求的觸發(fā)器。限制職工的基本工資和浮動(dòng)工資之和必須大于等于2000。createtriggerTR_Salaryon職工表afterinsert,updateasdeclarexSMALLINT,ySMALLINT,zSMALLINTsetx=<select基本工資from職工表where職工號(hào)in<select職工號(hào)frominserted>>sety=<select浮動(dòng)工資from職工表where職工號(hào)in<select職工號(hào)frominserted>>setz=x+yif<z>=2000>beginprint'操作符合要求'endelsebeginprintxprintyprintzprint'請(qǐng)注意,職工的基本工資和浮動(dòng)工資之和小于?。。。?select*from職工表select*frominsertedrollbackendinsertinto工作表values<'G001',10000,1000>insertinto職工表values<'Z001','三','G001',1000,100>限制工作表中最高工資不能低于最低工資的1.5倍。createtriggerTR_Salary1on工作表afterinsert,updateasdeclarexSMALLINT,ySMALLINT,zfloatsetx=<select最低工資from工作表where工作號(hào)in<select工作號(hào)frominserted>>sety=<select最高工資from工作表where工作號(hào)in<select工作號(hào)frominserted>>setz=y/xif<z>=1.5>beginprint'操作符合要求'endelsebeginprintxprintyprintzselect*from工作表select*frominsertedprint'請(qǐng)注意,最高工資低于最低工資的.5倍'rollbackendinsertinto工作表values<'G002',1000,1000>限制不能刪除基本工資低于1500的職工。altertriggerTR_Salary2on職工表afterdeleteasifexists<select*from職工表where基本工資<1500>beginprint'操作符合要求'endelsebeginprint'不能刪除基本工資低于的職工'select*from職工表select*fromdeletedrollbackend創(chuàng)建滿足下述要求的用戶自定義標(biāo)量函數(shù)。查詢指定學(xué)生已經(jīng)得到的修課總學(xué)分〔考試及格的課程才能拿到學(xué)分,學(xué)號(hào)為輸入?yún)?shù),總學(xué)分為函數(shù)返回結(jié)果。并寫出利用此函數(shù)查詢9512101學(xué)生的、所修的課程名、課程學(xué)分、考試成績(jī)以及拿到的總學(xué)分的SQL語(yǔ)句。createfunctiondbo.Sum_Credit<snochar<7>>returnsintasbegindeclaresumintsetsum=<selectsum<credit>o=owheresc.sno=snoandgrade>=60>returnsumendselectsname,o課程名,credit課程學(xué)分,grade考試成績(jī),dbo.Sum_Credit<sc.sno>as總學(xué)分o=ojoinstudentsons.sno=sc.snowheresc.sno='0811101'查詢指定系在指定課程<課程號(hào)>的考試平均成績(jī)。createfunctiondbo.Avg_Grade<deptnvarchar<20>,cnochar<6>>returnsintasbegindeclareAvgintselectAvg=avg<grade>fromscjoinstudentsonsc.sno=s.snowheredept=o=cnoreturnAvgendselectdistinctdbo.Avg_Grade<dept,cno>as平均成績(jī)fromscjoinstudentsonsc.sno=s.snowheredept='計(jì)算機(jī)系'o='C001'查詢指定系的男生中選課門數(shù)超過(guò)指定門數(shù)的學(xué)生人數(shù)。createfunctiondbo.Man<deptnvarchar<20>,menshuint>returnsintasbegindeclareNumintselectNum=count<*>from<selectsc.sno,count<o>asbfromstudentsleftjoinscons.sno=sc.snowheredept=deptandsex='男'groupbysc.snohavingcount<o>>menshu>astreturnNumendselectdistinctdbo.Man<dept,0>學(xué)生人數(shù)fromstudentsleftjoinscons.sno=sc.snowheredept='計(jì)算機(jī)系'創(chuàng)建滿足下述要求的用戶自定義聯(lián)表值函數(shù)。查詢選課門數(shù)在指定圍的學(xué)生的、所在系和所選的課程。createfunctiondbo.F_7_1<menshuint>returnstableasreturn<selectsname,dept,o,cnamefromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheres.snoin<selectsnofromscgroupbysnohavingcount<*>=menshu>>select*fromdbo.F_7_1<3>查詢指定系的學(xué)生考試成績(jī)大于等于90的學(xué)生的、所在系、課程名和考試成績(jī)。并寫出利用此函數(shù)查詢計(jì)算機(jī)系學(xué)生考試情況的SQL語(yǔ)句,只列出學(xué)生、課程名和考試成績(jī)。createfunctiondbo.F_7_2<deptchar<20>>returnstableasreturn<selectsname,dept,cname,gradefromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheredept=deptandgrade>=90>selectsname,cname,gradefromdbo.F_7_2<'計(jì)算機(jī)系'>創(chuàng)建滿足下述要求的用戶自定義多語(yǔ)句表值函數(shù)。查詢指定系年齡最大的前2名學(xué)生的和年齡,包括并列的情況。alterfunctiondbo.F_8_1<deptchar<20>>returnsret_F_8_1table<snamechar<10>,ageint>asbegininsertintoret_F_8_1selecttop2WITHTIESsname,year<GETDATE<>>-year<Birthday>agefromstudentwheredept=deptorderbyageDESCreturnendselectsname,agefromdbo.F_8_1<'計(jì)算機(jī)系'>查詢指定學(xué)生〔的考試情況,列出、所在系、修的課程名和考試情況,其中考試情況列的取值為：如果成績(jī)大于等于90,則為"優(yōu)"；如果成績(jī)?cè)?0~89,則為"良好"；如果成績(jī)?cè)?0~79,則為"一般"；如果成績(jī)?cè)?0~69,則為"不太好"；如果成績(jī)小于60,則為"很糟糕"。并寫出利用此函數(shù)查詢勇的考試情況的SQL語(yǔ)句。alterfunctiondbo.F_8_2<snamechar<10>>returnsret_F_8_2table<snamechar<10>,deptchar<20>,cnamechar<20>,GStyechar<6>>asbegininsertintoret_F_8_2selectsname,dept,cname,casewhengrade>=90then'優(yōu)'whengradebetween80and89then'良好'whengradebetween70and79then'一般'whengradebetween60and69then'不太好'whengrade<60then'很糟糕'endfromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheresname=snamereturnendselectsname,dept,cname,gstyefromdbo.F_8_2<'晨'>selectsname,dept,cname,gstyefromdbo.F_8_2<'勇'>創(chuàng)建滿足下述要求的游標(biāo)?！?查詢VB課程的考試情況,并按如下形式顯示結(jié)果數(shù)據(jù):選了VB課程的學(xué)生情況：所在系成績(jī)勇計(jì)算機(jī)系86晨計(jì)算機(jī)系78吳賓信息系75海信息系68print'選了VB課程的學(xué)生情況：'print''print'所在系成績(jī)'declareschar<10>,dchar<20>,gintdeclareC_9_1cursorforselectsname,dept,gradefromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowhereame='VB'openC_9_1fetchnextfromC_9_1intos,d,gwhilefetch_status=0beginprint''prints+d+cast<gaschar<4>>fetchnextfromC_9_1intos,d,gendcloseC_9_1deallocateC_9_1〔2統(tǒng)計(jì)每個(gè)系的男生人數(shù)和女生人數(shù),并按如下形式顯示結(jié)果數(shù)據(jù)。系名性別人數(shù)====================計(jì)算機(jī)系男2計(jì)算機(jī)系女1數(shù)學(xué)系男1數(shù)學(xué)系女1信息系男2信息系女1print'系名性別人數(shù)'print'===================='declaredchar<10>,schar<2>,cintdeclareC_9_2cursorforselectdept,sex,count<*>人數(shù)fromStudentgroupbydept,sexorderbydeptopenC_9_2fetchnextfromC_9_2intod,s,cwhilefetch_status=0beginprint''printd+''+s+''+cast<caschar<4>>fetchnextfromC_9_2intod,s,cendcloseC_9_2deallocateC_9_2〔3列出每個(gè)系的學(xué)生信息,要求首先列出一個(gè)系的系名,然后在該系名下列出本系學(xué)生的和性別；再列出下一個(gè)系名,然后在此系名下再列出該系的學(xué)生和性別；以此類推,直至列出全部系。要求按如下形式顯示結(jié)果數(shù)據(jù):計(jì)算機(jī)系學(xué)生：勇計(jì)算機(jī)系晨計(jì)算機(jī)系王敏計(jì)算機(jī)系=====================數(shù)學(xué)系學(xué)生：錢小平數(shù)學(xué)系王大力數(shù)學(xué)系=====================信息系學(xué)生：立信息系吳賓信息系海信息系=====================declaredeptvarchar<20>,snamechar<10>declareC_9_3cursorforselectdistinctdeptfromstudentopenC_9_3fetchnextfromC_9_3intodeptwhilefetch_status=0beginprintdept+':'declareC_3cursorforselectsname,deptfromstudentwheredept=deptopenC_3fetchnextfromC_3intosname,deptwhilefetch_status=0beginprintsname+deptfetchnextfromC_3intosname,deptendcloseC_3deallocateC_3print'======================'fetchnextfromC_9_3intodeptendcloseC_9_3deallocateC_9_3〔4設(shè)有工作表,結(jié)構(gòu)如下：Job<Jobidchar<4>primarykey,--工作編號(hào)descvarchar<40>,--工作描述lvltinyint>--工作級(jí)別設(shè)此表包含的數(shù)據(jù)如表7-2所示