|
南阳师范学院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 项目名称='男子标枪' |
|
|
