山东大学 2020级数据库系统 实验六_找出选修了所有课程并且每门课程每次考试成绩均及格的学生的学号、姓名。

What’s more

山东大学 2020级数据库系统 实验一
山东大学 2020级数据库系统 实验二
山东大学 2020级数据库系统 实验三
山东大学 2020级数据库系统 实验四
山东大学 2020级数据库系统 实验五
山东大学 2020级数据库系统 实验六
山东大学 2020级数据库系统 实验七
山东大学 2020级数据库系统 实验八、九

写在前面

做数据库实验一定要静得下心来，才能发现其中的错误然后进行改正。同时，如果发现 SQL 语句总是报错，“一定是你错了，只是不知道错在哪里！”

其次，SQL 语句中较为复杂的点博主都进行了注释，希望大家一定要看懂思路后自己写一遍，而不是盲目的 Ctrl+C，Ctrl+V，切记切记！！

实验六

实验六相较于之前的实验难度稍微低一些，但也需要了解视图和表的区别之后，做题会更加得心应手。考察的主要是查询语句，在之前的实验中想必应该非常熟悉了……

• 6-1 例如：找出年龄小于20岁的所有学生的学号、姓名、年龄
  正确执行:create view test6_00 as select sid，name，age from pub.student where age>20
  Oracle扩展后方便写法：
  create or replace view test6_00 as select sid，name，age from pub.student where age>20
  直行select count(*) from test6_00 检查是否能够5分钟内查询出全部结果，如果超时说明可能有错误，这种情况下严禁执行"update dbtest set test=6"进行交卷。
  找出年龄小于20岁且是"物理学院"的学生的学号、姓名、院系名称,按学号排序
  思路：
  1. 在 pub.student 中找到对应的属性值即可；
  2. 注意最后加上 order by sid；

create view test6_01 as
	select sid, name, dname
	from pub.student
	where age < 20
	and sid in
		(select sid
		from pub.student_course
		where dname = '物理学院')
	order by sid
1
2
3
4
5
6
7
8
9

• 6-2 查询统计2009级、软件学院每个学生的学号、姓名、总成绩（列名sum_score）（如果有学生没有选一门课，则总成绩为空值）
  思路：
  1. 先找到满足条件的学生的 sid, name；
  2. 由于有学生没有选一门课，但是这个学生也要计算在内，因此使用 natural left outer join 来进行连接；
  3. 找到对应的总成绩，然后连接即可；

create view test6_02 as
	select sid, name, sum_score
	from 
		(select distinct sid, name
		from pub.student
		where class = '2009'
		and dname = '软件学院') 
	natural left outer join
		(select distinct sid, sum(score) sum_score
		from pub.student_course
		group by sid)
1
2
3
4
5
6
7
8
9
10
11

• 6-3 查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。
  思路：
  1. 根据条件进行查询即可；

create view test6_03 as
	select sid, name, score
	from pub.student natural join pub.student_course
	where class = '2010'
	and dname = '计算机科学与技术学院'
	and cid = (select cid from pub.course where name = '操作系统')
1
2
3
4
5
6

• 6-4 找出选修"数据库系统"课程，且成绩大于90的学生的学号、姓名。
  思路：
  1. 根据条件进行查询即可；
  2. 可以将两张表进行自然连接；

create view test6_04 as
	select sid, name
	from pub.student natural join pub.student_course
	where cid = (select cid from pub.course where name = '数据库系统')
	and score > 90
1
2
3
4
5

• 6-5 找出姓名叫"李龙"的学生的学号及其选修全部课程的课程号、课程名和成绩。
  思路：
  1. 名为“李龙”的同学可以不只一个哦！！
  2. 将两张表进行自然连接后查询出满足条件的元素即可；

create view test6_05 as
	select sid, cid, name, score
	from pub.student_course natural join pub.course
	where sid in (select distinct sid from pub.student where name = '李龙')
1
2
3
4

• 6-6 找出选修了所有课程的学生的学号、姓名。
  思路：
  1. 使用存在性检测：not exists … except(minus) … 来对所有课程和学生选课进行检验即可；

create view test6_06 as
	select distinct sid, name
	from pub.student S
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course T
		where S.sid = T.sid))
1
2
3
4
5
6
7
8
9
10

• 6-7 找出选修了所有课程并且每门课程每次考试成绩均及格的学生的学号、姓名。（题6的延伸和巩固）
  思路：

  1. 同样可以使用存在性检测先得出选修了所有课程的学生的 sid, name；
  2. 然后添加条件 where score > 60即可；

  注意：这里的不及格指的是只要有一次考试不及格就不计入！！！

create view test6_07 as
	select distinct sid, name
	from pub.student S
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course T
		where S.sid = T.sid))
	and sid not in
		(select sid
		from pub.student_course
		where score < 60)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 6-8 找出选修了所有课程并且得到所有课程的学分（即每门课程最少一次考试及格）的学生的学号、姓名。（题6的 延伸和巩固）。
  思路：
  1. 首先还是使用存在性检测得出选修了所有课程的学生的 sid, name；
  2. “最少一次考试及格” 的反面为 “全都不及格”，因此将这些最大值不及格的学生去掉即可；

create view test6_08 as
	select distinct sid, name
	from pub.student S
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course T
		where S.sid = T.sid))
	and sid not in
		(select sid
		from 
			(select distinct sid, cid, max(score) max_score
			from pub.student_course
			group by sid, cid)
		where max_score < 60)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 6-9 查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。
  思路：
  1. 第一步求出满足条件的学生的 sid, name；
  2. 第二步利用之前的方法去求出学生的总学分；（注意最大值 > 60 即计入学分哦！！）

create view test6_09 as
	select t0.sid, t0.name, t1.sum_credit
	from 
		(select distinct sid, name
		from pub.student
		where class = '2010'
		and dname = '化学与化工学院') t0,
		
		(select distinct sid, sum(credit) sum_credit
		from (select distinct sid, cid, max(score) max_score
		from pub.student_course
		group by sid, cid) natural join pub.course
		where max_score >= 60
		group by sid)t1
	where t0.sid = t1.sid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 6-10 找出有间接先行课的所有课程的课程号、课程名称。
  Tips：所谓间接先行课：某一门课程 A 的先行课 B 的先行课 C，因此 C 就是 A 的间接先行课；
  思路：
  1. 利用存在性检测 exists 即可；
  2. where 条件中均为 fcid 和 cid 的比较；

create view test6_10 as
select cid, name
from pub.course t0
where exists
	(select cid
	from pub.course t1
	where t0.fcid = t1.cid
and exists
	(select cid
	from pub.course t2
	where t1.fcid = t2.cid))
1
2
3
4
5
6
7
8
9
10
11

再次强调：一定是看懂思路之后自己实践哈~~
有问题还请斧正！