数据库系统教程(何玉洁_李宝安_编著)第6章习题参考答案

第6章高级查询习题答案

1.查询计算机系每个学生的VB考试情况，列出学号、姓名、成绩和成绩情况，

其中成绩情况的显示规则如下：

如果成绩大于等于90，则成绩情况为“好”；

如果成绩在80~89，则成绩情况为“较好”；

如果成绩在70~79，则成绩情况为“一般”；

如果成绩在60~69，则成绩情况为“较差”；

如果成绩在小于60，则成绩情况为“差”。

select s.sno,s.sname,grade,

case

when grade >= 90 then '好'

when grade between 80 and 89 then '较好'

when grade between 70 and 79 then '一般'

when grade between 60 and 69 then '较差'

when grade < 60 then '差'

end as 考试情况

from student s join SC on s.sno = SC.sno

join course c on https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o

where cname = 'VB'

2.统计每个学生的选课门数（包括没有选课的学生），列出学号、选课门数和

选课情况，其中选课情况显示规则如下：

如果选课门数大于等于6门，则选课情况为“多”；

如果选课门数在3~5门，则选课情况为“一般”；

如果选课门数在1~2门，则选课情况为“偏少”；

如果没有选课，则选课情况为“未选课”。

select s.sno,count(cno) 选课门数,

case

when count(cno) >= 6 then '多'

when count(cno) between 3 and 5 then '一般'

when count(cno) between 1 and 2 then '偏少'

when count(cno) = 0 then '未选课'

end as 选课情况

from student s left join SC on s.sno = SC.sno

group by s.sno

3.统计每个系VB课程的考试情况，列出系名和考试情况，其中考试情况如下：

如果VB平均成绩超过90分，则考试情况为“好”；

如果VB平均成绩在81~90分，则考试情况为“良好”；

如果VB平均成绩在70~80分，则考试情况为“一般”；

如果VB平均成绩低于70分，则考试情况为“较差”。

select sdept,

case

when avg(grade) > 90 then '好'

when avg(grade) between 81 and 91 then '良好'

when avg(grade) between 70 and 81 then '一般'

when avg(grade) <70 then '较差'

end as 考试情况

from student s join SC on s.sno = SC.sno

join course c on https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o

where cname = 'VB'

group by sdept

4.修改全部课程的学分，修改规则如下：

如果是第1~2学期开设的课程，则学分增加5分；

如果是第3~4学期开设的课程，则学分增加3分；

如果是第5~6学期开设的课程，则学分增加1分；

对其它学期开设的课程，学分不变。

update SC set Grade = Grade +

case

when semester between 1 and 2 then 5

when semester between 3 and 4 then 3

when semester between 5 and 6 then 1

else 0

end

from SC join course c on https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o

5.查询每个系年龄大于20岁的学生人数，并将结果保存到一个新的永久表

Dept_Age中。

select sdept,count(sno) 人数

into student_age

from student

where sno in (

select sno from student

where sage < 20

)

group by sdept

6.统计第2学期开设的课程的总学分，列出该学期开设的课程名、学分和总学

分。（可分步骤实现）

select cname,credit,(select sum(credit) from course where semester = 2) 总学分from course where semester = 2

7.统计考试平均成绩大于等于80分的学生的姓名、考试的课程号、考试成绩

和平均成绩，并将结果按平均成绩从高到低排序。（可分步骤实现）

select sname,cno,grade,average from student join SC on student.sno = SC.sno

join (select sno,avg(grade) average from SC group by sno having avg(grade) >= 80) A on student.sno=A.sno

8.查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。

select sname,sage from student

where sdept = '计算机系' and sage < all (select sage from student where sdept = '信息管理系')

9.查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。

select sname,sage from student

where sdept = '计算机系' and sage > some (select sage from student where sdept = '信息管理系')

10.查询哪些课程没有学生选修，要求列出课程号和课程名。（用EXISTS子查询

实现）

select cno,cname from course c

where not exists(select * from SC where cno = https://www.360docs.net/doc/b94324176.html,o)

11.查询计算机系哪些学生没有选课，列出学生姓名。（用EXISTS子查询实现）

select sname from Student s

where sdept = '计算机系' and not exists (select * from SC where sno = s.sno)

12.查询没有选修第2学期开设的全部课程的学生的学号和所选的课程号。

select sno,cno from SC

where not exists(select * from course where Semester = 2 and cno = https://www.360docs.net/doc/b94324176.html,o)

13.查询至少选了第4学期开设的全部课程的学生的学号和所在系。

select sno,sdept from student s

where not exists(

select * from course c where semester = 4 and not exists(

select * from SC where https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o and s.sno = SC.sno))

14.查询至少选了“0831102”号学生所选的全部课程的学生的学号。

select sno from student s

where sno !=0831102 and not exists(

select * from SC c where sno=0831102 and not exists(

select * from SC where SC.sno = s.sno and https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o))

15.查询至少选了“张海”所选的全部课程的学生的学号、所在系和所选的课程

号。

select s.sno,sdept,cno from student s join SC on s.sno=SC.sno

where s.sno in(

select sno from SC

where cno in(

select cno from student s join SC on s.sno = SC.sno

where sname='张海')

group by sno

having count(*)=(

select count(*) from student s join SC on s.sno = SC.sno

where sname='张海'))

AND sname != '张海'

16.查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程

号、课程名以及学分。

select SC.sno,sdept,https://www.360docs.net/doc/b94324176.html,o,cname,credit from course c join SC on https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o

join student s on s.sno = SC.sno

where exists(

select sno,sum(credit) 'total credit' from SC join course c on https://www.360docs.net/doc/b94324176.html,o = https://www.360docs.net/doc/b94324176.html,o

group by SC.sno

having sum(credit) > 3)

17.查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课

程名和学分。

select cname,credit from course

where semester in (1,4) and credit in(

select credit from course where semester = 4

intersect

select credit from course where semester = 1)

18.查询“李勇”和“王大力”所选的相同的课程，列出课程名、开课学期和学

分。

select cname,semester,credit from course where cno in(

select cno from SC join student s on SC.sno = s.sno

where sname = '李勇'

intersect

select cno from SC join student s on SC.sno = s.sno

where sname = '王大力')

19.查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学

分。

select cname,semester,credit from course where Cno in(

select cno from SC join student s on SC.sno = s.sno

where Sname = '李勇'

except

select cno from SC join student s on SC.sno = s.sno

where Sname = '王大力')

20.查询至少同时选了“C001”和“C002”这两门课程的学生的学号和所选的课

程号。

select sno,cno from SC where sno in(

select sno from SC

where cno = 'C001'

intersect

select sno from SC

where cno = 'C002') *以上内容仅供参考