数据库考试样卷（SQL部分）

一、SQL语言

数据字典：

Users(用户表)

列名	数据类型	非空	注释
UID	int	是	用户号，主键
Name	varchar2(30)	是	用户名
School	varchar2(30)	否	学校名称
Rating	int	是	等级分，缺省值为1400

Take(参赛表)

列名	数据类型	非空	注释
CID	int	是	比赛号，主键，外键（Contests）
UID	int	是	用户号，主键，外键（Users）
Score	int	是	比赛得分

Contests(比赛)

列名	数据类型	非空	注释
CID	int	是	比赛号，主键
Title	varchar2(40)	是	比赛名称
Time_Start	date	是	比赛开始时间
Time_End	date	是	比赛结束时间

1、查询所有“湘潭大学”的用户名称与等级分

select Name, Rating from Users where School = '湘潭大学';

2、新增一条参赛信息，“用户号为1的用户，参加比赛号为1的比赛，比赛得分为1234分”

insert into table Take values(1, 1, 1234);

3、删除用户名为“管理员”的所有用户的参赛信息

delete from Take where UID in (select UID from Users where Name = '管理员');

4、查询没有参加过任何比赛的用户编号与用户名

select UID, Name from Users where UID not in (select UID from take);

5、建立成绩视图Contest_Information(CID, Title, Num, Max_Score, Min_Score, Avg_Score)表示每个比赛的参加人数，比赛最高得分，最低得分，平均得分

create view Contest_Information(CID, Title, Num, Max_Score, Min_Score, Avg_Score) as (
	select CID, Title, count(distinct(UID)), max(Score), min(Score), avg(Score)
	from Take natural join Contests
 	group by CID, Title
);

6、将等级分在1200分以下的用户等级分增加20%， 1201~1400分的用户等级分增加15%， 1400分以上的用户等级分增加10%

update Users 
set Rating = case
	when Rating < 1200 then Rating * 1.2
	when Rating between 1200 and 1400 then Rating * 1.15
	else Rating * 1.1
end;	

7、查询每次比赛得分高于平均得分的用户号及得分，按比赛号升序，用户号升序，得分逆序排列

select CID, UID, Score from Take a 
where Score >= (
	select avg(Score) from Take b
	where b.CID = a.CID
)
order by UID, CID, Rating desc;

8、查询在1号用户所有参加的比赛中都比他得分高的用户的编号与姓名

select UID, Name from Users a
where not exists (
        (select CID from Take where UID = 1)
        minus
        (
            select CID from Take T1
            where T1.UID = a.UID and Score > (
                  select Score from Take T2 where T2.UID = 1 and T2.CID = T1.CID
            )
        )    
);--not exists (B except A) 表示 关系A 包含 关系B

9、使用标量子查询完成每个学校等级分排名前5位的用户编号，用户名，等级分，并按学校升序，等级分逆序排列

select UID, Name, School, Rating
from (
    select UID, Name, School, Rating, 
	(1+(select count(*) from User B where A.School = B.School and A.Rating < B.Rating)) as rk
    from Users A
)
where rk <= 5
order by School, Rating desc;

10、统计所有用户，比赛号1~4比赛的比赛得分，如果没有参加对应比赛，则为空值，查询的列依次为UID, NAME, C1， C2， C3， C4，其中C1， C2， C3， C4依次表示比赛1~4的用户得分

select UID, Name, 
         (select Score from Take b where b.UID = a.UID and b.CID = 1) as C1,
         (select Score from Take b where b.UID = a.UID and b.CID = 2) as C2,
         (select Score from Take b where b.UID = a.UID and b.CID = 3) as C3,
         (select Score from Take b where b.UID = a.UID and b.CID = 4) as C4
from Users a;