多表查询----实验三 SQL语句应用

最新推荐文章于 2023-04-23 00:00:00 发布

X__太瘦

最新推荐文章于 2023-04-23 00:00:00 发布

阅读量7.8k

点赞数 8

分类专栏：数据库实验

本文链接：https://blog.csdn.net/Adavinajh/article/details/92759454

版权

数据库实验专栏收录该内容

4 篇文章 4 订阅

订阅专栏

一. 实验目的

进一步熟悉掌握SQL Server 查询分析器环境。
掌握多表查询的两种方法：连接查询、嵌套查询。
掌握合并查询的方法。

二. 实验内容

连接查询的两种连接方法：同一查询要求，考虑两种方法实现。
通过实验体会外连接的实现：左外连接、右外连接、全外连接。
涉及自身连接的查询。
涉及三表（多表）的连接查询。
嵌套查询中不相关子查询、相关子查询的应用。
引出子查询的谓词IN、ANY、ALL的应用。
合并查询：并操作(UNION)。
存储查询结果到表中：SELECT…INTO 语句的应用。

三.实验步骤

在实验一、二的基础上，运用SQL语句实现以下查询要求：

1. 查询所有同学的选课及成绩情况，要求显示学生的学号s_no、姓名s_name、

课程号course_no和课程的成绩score。

select Student.s_no,s_name,course_no,score
from Student,Choice
where (Student.s_no=Choice.s_no)

2.查询所有同学的选课及成绩情况，要求显示学生的姓名s_name、课程名

称course_ name、课程的成绩score，并将查询结果存放到一个新的数据

表new_table中。（SELECT…INTO）

select s_name,course_name,score
into new_table
from Student,Choice,Course
where (Student.s_no=Choice.s_no)
and (Course.course_no=Choice.course_no)

3.查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号

s_no、姓名s_name、课程号course_no、课程名称course_name、课程的

成绩score。

select Student.s_no,s_name,Course.course_no,Course_name,score
from Student,Course,Choice,Class
where (Student.s_no=Choice.s_no)
and (Course.course_no=Choice.course_no)
and Class.class_no=Student.class_no
and class_name='计算机99-1'

4.查询所有同学的学分情况（假设课程成绩≥60分时可获得该门课程的学

分），要求显示学生的学号s_no、姓名s_name、总学分（将该列定名为：

total_score）。（用JOIN）

select Student.s_no,s_name,sum(score) as total_score
from Student join Choice
on Student.s_no=Choice.s_no
where score>=60
group by Student.s_no,s_name

5.查询所有同学的平均成绩及选课门数，要求显示学生的学号s_no、姓名

s_name、平均成绩（将该列定名为average_score）、选课的门数（将该

列定名为：choice_num）。

select Student.s_no,s_name,AVG(score) as average_score,COUNT(*) as choice_num
from Student,Choice
where Student.s_no=Choice.s_no
group by Student.s_no,s_name

6.查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学

生的学号s_no、姓名s_name、课程号course_no、课程名称course_name。

select Student.s_no,s_name,Course.course_no,course_name
from Student join Choice
on Student.s_no=Choice.s_no
join Course
on Choice.course_no=Course.course_no
where Choice.course_no is not null and score is null

7.查询所有选修了课程但考试不及格（假设＜60分为不及格）的所有同学

及相应的课程，要求显示学生的学号s_no、姓名s_name、课程号course_no、

课程名称course_name、学分course_score。

select Student.s_no,s_name,Course.course_no,course_name,course_score
from Student join Choice 
on Student.s_no=Choice.s_no
join Course
on Course.course_no=Choice.course_no
where score<60

8.查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示

学生的姓名s_name、课程的成绩score。（使用ANY）

select s_name,score
from Student join Choice
on Student.s_no=Choice.s_no
join Course
on Course.course_no=Choice.course_no
where Course.course_name  = '程序设计语言'

9.查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_no、

姓名s_name、班级名称class_name、课程号course_no、课程名称

course_name、课程的成绩score。

select Student.s_no,s_name,class_name,Course.course_no,course_name,score
from Student,Class,Course,Choice
where (Course.course_no=Choice.course_no)
and (Class.class_no=Student.class_no)
and (Student.s_no=Choice.s_no)
and class_dept='计算机系'

10.查询所有教师的任课情况，要求显示教师姓名t_name、担任课程的名称

course_name。

/*连接查询*/
select t_name,course_name
from Teacher join Teaching
on Teacher.t_no=Teaching.t_no
join Course
on Course.course_no=Teaching.course_no
/*子循环嵌套查询*/
select t_name,course_name
from Teacher,Course
where course_name=(select course_name
                   from Teaching,Course
                   where Teaching.course_no=Course.course_no)

11.查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数

（将该列定名为course_number）。

select t_name,COUNT(*) as course_number
from Teacher,Teaching
where Teacher.t_no=Teaching.t_no
group by Teaching.t_no,t_name

12.查询和“王蕾”是同一班级的同学的姓名。（使用子查询）

select s_name
from Student
where class_no=(select class_no
				from Student
				where s_name='王蕾'
				)

13.查询没有选修“计算机基础”课程的学生姓名。（使用子查询）

select s_name
from Student
where s_no not in(select s_no,course_name
                  from Student,Course
			      where course_name=(select course_name
			                         from Course
							         where course_name='计算机基础')
		    )

14.查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用

UNION）

