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

数据库系统教程(何玉洁_李宝安_编著)第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

from student s join SC on s.sno = SC.sno join course c on c.cno = SC.cno 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)

from student s join SC on s.sno = SC.sno join course c on c.cno = SC.cno 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 SC.cno = c.cno 5.查询每个系年龄大于20岁的学生人数，并将结果保存到一个新的永久表Dept_Age中。

select sdept,count(sno)人数 into student_age from student where sno in(select sno from student where sage

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

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 = c.cno)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 = SC.cno)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 SC.cno = c.cno 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 SC.cno = c.cno))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,SC.cno,cname,credit from course c join SC on c.cno = SC.cno join student s on s.sno = SC.sno where exists(select sno,sum(credit)'total credit' from SC join course c on SC.cno = c.cno 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')*以上内容仅供参考