实验七 T-SQL语句高级应用（一）

实验七 T-SQL语句高级应用（一）

 

1、实验目的

（1）掌握多表连接查询、子查询的基本概念

（2）掌握多表连接的各种方法，包括内连接、外连接、交叉连接等

（3）掌握子查询的方法，包括相关子查询和不相关子查询。

2、实验内容

（1）查询所有班级的期末成绩平均分，并按照平均分降序排列

（2）查询教师基本信息和教授课程信息，其中包括未分配课程的教师信息

（3）查询‘090501’班级中选修了‘韩晋升’老师讲授的课程的学生的学号、姓名、课程名和期末成绩

（4）查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序

（5）查询两门及以上课程的期末成绩超过80分的学生的姓名及平均成绩。

（6）查询“C语言”课程期末成绩比“电子技术”课程期末成绩高的所有学生的学号和姓名。

（7）查询所有班级期末平均成绩的最高分，并将其值赋给变量，通过PRINT语句输出。

（8）查询至少选修了姓名为“韩吟秋”的学生所选修课程中一门课的学生的学号和姓名。

--查询所有班级的期末成绩平均分，并按照平均分降序排列
select classno,avg(final) as '平均分'
from student,score 
where student.studentno = score.studentno 
group by student.classno
order by 平均分 desc


--查询教师基本信息和教授课程信息，其中包括未分配课程的教师信息
select teacher.*,course.cname
from teacher,course,teach_class
where teacher.teacherno = teach_class.teacherno and
teach_class.courseno = course.courseno


--查询‘090501’班级中选修了‘韩晋升’老师讲授的课程的学生的学号、姓名、课程名和期末成绩
select student.studentno,student.sname,course.cname,score.final
from student,course,score
where student.studentno  = score.studentno and
course.courseno = score.courseno and
student.classno = '090501' and
course.courseno  in
(
select teach_class.courseno
from teacher,teach_class
where teacher.teacherno = teach_class.teacherno and
teacher.tname = '韩晋升' 
)




 --查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序
select course.courseno,course.cname,count(*) as '学生人数'
from course,student,score
where student.studentno = score.studentno and
course.courseno = score.courseno
group by course.courseno , course.cname
order by 学生人数 asc


--查询两门及以上课程的期末成绩超过80分的学生的姓名及平均成绩。
 select student.sname,avg(final) as '平均成绩'
from student,score
 where final >80 and
student.studentno = score.studentno 
group by student.studentno,student.sname
having count(* ) >= 2


 
 --查询“C语言”课程期末成绩比“电子技术”课程期末成绩高的所有学生的学号和姓名。
select test1.studentno,test1.sname from(
select student.studentno,sname,final
from student,score,course
where student.studentno = score.studentno and
score.courseno = course.courseno and
course.cname = N'C语言'
) as test1,(
select student.studentno,sname,final
from student,score,course
where student.studentno = score.studentno and
score.courseno = course.courseno and
course.cname = N'电子技术'
) as test2
where test1.final > test2.final and test1.studentno = test2.studentno
/*
select student.studentno ,student.sname  ,score.final
into cc
from student,score,course
where student.studentno = score.studentno and
score.courseno = course.courseno and
cname =N'C语言'
select student.studentno ,student.sname  ,score.final
into ca
from student,score,course
where student.studentno = score.studentno and
score.courseno = course.courseno and
cname =N'电子技术'
select cc.studentno ,cc.sname 
from ca,cc
where ca.sname = cc.sname and
cc.final>ca.final
*/
 
--查询所有班级期末平均成绩的最高分，并将其值赋给变量，通过PRINT语句输出。
declare @high float
 
set @high =
(select top 1 tt.high from(
select  avg(final) as 'high'
from student,score,course
where student.studentno = score.studentno and
score.courseno = course.courseno
group by student.classno
)as tt
)
print cast(@high as varchar(30)) 
select @high
--查询至少选修了姓名为“韩吟秋”的学生所选修课程中一门课的学生的学号和姓名。
select student.studentno,sname
from student,teach_class
where student.classno = teach_class.classno and
 student.sname != N'韩吟秋' and
teach_class.classno in
(
select teach_class.classno 
from teach_class,teacher,student
where
teacher.teacherno = teach_class.teacherno and
student.sname = N'韩吟秋' and
student.classno = teach_class.classno
)
group by sname,student.studentno