select distinct teacher.t_name --100
from Teacher join Teaching
on Teacher.t_no=Teaching.t_no
join Course
on Course.course_no=Teaching.course_no
where course_name='数据库原理与应用'
union
select distinct teacher.t_name 
from Teacher join Teaching
on Teacher.t_no=Teaching.t_no
join Course
on Course.course_no=Teaching.course_no
where course_name='数据结构'

15.查询讲授了“数据库原理与应用”课程的教师的姓名。

select distinct teacher.t_name 
from Teacher join Teaching
on Teacher.t_no=Teaching.t_no
join Course
on Course.course_no=Teaching.course_no
where course_name='数据库原理与应用'

要求：至少有一个题目，使用连接查询、嵌套查询两种方法来实现。

附加题：10.查询所有教师的任课情况，要求显示教师姓名t_name、担任课程的名称course_name。

/*内连接:系统会自动忽略两个表中对应不起来的数据*/
select t_name,course_name
from Teacher inner join Teaching
on Teacher.t_no=Teaching.t_no
inner join Course
on Course.course_no=Teaching.course_no
/*左外连接：显示left左边表中的所有数据，右边表数据少了补null值*/
select t_name,course_name
from Teacher left join Teaching
on Teacher.t_no=Teaching.t_no
left join Course
on Course.course_no=Teaching.course_no
/*右外连接： 显示right右边表中所有的数据，左边表少了补null值*/
select t_name,course_name
from Teacher right join Teaching
on Teacher.t_no=Teaching.t_no
right join Course
on Course.course_no=Teaching.course_no

四.根据上课讲的例题，自己给出10个查询语句，包括实验内容中要求的知识点。

注意：每个均要写出查询要求和相应的SQL语句。

1.查询所有老师的授课情况，要求显示老师的教师号t_no、教师姓名t_name、教师职称t_title、所授课程号course_no

select Teacher.t_no,t_name,t_title,course_no
from Teacher,Teaching
where Teacher.t_no=Teaching.t_no

2.查询所有老师的选课情况，要求显示老师的姓名t_name、教师号t_no、所授课程号course_ no，并将查询结果存放到一个新的数据表Teach中。（SELECT…INTO）

select t_name,Teacher.t_no,course_no
into Teach
from Teacher,Teaching
where Teacher.t_no=Teaching.t_no

3.查询班号为“js0002”的同学的选课情况，要求显示学生的学号s_no、课程号course_no、系别class_dept、课程名称course_name

select Student.s_no,Choice.course_no,class_dept,course_name
from Student,Choice,Class,Course
where Student.s_no=Choice.s_no
and Student.class_no=Class.class_no
and Student.s_no=Choice.s_no
and Class.class_no='js0002'
/*group by Student.s_no,Choice.course_no,Class.class_dept,Class.class_name*/

4.查询所有同学的成绩情况（假设课程成绩≥60分时及格），要求显示学生的学号s_no、姓名s_name、总成绩（将该列定名为：sum_score）。（用JOIN）

select Student.s_no,s_name,SUM(score) as sum_score
from Student join Choice
on Student.s_no=Choice.s_no
where score>=60
group by Student.s_no,s_name

5.查询所有选修了课程且考试及格（假设＜60分为不及格）的所有同学及相应的课程，
要求显示学生的学号s_no、课程号course_no、学分course_score。

select Student.s_no,Course.course_no,course_score
from Student join Choice 
on Student.s_no=Choice.s_no
join Course
on Course.course_no=Choice.course_no
where score>60

6.查询和“藩桃芝”是同一系别的同学的姓名。（使用子查询）

select s_name,class_dept
from Student,Class
where class_dept=(select class_dept,s_name
                  from Class join Student
				  on Class.class_no=Student.class_no
				  where s_name='藩桃芝')

7.查询讲授了“计算机基础”课程的教师的姓名。

select distinct Teacher.t_name 
from Teacher join Teaching
on Teacher.t_no=Teaching.t_no
join Course
on Course.course_no=Teaching.course_no
where course_name='计算机基础'

8.查询“李建国”的选修的课程数，要求显示学号s_no、选修的课程门数（将该列定名为course_number）。

select Student.s_no,COUNT(*) as course_number
from Student,Choice
where Student.s_no=Choice.s_no
and s_name='李建国'
group by Student.s_no

9.查询选修了课程号为“01002”但未参加考试的所有同学，要求显示学生的学号s_no、姓名s_name、
课程号course_no、课程名称course_name

select Student.s_no,s_name,Course.course_no,course_name
from Student join Choice
on Student.s_no=Choice.s_no
join Course
on Choice.course_no=Course.course_no
where Course.course_no='01002'

10.查询“宋浩然”老师所教授的所有课程，要求显示教师号t_no、教师姓名t_name、课程号course_no、课程名course_name

select Teacher.t_no,t_name,Course.course_no,Course.course_name
from Teacher join Teaching
on Teacher.t_no=Teaching.t_no
join Course
on Teaching.course_no=Course.course_no
where t_name='宋浩然'

X__太瘦

关注

8
点赞
踩
42

收藏

觉得还不错? 一键收藏
0
评论
多表查询----实验三 SQL语句应用

一. 实验目的进一步熟悉掌握SQL Server 查询分析器环境。掌握多表查询的两种方法：连接查询、嵌套查询。掌握合并查询的方法。二. 实验内容连接查询的两种连接方法：同一查询要求，考虑两种方法实现。通过实验体会外连接的实现：左外连接、右外连接、全外连接。涉及自身连接的查询。涉及三表（多表）的连接查询。嵌套查询中不相关子查询、相关子查询的应用。引出子查询的谓...
复制链接

扫一扫