數(shù)據(jù)庫實(shí)驗(yàn)《實(shí)驗(yàn)6》

1、；實(shí)驗(yàn)名稱實(shí)驗(yàn)6實(shí)驗(yàn)地點(diǎn)8-318實(shí)驗(yàn)類型設(shè)計(jì)實(shí)驗(yàn)學(xué)時(shí)1實(shí)驗(yàn)日期2018-6-14 撰寫注意：版面格式已設(shè)置好（不得更改），填入內(nèi)容即可。一、 實(shí)驗(yàn)?zāi)康?. 掌握系統(tǒng)數(shù)據(jù)類型的特點(diǎn)和功能。2. 掌握創(chuàng)建、修改表結(jié)構(gòu)的方法。3. 掌握插入、更新和刪除表數(shù)據(jù)的方法。二、 實(shí)驗(yàn)內(nèi)容1.查詢所有班級(jí)的期末成績平均分，并按照平均分降序排序。2.查詢教師基本信息和教授課程信息，其中包括未分配課程的教師信息。3.查詢160501班級(jí)中選修了“韓晉升”老師講授的課程的學(xué)生學(xué)號(hào)、姓名、課程號(hào)和期末成績。4.查詢每門課程的課程號(hào)、課程名和選修該課程的學(xué)生人數(shù)，并按所選人數(shù)升序排序。5.查詢兩門及以上課程的期末成績

2、超過80分的學(xué)生姓名及平均成績。6.查詢?nèi)雽W(xué)考試成績最高的學(xué)生學(xué)號(hào)、姓名和入學(xué)成績。7.查詢同時(shí)教授c05127號(hào)和c05109號(hào)課程的教師信息。8.查詢至少選修了姓名為“韓吟秋”的學(xué)生所選修課程中一門課程的學(xué)生學(xué)號(hào)和姓名。9.查詢所有教授c05127號(hào)課程的教師信息。10.查詢沒有被任何學(xué)生選修的課程編號(hào)、課程名稱和學(xué)分。11.查詢“C語言”課程期末成績比“電子技術(shù)”課程期末成績高的所有學(xué)生的學(xué)號(hào)和姓名。12查詢所有班級(jí)期末平均成績的最高分，并將其賦值給變量，通過PRINT語句輸出。13.使用游標(biāo)輸出學(xué)生姓名、選修課程名稱和期末考試成績。14.使用游標(biāo)統(tǒng)計(jì)每個(gè)學(xué)院教師所開設(shè)課程的選修率。15

3、.使用游標(biāo)計(jì)算學(xué)生期末成績的等級(jí)，并更新level列。三、 實(shí)驗(yàn)環(huán)境1. 操作系統(tǒng)：Windows XP2. 開發(fā)軟件：SQL Server 2008四、 提交文檔提交本實(shí)驗(yàn)報(bào)告（電子版），文件名命名：學(xué)號(hào) 姓名實(shí)驗(yàn)X：XXXXXXX.doc教師將批閱后（有分?jǐn)?shù)）的全體學(xué)生實(shí)驗(yàn)報(bào)告刻入一張光盤存檔，保證光盤可讀。五、 附：源代碼1.select studentno,AVG(final) as 平均分 from score group by studentno order by AVG(final)2.select * from teacherselect * from studentselec

4、t * from courseinsert into course(courseno,cname,ctype,period,credit)values('c05103','高等數(shù)學(xué)','必修',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,p

5、rof,department)values('t05001','韓晉升','軟件工程','教授','計(jì)算機(jī)學(xué)院')select * from classinsert into class(classno,classname,department,monitor)values('160501','計(jì)算機(jī)','計(jì)算機(jī)學(xué)院','張三')select * from teach_classinsert into teach_class(teacherno,cla

6、ssno,courseno)values('t05001','160501','c05103')select * from teacherselect * from courseselect * from scoreselect classno,AVG(final) as 平均分 from student join scoreon student.studentno=score.studentno group by classnoorder by AVG(final) descselect teacher.*,cname from teacher

7、 left join teach_classon teacher.teacherno=teach_class.teachernoleft join course on teach_class.classno=course.courseno3.select student.studentno,sname,cname,final from studentjoin score on student.studentno=score.studentnojoin course on course.courseno=score.coursenowhere score.courseno in(select c

8、ourseno from teach_class join teacher on teach_class.teacherno=teacher.teachernowhere tname='韓晉升')and classno='090501'4.select course.courseno,cname,COUNT(studentno) fromscore join course on score.courseno=course.coursenogroup by course.courseno,cnameorder by COUNT(studentno) desc5.s

9、elect 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 student wherestudentno=(select top 1 studentno from student order by point)7.select teacher.teacherno,t

10、name,major ,prof,department from teacher join teach_class on teacher.teacherno=teach_class.teachernowhere courseno='c05127'8.select distinct student.studentno,sname from scorejoin student on score.studentno=student.studentnowhere courseno in(select courseno from score join student onscore.st

11、udentno=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 exists(select * from score where score.courseno=course.courseno

12、)11.select student.studentno,sname from score sc1 join studenton (sc1.studentno=student.studentno) join course c1 on(sc1.courseno=c1.courseno)where ame='c語言' and exists(select * from score sc2 join course c2 on(sc2.courseno=c2.courseno)where ame='電子技術(shù)' and sc1.studentno=sc2.studentno

13、and sc1.final>sc2.final)12.declare max numeric(6,2)select max=MAX(平均分) from (select classno as 班級(jí)號(hào),AVG(final) as 平均分 from scorejoin student on (score.studentno=student.studentno)join course on (course.courseno=score.courseno)where final is not nullgroup by classno) tprint '所有班級(jí)期末平均成績的最高分:'

14、;+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 from sc_cursor into sname,cname,

15、finalprint '學(xué)生姓名 課程名稱 期末成績'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 department,c

16、ount(*) as '選修課數(shù)' 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

17、 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=sco

18、re.studentno and course.courseno=score.coursenoopen stufetch stuinto sname,final,cnameif final>=90print N'優(yōu)'+sname+cnameelse if final>=80 and final<90print N'良'+sname+cnameelse if final>=70 and final<80print N'中'+sname+cnameelse if final>=60 and final<70print N'及'+sname+cnameelse if final<60print N'差'+sname+cnamewhile FETCH_STATUS=0beginfetch next from stuinto sname,final,cnameif final>=90print N'