计科09-数据库技术实践-第三部分

./实验报告课程名称数据库技术实践实验项目存储过程、触发器、用户自定义函数与游标实验仪器SQLServer2008系别____计算机科学与技术系_专业____计算机科学与技术____班级/学号_______________________学生_______________________实验日期__________成绩_______________________指导教师_________________[在容说明部分请总体说明在本部分实践过程中,具体都完成了哪些容]一．容说明[请按照下面练习题的要求,完成各项容,并说明每个题目完成的情况,是否存在问题,如何解决等]二.实验步骤与容如无特别说明,以下各题均利用之前建立的Students数据库以及Student、Course和SC表实现。创建满足下述要求的存储过程,并查看存储过程的执行结果。查询每个学生的修课总学分,要求列出学生学号及总学分。createprocSumCreditasselectsno学号,sum<credit>总学分o=ogroupbysnogoexecSumCredit查询学生的学号、、修的课程号、课程名、课程学分,将学生所在系作为输入参数,默认值为"计算机系"。执行此存储过程,并分别指定一些不同的输入参数值,查看执行结果。createprocStudentInformationdeptvarchar<50>='计算机系',snamechar<50>asselectsc.sno学号,sname,o课程号,cname课程名,credit学分fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheredept=deptandSname=snamegoexecStudentInformation'信息管理系','吴宾'execStudentInformationsname='勇'查询指定系的男生人数,其中系为输入参数,人数为输出参数。createprocMan_Numdeptvarchar<50>,rsintoutputasselectrs=COUNT<*>fromStudentwhereDept=deptandSex='男'godeclarersintexecMan_Num'信息管理系',rsoutputselectrsas人数查询考试平均成绩超过指定分值的学生学号和平均成绩。createprocAvgGradegradeintasselectsno,AVG<grade>as'平均成绩'fromSCgroupbySnohavingAVG<grade>>gradegoexecAvgGrade60查询查询指定系的学生中,选课门数最多的学生的选课门数和平均成绩,要求系为输入参数,选课门数和平均成绩用输出参数返回。createprocChoose_Coursedeptvarchar<50>,rsintoutput,avgintoutputasselecttop1rs=COUNT<*>,avg=avg<grade>fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cnowheredept=deptgroupbysc.Snogodeclarersint,avgintexecChoose_Course'信息管理系',rsoutput,avgoutputselectrsas选课门数,avgas平均成绩删除指定学生的修课记录,其中学号为输入参数。createprocDel_Coursesnochar<50>asdeletefromSCwhereSno=snogoexecDel_Course'0831102'修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期,开课学期的默认值为2。如果指定的开课学期不在1~8围,则不进行修改。利用SSMS工具查看在students数据库中创建的全部存储过程。Students=>可编程性=>存储过程修改第1题〔1的存储过程,使之能够查询指定系中,每个学生选课总门数、总学分和考试平均成绩。alterprocSumCreditdeptnvarchar<20>asselectCOUNT<SC.Cno>总门数,sum<credit>总学分,AVG<Grade>平均成绩fromstudentsleftjoinscons.sno=o=owheredept=deptgroupbysc.SnogoexecSumCredit'信息管理系'创建满足下述要求的触发器〔前触发器、后触发器均可,并验证触发器执行情况。限制每个学期开设的课程总学分在20~30围。altertriggerTR_SumGradeoncourseafterinsertasdeclaresint,xint,yintsets=<selectsum<Credit>fromcoursewheresemesterin<selectsemesterfrominserted>>if<20<sands<30>beginprint'课程总学分没有超出围!!!'printsendelsebeginprint'课程总学分超出围!!!'print's='printsrollbackendinsertintocoursevalues<'C010','汇编语言',200,1>限制每个学生每学期选课门数不能超过5门〔设只针对插入操作。ALTERtriggerTR_MENonscafterinsertasdeclarexintsetx=<selectcount<*>o=owheresemesterin<selectsemesterfromcoursewhereoin<selectofrominserted>>andsc.snoin<selectsnofrominserted>>if<x>5>beginselect*o=oselect*frominsertedprintxprint'选课门数超过门'rollbackend在Students数据库建立如下所示的工作表和职工表CREATETABLE工作表<工作号CHAR<8>PRIMARYKEY,最低工资SMALLINT,最高工资SMALLINT>CREATETABLE职工表<职工号CHAR<7>PRIMARYKEY,职工名CHAR<10>NOTNULL,工作号CHAR<8>REFERENCES工作表<工作号>,基本工资SMALLINT,浮动工资SMALLINT>利用这两表建立满足如下要求的触发器。限制职工的基本工资和浮动工资之和必须大于等于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创建满足下述要求的用户自定义标量函数。查询指定学生已经得到的修课总学分〔考试及格的课程才能拿到学分,学号为输入参数,总学分为函数返回结果。并写出利用此函数查询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'查询指定系的男生中选课门数超过指定门数的学生人数。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>学生人数fromstudentsleftjoinscons.sno=sc.snowheredept='计算机系'创建满足下述要求的用户自定义联表值函数。查询选课门数在指定围的学生的、所在系和所选的课程。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的学生的、所在系、课程名和考试成绩。并写出利用此函数查询计算机系学生考试情况的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<'计算机系'>创建满足下述要求的用户自定义多语句表值函数。查询指定系年龄最大的前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,则为"优"；如果成绩在80~89,则为"良好"；如果成绩在70~79,则为"一般"；如果成绩在60~69,则为"不太好"；如果成绩小于60,则为"很糟糕"。并写出利用此函数查询勇的考试情况的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'优'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<'勇'>创建满足下述要求的游标。〔1查询VB课程的考试情况,并按如下形式显示结果数据:选了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统计每个系的男生人数和女生人数,并按如下形式显示结果数据。系名性别人数====================计算机系男2计算机系女1数学系男1数学系女1信息系男2信息系女1print'系名性别人数'print'===================='declaredchar<10>,schar<2>,cintdeclareC_9_2cursorforselectdept,sex,count<*>人数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列出每个系的学生信息,要求首先列出一个系的系名,然后在该系名下列出本系学生的和性别；再列出下一个系名,然后在此系名下再列出该系的学生和性别；以此类推,直至列出全部系。要求按如下形式显示结果数据:计算机系学生：勇计算机系晨计算机系王敏计算机系=====================数学系学生：钱小平数学系王大力数学系=====================信息系学生：立信息系吴宾信息系海信息系=====================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设有工作表,结构如下：Job<Jobidchar<4>primarykey,--工作编号descvarchar<40>,--工作描述lvltinyint>--工作级别设此表包含的数据如表7-2所示