数据库测试题答案

1.查询报名人数大于等于30并且小于等于40的课程信息，要求显示课程名称和报名人数（使用BETWEEN…AND）
use xk
go
select couname,willnum
from course
where willnum between 30 and 40
go

2.显示所有系的班级名称，要求计算各系班级数量，显示要求如下：
use xk
go
select department.departno ,departname ,classname
from department,class
where department.departno=class.departno
order by department.departno
compute count(classname) by department.departno
go
3.查询每个系所开课程的门数，显示系代码、系名称、开课门数
use xk
go
select department.departno,departname,count(couname)
from department,course
where department.departno=course.departno
group by department.departno,departname
go
4.统计各个学生的选课门数，并按选课门数由高到低排序。
use xk
go
select stuname,count(couno)
from student,stucou
where student.stuno=stucou.stuno
group by stuname
order by count(couno) desc
go

5.查看未选课的学生信息（班级名称，学号，姓名）并统计未选课的学生个数（用compute）。
use xk
go
select classname,stuno,stuname
from student,class
where class.classno=student.classno
and stuno not exists(select stuno from stucou)
go
6.查找选课门数等于5门的学生，要求显示班级，学号，姓名，选课门数。
use xk
go
select classname,student.stuno,stuname,count(couno)
from class,student,stucou,course
where class.classno=student.classno
and student.stuno=stucou.stuno
and stucou.couno=course.couno
group by classname,student.stuno,stuname
having count(couno)=5
go
7.查看“01多媒体”班姓名为“叶杰”的学生的选课信息（班级名称，学号，姓名，课程名称）并统计课程数（用compute）。
select classname,stucou.stuno,stuname,couname
use xk
go
from class,student,stucou,course
where class.classno=student.classno
and student.stuno=stucou.stuno
and stucou.couno=course.couno
and classname='01多媒体'
and stuname='叶杰'
compute count(couname)
go
8.查找报名总人数大于100的系，要求显示系名和报名人数。
use xk
go
select departname,sum(willnum)
from department,course
where department.departno=course.departno
group by departname
having sum(willnum)>100
go

9.“李云”同学取消了“中餐菜肴制作”课程，删除该选课记录。
use xk
go
delete stucou
from stucou,course,student
where stucou.couno=course.couno
and student.stuno=stucou.stuno
and stuname='李云'
and couname like '%中餐菜肴制作%'
go%
update course set willnum=willnum-1 where couname='%中餐菜肴制作%'
go
10.将“01电子商务”班姓名为“张浩”同学选的“中餐菜肴制作”课程改为“房地产漫谈”课程。
use xk
go
update stucou
set couno=(select couno
from course
where couname like '%房地产漫谈%')
from stucou,course,student,class
where stucou.couno=course.couno
and

student.stuno=stucou.stuno
and class.classno=student.classno
and stuname='张浩'
and classname='01电子商务'
and couname like '%中餐菜肴制作%'
go