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

最新推荐文章于 2023-07-19 11:01:32 发布

wangd933

最新推荐文章于 2023-07-19 11:01:32 发布

阅读量682

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/wangd933/article/details/8150635

SQL 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了使用EXCEL表进行数据库表操作与查询的方法，包括多表连接查询、子查询等，具体步骤涵盖班级成绩平均分计算、教师与课程信息查询、特定条件下的学生信息检索、课程选修人数统计、高分学生筛选、课程成绩比较、班级平均成绩最高分查找、特定学生选修课程查询等，旨在提高数据库操作与分析能力。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库表的EXCEL表

1、实验目的

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

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

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

2、实验内容

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

USE database1

select qq.classno,avg(qq.final) as '班级平均分'

from

(select classno ,final

from student join score

on student.studentno=score.studentno)qq

group by qq.classno

order by '班级平均分' desc

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

USE database1

select *

from

(select teacher.*,teach_class.courseno

from teacher left join teach_class

on teacher.teacherno=teach_class.teacherno)

teacher_course left join course

on teacher_course.courseno=course.courseno

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

USE database1

select *

from student join

(select studentno

from score where score.courseno=

(select teach_class.courseno

from teach_class

where teach_class.teacherno=(select teacherno

from teacher

where teacher.tname='韩晋升')and teach_class.classno='090501'))qq

on student.studentno=qq.studentno

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

use database1

select qq.courseno,qq.cname,count(qq.courseno) as '选择该门课程的学生人数'

from

(select course.courseno,course.cname

from course join score

on course.courseno=score.courseno) as qq

group by qq.courseno,qq.cname

order by '选择该门课程的学生人数'

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

use database1

select qq.studentno,student.sname,qq.ww as '平均成绩'

from

(select score.studentno,avg(score.final) as ww

from score

where score.final>80

group by score.studentno

having count(score.studentno)>1)as qq join student

on qq.studentno=student.studentno

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

USE database1

declare @aa nvarchar(255)

declare @bb nvarchar(255)

set @aa=(select course.courseno from course where course.cname in ('C语言'))

set @bb=(select course.courseno from course where course.cname in ('电子技术'))

select student.studentno,student.sname from student where student.studentno in

(select b.studentno from

(select score.studentno,score.final from score where score.courseno=@aa)as b join

(select score.studentno,score.final from score where score.courseno=@bb)as c on b.studentno=c.studentno

and b.final>c.final)

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

USE database1

declare @aa float

set @aa=(select max(rr.ww) from

(

select avg(qq.final) as ww

from

(select classno,final

from student join score

on student.studentno=score.studentno

)as qq

group by qq.classno)

as rr)

print CAST(@aa AS varchar(12))

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

USE database1

select student.studentno,student.sname from student where student.studentno in(

select score.studentno from score where score.courseno in

(select score.courseno from score where

score.studentno=(select student.studentno from student where student.sname='韩吟秋')

)and score.studentno!=(select student.studentno from student where student.sname='韩吟秋'))

1、实验目的

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

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

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

2、实验内容

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

USE database1

select qq.classno,avg(qq.final) as '班级平均分'

from

(select classno ,final

from student join score

on student.studentno=score.studentno)qq

group by qq.classno

order by '班级平均分' desc

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

USE database1

select *

from

(select teacher.*,teach_class.courseno

from teacher left join teach_class

on teacher.teacherno=teach_class.teacherno)

teacher_course left join course

on teacher_course.courseno=course.courseno

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

USE database1

select *

from student join

(select studentno

from score where score.courseno=

(select teach_class.courseno

from teach_class

where teach_class.teacherno=(select teacherno

from teacher

where teacher.tname='韩晋升')and teach_class.classno='090501'))qq

on student.studentno=qq.studentno

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

use database1

select qq.courseno,qq.cname,count(qq.courseno) as '选择该门课程的学生人数'

from

(select course.courseno,course.cname

from course join score

on course.courseno=score.courseno) as qq

group by qq.courseno,qq.cname

order by '选择该门课程的学生人数'

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

use database1

select qq.studentno,student.sname,qq.ww as '平均成绩'

from

(select score.studentno,avg(score.final) as ww

from score

where score.final>80

group by score.studentno

having count(score.studentno)>1)as qq join student

on qq.studentno=student.studentno

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

USE database1

declare @aa nvarchar(255)

declare @bb nvarchar(255)

set @aa=(select course.courseno from course where course.cname in ('C语言'))

set @bb=(select course.courseno from course where course.cname in ('电子技术'))

select student.studentno,student.sname from student where student.studentno in

(select b.studentno from

(select score.studentno,score.final from score where score.courseno=@aa)as b join

(select score.studentno,score.final from score where score.courseno=@bb)as c on b.studentno=c.studentno

and b.final>c.final)

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

USE database1

declare @aa float

set @aa=(select max(rr.ww) from

(

select avg(qq.final) as ww

from

(select classno,final

from student join score

on student.studentno=score.studentno

)as qq

group by qq.classno)

as rr)

print CAST(@aa AS varchar(12))

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

USE database1

select student.studentno,student.sname from student where student.studentno in(

select score.studentno from score where score.courseno in

(select score.courseno from score where

score.studentno=(select student.studentno from student where student.sname='韩吟秋')

)and score.studentno!=(select student.studentno from student where student.sname='韩吟秋'))