数据库实验《实验》.doc

《数据库实验《实验》.doc》由会员分享，可在线阅读，更多相关《数据库实验《实验》.doc（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验名称实验6实验地点8-318实验类型设计实验学时1实验日期2018-6-14 撰写注意:版面格式已设置好(不得更改),填入内容即可。一、 实验目得1、 掌握系统数据类型得特点与功能。2、 掌握创建、修改表结构得方法。3、 掌握插入、更新与删除表数据得方法。二、 实验内容1、查询所有班级得期末成绩平均分,并按照平均分降序排序。2、查询教师基本信息与教授课程信息,其中包括未分配课程得教师信息。3、查询160501班级中选修了“韩晋升”老师讲授得课程得学生学号、姓名、课程号与期末成绩。4、查询每门课程得课程号、课程名与选修该课程得学生人数,并按所选人数升序排序。5、查询两门及以上课程得期末成绩超

2、过80分得学生姓名及平均成绩。6、查询入学考试成绩最高得学生学号、姓名与入学成绩。7、查询同时教授c05127号与c05109号课程得教师信息。8、查询至少选修了姓名为“韩吟秋”得学生所选修课程中一门课程得学生学号与姓名。9、查询所有教授c05127号课程得教师信息。10、查询没有被任何学生选修得课程编号、课程名称与学分。11、查询“C语言”课程期末成绩比“电子技术”课程期末成绩高得所有学生得学号与姓名。12.查询所有班级期末平均成绩得最高分,并将其赋值给变量,通过PRINT语句输出。13、使用游标输出学生姓名、选修课程名称与期末考试成绩。14、使用游标统计每个学院教师所开设课程得选修率。15

3、、使用游标计算学生期末成绩得等级,并更新level列。三、 实验环境1. 操作系统:Windows XP2. 开发软件:SQL Server 2008四、 提交文档提交本实验报告(电子版),文件名命名:学号 姓名实验X:XXXXXXX、doc教师将批阅后(有分数)得全体学生实验报告刻入一张光盘存档,保证光盘可读。五、 附:源代码select studentno,AVG(final) as 平均分 from score group by studentno order by AVG(final)2、select * from teacherselect * from studentselect

4、* from courseinsert into course(courseno,cname,ctype,period,credit)values(c05103,高等数学,必修,64,4、0)select * from scoreinsert into score(studentno,courseno,usually,final)values(16122210009,c05103,87、00,82、00)insert into teacher(teacherno,tname,major,prof,department)values(t05001,韩晋升,软件工程,教授,计算机学院)select

5、 * from classinsert into class(classno,classname,department,monitor)values(160501,计算机,计算机学院,张三)select * from teach_classinsert into teach_class(teacherno,classno,courseno)values(t05001,160501,c05103)select * from teacherselect * from courseselect * from scoreselect classno,AVG(final) as 平均分 from stu

6、dent join scoreon student、studentno=score、studentno group by classnoorder by AVG(final) descselect teacher、*,cname from teacher left join teach_classon teacher、teacherno=teach_class、teachernoleft join course on teach_class、classno=course、courseno3、select student、studentno,sname,cname,final from stud

7、entjoin score on student、studentno=score、studentnojoin course on course、courseno=score、coursenowhere score、courseno in(select courseno from teach_class join teacher on teach_class、teacherno=teacher、teachernowhere tname=韩晋升)and classno=0905014、select course、courseno,cname,COUNT(studentno) fromscore j

8、oin course on score、courseno=course、coursenogroup by course、courseno,cnameorder by COUNT(studentno) desc5、select sname,AVG(final) from score join student on score、studentno=student、studentnowhere final=80group by student、studentno,snamehaving COUNT(courseno)=26、select studentno,sname,point from stud

9、ent wherestudentno=(select top 1 studentno from student order by point)7、select teacher、teacherno,tname,major ,prof,department from teacher join teach_class on teacher、teacherno=teach_class、teachernowhere courseno=c051278、select distinct student、studentno,sname from scorejoin student on score、studen

10、tno=student、studentnowhere courseno in(select courseno from score join student onscore、studentno=student、studentnowhere sname=韩吟秋)and sname!=韩吟秋9、select * from teacher where teacherno in(select teacherno from teach_class wherecourseno=c05127)10、select courseno,cname,credit from course where not exis

11、ts(select * from score where score、courseno=course、courseno)11、select student、studentno,sname from score sc1 join studenton (sc1、studentno=student、studentno) join course c1 on(sc1、courseno=c1、courseno)where c1ame=c语言 and exists(select * from score sc2 join course c2 on(sc2、courseno=c2、courseno)where

12、 c2ame=电子技术 and sc1、studentno=sc2、studentnoand sc1、finalsc2、final)12、declare max numeric(6,2)select max=MAX(平均分) from (select classno as 班级号,AVG(final) as 平均分 from scorejoin student on (score、studentno=student、studentno)join course on (course、courseno=score、courseno)where final is not nullgroup by c

13、lassno) tprint 所有班级期末平均成绩得最高分:+cast(max as varchar(6)13、declare sname nchar(8),cname nchar(10),final numeric(6,2)declare sc_cursor cursor forselect sname,cname,finalfrom score join student on(score、studentno=student、studentno)join course on(score、courseno=course、courseno)open sc_cursorfetch next fro

14、m sc_cursor into sname,cname,finalprint 学生姓名 课程名称 期末成绩print -while FETCH_STATUS=0beginprint sname+cname+cast(final as nchar(6)fetch next from sc_cursor into sname,cname,finalendclose sc_cursordeallocate sc_cursor14、declare department nchar(30),num int,avg floatdeclare cur cursor staticfor select dep

15、artment,count(*) as 选修课数 from classwhere class、classno in(select student、classno from student group by classno)group by departmentopen curfetch curinto department,numset avg=num/(select COUNT(*) from class where department=department)print departmentprint avgwhile FETCH_STATUS=0beginfetch next from

16、curinto department,numset avg=num/(select COUNT(*) from class where department=department)print departmentprint avgendclose curdeallocate cur15、declare sname nchar(30),cname nchar(30),final floatdeclare stu cursor staticforselect sname,final,cnamefrom student,score,coursewhere student、studentno=scor

17、e、studentno and course、courseno=score、coursenoopen stufetch stuinto sname,final,cnameif final=90print N优+sname+cnameelse if final=80 and final=70 and final=60 and final70print N及+sname+cnameelse if final=90print N优+sname+cnameelse if final=80 and final=70 and final=60 and final70print N及+sname+cnameelse if final60print N差+sname+cnameendclose studeallocate stu