北大“數(shù)據(jù)庫原理”上機(jī)考試題（6月4日）

/*？ Examinee（UID， Uname， Gender）
    注：考生（考生號，考生姓名，性別）
    要求：考生號為主碼， 所有字段都不能為空。
    ？ Question（QID， Qdesc）
    注：題目（題目號，題目描述）
    要求：題目為主碼，所有字段不為空。
    ？ Record （UID， QID ，Score）
    注：做題記錄（考生號，題目號，得分）
    要求：考生號為參照考生表的主碼的外碼， 題目號為參照題目表的主碼的外碼，所有字段不為空，得分為介于0—100之間的整數(shù)。
    Examinee表中保存了該網(wǎng)站所有注冊用戶的信息，Question表中保存當(dāng)前所有可做的題目的信息。任何用戶可以做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） 列出蕭娟同學(xué)做的所有題目的描述及其得分。*/
    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） 列出每名考生已經(jīng)做的題目總數(shù)及總得分。*/
    select UID as 考生號，count（QID） as 題目總數(shù)，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.使用游標(biāo)完成如下操作（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））