数据库实验《实验6》

实验名称实验种类

实验6实验地址设计实验学时1实验日期

8-3182018-6-14★撰写注意：版面格式已设置好（不得改正），填入内容即可。一、实验目的掌握系统数据种类的特色和功能。掌握创立、改正表构造的方法。掌握插入、更新和删除表数据的方法。二、实验内容1.查问全部班级的期末成绩均匀分，并依据均匀分降序排序。2.查问教师基本信息和教讲课程信息，此中包含未分派课程的教师信息。3.查问160501班级中选修了“韩荣膺”老师讲解的课程的学生学号、姓名、课程号和期末成绩。4.查问每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序。5.查问两门及以上课程的期末成绩超出80分的学生姓名及均匀成绩。6.查问入学考试成绩最高的学生学号、姓名和入学成绩。7.查问同时教授c05127号和c05109号课程的教师信息。8.查问起码选修了姓名为“韩吟秋”的学生所选修课程中一门课程的学生学号和姓名。9.查问全部教授c05127号课程的教师信息。10.查问没有被任何学生选修的课程编号、课程名称和学分。11.查问“C语言”课程期末成绩比“电子技术”课程期末成绩高的全部学生的学号和姓名。12．查问全部班级期末均匀成绩的最高分，并将其赋值给变量，经过PRINT语句输出。13.使用游标输出学生姓名、选修课程名称和期末考试成绩。14.使用游标统计每个学院教师所开设课程的选修率。15.使用游标计算学生期末成绩的等级，并更新level列。三、实验环境操作系统：WindowsXP开发软件：SQLServer2008四、提交文档提交本实验报告（电子版），文件名命名：学号姓名《实验X：XXXXXXX》.doc教师将批阅后（有分数）的全体学生实验报告刻入一张光盘存档，保证光盘可读。五、附：源代码1.selectstudentno,AVG(final)as均匀分fromscoregroupbystudentnoorderbyAVG(final)2.select*fromteacherselect*fromstudentselect*fromcourseinsertintocourse(courseno,cname,ctype,period,credit)values('c05103','高等数学','必修',64,select*fromscoreinsertintoscore(studentno,courseno,usually,final)values('','c05103',,insertintoteacher(teacherno,tname,major,prof,department)values('t05001','韩荣膺','软件工程','教授','计算机学院')select*fromclassinsertintoclass(classno,classname,department,monitor)values('160501','计算机','计算机学院','张三')select*fromteach_classinsertintoteach_class(teacherno,classno,courseno)values('t05001','160501','c05103')select*fromteacherselect*fromcourseselect*fromscoreselectclassno,AVG(final)as均匀分fromstudentjoinscoreon=groupbyclassnoorderbyAVG(final)descselectteacher.*,cnamefromteacherleftjointeach_classon=leftjoincourseon=3.select,sname,cname,finalfromstudentjoinscoreon=joincourseon=wherein(selectcoursenofromteach_classjointeacheron=wheretname='韩荣膺')andclassno='090501'4.select,cname,COUNT(studentno)fromscorejoincourseon=groupby,cnameorderbyCOUNT(studentno)desc5.selectsname,AVG(final)fromscorejoinstudenton=wherefinal>=80groupby,snamehavingCOUNT(courseno)>=26.selectstudentno,sname,pointfromstudentwherestudentno=(selecttop1studentnofromstudentorderbypoint)7.select,tname,major,prof,departmentfromteacherjointeach_classon=wherecourseno='c05127'8.selectdistinct,snamefromscorejoinstudenton=wherecoursenoin(selectcoursenofromscorejoinstudenton=wheresname='韩吟秋')andsname!='韩吟秋'9.select*fromteacherwhereteachernoin(selectteachernofromteach_classwherecourseno='c05127')10.selectcourseno,cname,creditfromcoursewherenotexists(select*fromscorewhere=11.select,snamefromscoresc1joinstudenton=joincoursec1on=where='c语言'andexists(select*fromscoresc2joincoursec2on=where='电子技术'and=and>)12.declare@maxnumeric(6,2)select@max=MAX(均匀分)from(selectclassnoas班级号,AVG(final)as均匀分fromscorejoinstudenton=joincourseon=wherefinalisnotnullgroupbyclassno)tprint'全部班级期末均匀成绩的最高分:'+cast(@maxasvarchar(6))13.declare@snamenchar(8),@cnamenchar(10),@finalnumeric(6,2)declaresc_cursorcursorforselectsname,cname,finalfromscorejoinstudenton=joincourseon=opensc_cursorfetchnextfromsc_cursorinto@sname,@cname,@finalprint'学生姓名课程名称期末成绩'print'----------------------------------'while@@FETCH_STATUS=0beginprint@sname+@cname+cast(@finalasnchar(6))fetchnextfromsc_cursorinto@sname,@cname,@finalendclosesc_cursordeallocatesc_cursor14.declare@departmentnchar(30),@numint,@avgfloatdeclarecurcursorstaticforselectdepartment,count(*)as'选修课数'fromclasswherein(selectfromstudentgroupbyclassno)groupbydepartmentopencurfetchcurinto@department,@numset@avg=@num/(selectCOUNT(*)fromclasswheredepartment=@department)print@departmentprint@avgwhile@@FETCH_STATUS=0beginfetchnextfromcurinto@department,@numset@avg=@num/(selectCOUNT(*)fromclasswheredepartment=@department)print@departmentprint@avgendclosecurdeallocatecur15.declare@snamenchar(30),@cnamenchar(30),@finalfloatdeclarestucursorstaticforselectsname,final,cnamefromstudent,score,coursewhere=and=openstufetchstuinto@sname,@final,@cnameif@final>=90printN'优'+@sname+@cnameelseif@final>=80and@final<90printN'良'+@sname+@cnameelseif@final>=70and@final<80printN'中'+@sname+@cnameelseif@final>=60and@final<70printN'及'+@sname+@cnameelseif@final<60printN'差'+@sname+@cnamewhile@@FETCH_STATUS=0beginfetchnextfromstuinto@sname,@final,@cnameif@final>=90printN'优'+@sname+@cname