数据库竞赛题目及参考答案

数据库竞赛题目及参考答案
南阳师范学院2013-2014（1）数据库课程
教育技术学12.4班 SQL语句竞赛
（提示：所有的操作均用SQL语句完成。

）
有某个学生运动会比赛信息的数据库，保存了如下的表：
运动员（运动员编号，运动员姓名，运动员性别，所属系名）
项目（项目编号，项目名称，项目比赛地点）
成绩（运动员编号，项目编号，积分）
请用SQL语句完成如下功能：
1．建立数据库，数据库名称为y_x_c（4分）
Create database y_x_c
2. 建立数据表，并满足如下条件：（30分）
（1）定义各个表的主码、外码约束
（2）运动员的姓名和所属系别不能为空值。

（3）积分要么为空值，要么为6,4,2,0,分别代表第一，二，三名和其他名次的积分。

Create table 运动员(
运动员编号 char(8) primary key,
运动员姓名 char(20) not null,
运动员性别 check(运动员姓名 in('男','女'),
所属系名 varchar(100) not null
)
Create table 项目(
项目编号 char(6) primary key,
项目名称 varchar(50),
项目比赛地点 varchar(50))
Create table 成绩(
运动员编号 char(8) foreign key references 运动员(运动员编号), 项目编号 char(6) foreign key references 项目(项目编号),
积分 tinyint check(积分 in(6,4,2,0)) --注释：没有写not null 就是允许空
)
3．往表中插入数据：(提示: 每个表只需要书写插入前面两行数据的命令即可)（6分）
运动员（1001，李明，男，计算机系
1002，张三，男，数学系
1003，李四，男，计算机系
1004，王二，男，物理系
1005，李娜，女，心理系
1006，孙丽，女，数学系）
项目（ x001，男子五千米，1操场
x002，男子标枪，1操场
x003，男子跳远，2操场
x004，女子跳高，2操场
x005，女子三千米，3操场）
成绩（ 1001，x001，6
1002，x001，4
1003，x001，2
1004，x001，0
1001，x003，4
1002，x003，6
1004，x003，2
1005，x004，6
1006，x004，4）
这部分比较简单，省略答案
4．使用完成如下查询
（1）找出参加了张三所参加的所有项目的其他同学的姓名。

（10
分）
select 运动员姓名
from 运动员
where 运动员姓名!='张三' and 运动员编号 in(select distinct 运动员编号 from 成绩
where 项目编号 in(select 项目.项目编号
from dbo.成绩,dbo.项目,dbo.运动员
where dbo.成绩.运动员编号=dbo.运动员.运动员编号and dbo.成绩.项目编号=dbo.项目.项目编号 and 运动员编号=(select 运动员编号
from 运动员
where 运动员姓名='张三')))
（2）求出目前总积分最高的系名，及其积分。

（10分）
select 所属系名,sum(积分)
from 运动员,成绩
where 运动员.运动员编号=成绩.运动员编号
group by 所属系名
having sum(积分)>=all --注释：和any不同，>=all等同于大于等于子查询中的最大值
(select sum(积分)
from 运动员,成绩
where 运动员.运动员编号=成绩.运动员编号
group by 所属系名)
（3）找出在1操场进行比赛的各项目名称及其冠军的姓名。

（10分）
select 项目名称,运动员姓名
from 运动员,成绩,项目
where 运动员.运动员编号=成绩.运动员编号 and 项目.项目编号=成绩.项目编号 and项目比赛地点='1操场'and 积分>=
(select max(积分)
from 成绩,项目
where 项目比赛地点='1操场' and 成绩.项目编号=项目.项目编号) （4）找出每个项目的最高分及对应的项目编号。

（5分）
Select项目编号,max(积分)
From 项目
Group by 项目编号
5．建立视图xm_ydy_yx，查询每个项目的项目名称、运动员姓名和所属系名。

（10分）
create view xm_ydy_yx
as
select 项目名称,运动员姓名,所属系名
from 项目,运动员,成绩
where 项目.项目编号=成绩.项目编号 and 运动员.运动员编号=成绩.运动员编号
6．经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中做出相应修改。

（10分）
Update 成绩
Set 积分=0
Where 运动员编号=( Select 运动员编号 from 运动员 where 运动员姓名='张三')
7．经组委会协商，需要删除女子跳高比赛项目。

（5分）
这道题看似只需要删除项目中的数据，实际上并没有这么简单。

因为项目表作为主表对外表成绩表有影响，所以要想删除女子跳高必须先将参加女子跳高的信息都删除。

Delete from 成绩
Where 项目编号=(select 项目编号 from 项目 where 项目名称='女子跳高')
然后删除项目表中的数据：
Delete from 项目
Where项目名称='女子跳高'
8．将男子标枪项目调到2操场进行比赛。

Update 项目
Set项目比赛地点='2操场'
Where 项目名称='男子标枪'。