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

./實驗報告課程名稱數(shù)據(jù)庫技術(shù)實踐實驗項目存儲過程、觸發(fā)器、用戶自定義函數(shù)與游標實驗儀器SQLServer2008系別____計算機科學與技術(shù)系_專業(yè)____計算機科學與技術(shù)____班級/學號_______________________學生_______________________實驗日期__________成績_______________________指導教師_________________[在容說明部分請總體說明在本部分實踐過程中,具體都完成了哪些容]一．容說明[請按照下面練習題的要求,完成各項容,并說明每個題目完成的情況,是否存在問題,如何解決等]二.實驗步驟與容如無特別說明,以下各題均利用之前建立的Students數(shù)據(jù)庫以及Student、Course和SC表實現(xiàn)。創(chuàng)建滿足下述要求的存儲過程,并查看存儲過程的執(zhí)行結(jié)果。查詢每個學生的修課總學分,要求列出學生學號及總學分。createprocSumCreditasselectsno學號,sum<credit>總學分o=ogroupbysnogoexecSumCredit查詢學生的學號、、修的課程號、課程名、課程學分,將學生所在系作為輸入?yún)?shù),默認值為"計算機系"。執(zhí)行此存儲過程,并分別指定一些不同的輸入?yún)?shù)值,查看執(zhí)行結(jié)果。createprocStudentInformationdeptvarchar<50>='計算機系',snamechar<50>asselectsc.sno學號,sname,o課程號,cname課程名,credit學分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ù)查詢考試平均成績超過指定分值的學生學號和平均成績。createprocAvgGradegradeintasselectsno,AVG<grade>as'平均成績'fromSCgroupbySnohavingAVG<grade>>gradegoexecAvgGrade60查詢查詢指定系的學生中,選課門數(shù)最多的學生的選課門數(shù)和平均成績,要求系為輸入?yún)?shù),選課門數(shù)和平均成績用輸出參數(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平均成績刪除指定學生的修課記錄,其中學號為輸入?yún)?shù)。createprocDel_Coursesnochar<50>asdeletefromSCwhereSno=snogoexecDel_Course'0831102'修改指定課程的開課學期。輸入?yún)?shù)為：課程號和修改后的開課學期,開課學期的默認值為2。如果指定的開課學期不在1~8圍,則不進行修改。利用SSMS工具查看在students數(shù)據(jù)庫中創(chuàng)建的全部存儲過程。Students=>可編程性=>存儲過程修改第1題〔1的存儲過程,使之能夠查詢指定系中,每個學生選課總門數(shù)、總學分和考試平均成績。alterprocSumCreditdeptnvarchar<20>asselectCOUNT<SC.Cno>總門數(shù),sum<credit>總學分,AVG<Grade>平均成績fromstudentsleftjoinscons.sno=o=owheredept=deptgroupbysc.SnogoexecSumCredit'信息管理系'創(chuàng)建滿足下述要求的觸發(fā)器〔前觸發(fā)器、后觸發(fā)器均可,并驗證觸發(fā)器執(zhí)行情況。限制每個學期開設(shè)的課程總學分在20~30圍。altertriggerTR_SumGradeoncourseafterinsertasdeclaresint,xint,yintsets=<selectsum<Credit>fromcoursewheresemesterin<selectsemesterfrominserted>>if<20<sands<30>beginprint'課程總學分沒有超出圍!!!'printsendelsebeginprint'課程總學分超出圍!!!'print's='printsrollbackendinsertintocoursevalues<'C010','匯編語言',200,1>限制每個學生每學期選課門數(shù)不能超過5門〔設(shè)只針對插入操作。ALTERtriggerTR_MENonscafterinsertasdeclarexintsetx=<selectcount<*>o=owheresemesterin<selectsemesterfromcoursewhereoin<selectofrominserted>>andsc.snoin<selectsnofrominserted>>if<x>5>beginselect*o=oselect*frominsertedprintxprint'選課門數(shù)超過門'rollbackend在Students數(shù)據(jù)庫建立如下所示的工作表和職工表CREATETABLE工作表<工作號CHAR<8>PRIMARYKEY,最低工資SMALLINT,最高工資SMALLINT>CREATETABLE職工表<職工號CHAR<7>PRIMARYKEY,職工名CHAR<10>NOTNULL,工作號CHAR<8>REFERENCES工作表<工作號>,基本工資SMALLINT,浮動工資SMALLINT>利用這兩表建立滿足如下要求的觸發(fā)器。限制職工的基本工資和浮動工資之和必須大于等于2000。createtriggerTR_Salaryon職工表afterinsert,updateasdeclarexSMALLINT,ySMALLINT,zSMALLINTsetx=<select基本工資from職工表where職工號in<select職工號frominserted>>sety=<select浮動工資from職工表where職工號in<select職工號frominserted>>setz=x+yif<z>=2000>beginprint'操作符合要求'endelsebeginprintxprintyprintzprint'請注意,職工的基本工資和浮動工資之和小于?。。?！'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工作號in<select工作號frominserted>>sety=<select最高工資from工作表where工作號in<select工作號frominserted>>setz=y/xif<z>=1.5>beginprint'操作符合要求'endelsebeginprintxprintyprintzselect*from工作表select*frominsertedprint'請注意,最高工資低于最低工資的.5倍'rollbackendinsertinto工作表values<'G002',1000,1000>限制不能刪除基本工資低于1500的職工。altertriggerTR_Salary2on職工表afterdeleteasifexists<select*from職工表where基本工資<1500>beginprint'操作符合要求'endelsebeginprint'不能刪除基本工資低于的職工'select*from職工表select*fromdeletedrollbackend創(chuàng)建滿足下述要求的用戶自定義標量函數(shù)。查詢指定學生已經(jīng)得到的修課總學分〔考試及格的課程才能拿到學分,學號為輸入?yún)?shù),總學分為函數(shù)返回結(jié)果。并寫出利用此函數(shù)查詢9512101學生的、所修的課程名、課程學分、考試成績以及拿到的總學分的SQL語句。createfunctiondbo.Sum_Credit<snochar<7>>returnsintasbegindeclaresumintsetsum=<selectsum<credit>o=owheresc.sno=snoandgrade>=60>returnsumendselectsname,o課程名,credit課程學分,grade考試成績,dbo.Sum_Credit<sc.sno>as總學分o=ojoinstudentsons.sno=sc.snowheresc.sno='0811101'查詢指定系在指定課程<課程號>的考試平均成績。createfunctiondbo.Avg_Grade<deptnvarchar<20>,cnochar<6>>returnsintasbegindeclareAvgintselectAvg=avg<grade>fromscjoinstudentsonsc.sno=s.snowheredept=o=cnoreturnAvgendselectdistinctdbo.Avg_Grade<dept,cno>as平均成績fromscjoinstudentsonsc.sno=s.snowheredept='計算機系'o='C001'查詢指定系的男生中選課門數(shù)超過指定門數(shù)的學生人數(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>學生人數(shù)fromstudentsleftjoinscons.sno=sc.snowheredept='計算機系'創(chuàng)建滿足下述要求的用戶自定義聯(lián)表值函數(shù)。查詢選課門數(shù)在指定圍的學生的、所在系和所選的課程。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>查詢指定系的學生考試成績大于等于90的學生的、所在系、課程名和考試成績。并寫出利用此函數(shù)查詢計算機系學生考試情況的SQL語句,只列出學生、課程名和考試成績。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<'計算機系'>創(chuàng)建滿足下述要求的用戶自定義多語句表值函數(shù)。查詢指定系年齡最大的前2名學生的和年齡,包括并列的情況。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<'計算機系'>查詢指定學生〔的考試情況,列出、所在系、修的課程名和考試情況,其中考試情況列的取值為：如果成績大于等于90,則為"優(yōu)"；如果成績在80~89,則為"良好"；如果成績在70~79,則為"一般"；如果成績在60~69,則為"不太好"；如果成績小于60,則為"很糟糕"。并寫出利用此函數(shù)查詢勇的考試情況的SQL語句。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)建滿足下述要求的游標?！?查詢VB課程的考試情況,并按如下形式顯示結(jié)果數(shù)據(jù):選了VB課程的學生情況：所在系成績勇計算機系86晨計算機系78吳賓信息系75海信息系68print'選了VB課程的學生情況：'print''print'所在系成績'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)計每個系的男生人數(shù)和女生人數(shù),并按如下形式顯示結(jié)果數(shù)據(jù)。系名性別人數(shù)====================計算機系男2計算機系女1數(shù)學系男1數(shù)學系女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列出每個系的學生信息,要求首先列出一個系的系名,然后在該系名下列出本系學生的和性別；再列出下一個系名,然后在此系名下再列出該系的學生和性別；以此類推,直至列出全部系。要求按如下形式顯示結(jié)果數(shù)據(jù):計算機系學生：勇計算機系晨計算機系王敏計算機系=====================數(shù)學系學生：錢小平數(shù)學系王大力數(shù)學系=====================信息系學生：立信息系吳賓信息系海信息系=====================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,--工作編號descvarchar<40>,--工作描述lvltinyint>--工作級別設(shè)此表包含的數(shù)據(jù)如表7-2所示