- 北大“数据库原理”上机考试题（6月4日）
- 发布日期时间：2007-7-19 来源：不详 点击数： 作者：佚名
from c111Record
group by UID
having avg（Score）<60）
and Score<60
/*5.使用游标完成如下操作（15分）
找出平均得分低于60分的题目，然后把所有题目的得分开平方（SQRT（））再乘以10.*/
declare c1 cursor for
select b.QID
from c111Question as a，c111Record as b
where b.QID= a.QID
group by b.QID
having avg（Score）<60
open c1
declare @x int
fetch next from c1 into @x
if @@fetch_status=0
begin
update c111Record
set Score=（（SQRT（Score））*10）
where QID=@x
fetch next from c1 into @x
end
close c1
deallocate c1
/*6.列出做了平均得分最低的题目的考生，需要提供以下信息， 考生姓名、该考生在该题目的得分。（20分）*/
select Uname as 考生姓名，c.QID as 题目号，Qdesc as 题目描述，Score as 得分
from c111Examinee as a，c111Question as b，c111Record as c
where a.UID=c.UID and b.QID= c.QID and c.UID in （select c111Record.UID
from c111Examinee ，c111Question，c111Record
where c111Examinee.UID=c111Record.UID and c111Question .QID=c111Record .QID
group by c111Record.UID
having avg（Score）<=all（select avg（Score）
from c111Record
group by UID））
文章转载请注明来源于：汕头自考网
|
|
