2007年6月4日上午数据库原理上机考试

题目和我个人的答案如下：

建三个表

Examinee(UID,Uname,Gender)
注:考生(考生号,姓名,性别),所有字段不为空,考生号为主键

Question(QID,Qdesc)
注:题目(题目号,题目描述),所有字段不为空,问题号为主键

Record(UID,QID,Score)
注:做题记录(考生号,题目号,得分),UID,QID分别为参考表Examinee,Question的主键的外键,所有字段不为空,Score为介于0到100的整数

1.建表,要求全部实现上述的要求
create table Examinee(
UID char(5),
Uname varchar(20)not null,
Gender char(2)not null,
primary key (UID))

create table Question(
QID char(5),
Qdesc varchar(50)not null,
primary key (QID))

create table Record(
UID char(5),
QID char(5),
Score tinyint not null,
primary key (UID,QID),
foreign key (UID) references Examinee(UID) on delete cascade on update cascade,
foreign key (QID) references Question(QID) on delete cascade on update cascade,
check (Score between 0 and 100))

2.插入数据
(题目中带了数据,不过我没有记^_^)

3.(1)找出萧娟同学做过的题目的题目描述和得分
select Uname,Qdesc,Score
from Examinee a,Question b,Record c
where a.UID=c.UID and b.QID=c.QID and Uname='萧娟'

3.(2)列出每个同学已经回答的问题总数以及总得分
select UID,count(*) 问题总数,sum(Score) 总得分
from Record
group by UID

3.(3)找出回答了题目描述为'Who am I ?'的同学姓名,以及该同学回答的所有题目的描述和得分
select Uname,Qdesc,Score
from Examinee a,Question b,Record c
where a.UID=c.UID and b.QID=c.QID and Uname in (
select Uname
from Examinee a,Question b,Record c
where a.UID=c.UID and b.QID=c.QID and Qdesc='Who am I ?')

4.找出平均成绩不到60分的考生,将其不到60分的成绩加5分
update Record
set Score=Score+5
where Score<60 and UID in (
select UID
from Record
group by UID
having avg(Score)<60)

5.用游标实现以下操作
找出平均得分不到60分的题目,将这些题目的得分开方再乘以10
declare tempcursor cursor for
select QID
from Record
group by QID
having avg(Score)<60

declare @QID char(5)

open tempcursor
fetch next from tempcursor
into @QID
while @@fetch_status=0
begin
update Record
set Score=SQRT(Score)*10
where QID=@QID
fetch next from tempcursor
into @UID
end

close tempcursor
deallocate tempcursor

6.找出做了平均得分最低的题目的考生,包括该生的姓名以及所做的该题的得分
declare @temptable table(
QID char(5),avg_score tinyint)
insert into @temptable
select QID,avg(Score)
from Record
group by QID

select Uname,Score
from Examinee a,Record b
where a.UID=b.UID and b.QID in (
select QID
from @temptable
where Score=(Select min(avg_grade)
from @temptable)

最后一点个人建议，如果有想拿北大学士学位的同学，最好尽量在这一门上机上拿优，个人认为这是3门中较为容易的一门

更多资料尽在四联自考论坛（http://bbs.4lzx.com），转贴请保留此信息。