北大“数据库原理”上机考试题（6月4日） -自考串讲笔记

　　/*？ Examinee（UID， Uname， Gender）

　　注：考生（考生号，考生姓名，性别）

　　要求：考生号为主码， 所有字段都不能为空。

　　？ Question（QID， Qdesc）

　　注：题目（题目号，题目描述）

　　要求：题目为主码，所有字段不为空。

　　？ Record （UID， QID ，Score）

　　注：做题记录（考生号，题目号，得分）

　　要求：考生号为参照考生表的主码的外码， 题目号为参照题目表的主码的外码，所有字段不为空，得分为介于0—100之间的整数。

　　Examinee表中保存了该网站所有注册用户的信息，Question表中保存当前所有可做的题目的信息。任何用户可以做Question表中的任何题目。*/

　　drop table c111Examinee

　　drop table c111Question

　　drop table c111Record

　　create table c111Examinee

　　（UID int not null，

　　Uname char（10）not null，

　　Gender char（1）not null，

　　primary key（UID））

　　create table c111Question

　　（QID int not null，

　　Qdesc char（30）not null，

　　primary key（QID））

　　create table c111Record

　　（UID int not null，

　　QID int not null，

　　Score int，

　　primary key（UID，QID），

　　foreign key（UID）references c111Examinee（UID），

　　foreign key（QID）references c111Question（QID），

　　check （Score between 0and 100））

　　select *from c111Examinee

　　select *from c111Question

　　select *from c111Record

　　insert into c111Examinee values（101，'张刚'，'F'）

　　insert into c111Examinee values（102，'王玲'，'M'）

　　insert into c111Examinee values（103，'萧娟'，'M'）

　　insert into c111Examinee values（104，'李凯'，'F'）

　　insert into c111Question values（201，'Who am I ？'）

　　insert into c111Question values（202，'Where is PKU ？'）

　　insert into c111Question values（203，'What is Database ？'）

　　insert into c111Question values（204，'Are you OK ？'）

　　insert into c111Question values（205，'What is Data Stream ？'）

　　insert into c111Record values（101，201，44）

　　insert into c111Record values（101，202，59）

　　insert into c111Record values（102，204，88）

　　insert into c111Record values（102，202，66）

　　insert into c111Record values（103，205，69）

　　insert into c111Record values（104，203，94）

　　/*3.用SQL语句完成下列查询：（每个10分，共30分）

　　（1） 列出萧娟同学做的所有题目的描述及其得分。*/

　　select 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 Uname='萧娟'

　　/*（2） 列出每名考生已经做的题目总数及总得分。*/

　　select UID as 考生号，count（QID） as 题目总数，sum（Score） as 总得分

　　from c111Record

　　group by UID

　　/*（3） 列出做了题目描述为‘ Who am I ？’的考生的姓名，以及该考生所做的所有题目的描述和得分。*/

　　select c.UID as 考生号，b.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 UID

　　from c111Question ，c111Record

　　where c111Question.QID=c111Record.QID

　　and Qdesc='Who am I ？'）

　　/*4.完成如下更新（15分）：

　　对于平均得分不到60分的考生，把他的每个低于60分的得分提高5分。*/

　　update c111Record

　　set Score=Score+5

　　where UID in（select UID

　　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））