综合练习题（一）
设计学生运动会比赛信息的数据库，保存了如下的表：
? 运动员 sporter（运动员编号 sporterid，运动员姓名 name，运动员性别 sex，
所属系department）
? 项目 item（项目编号 itemid，项目名称 itemname，项目比赛地点 location） ? 成绩 grade（运动员编号 sporterid，项目编号 itemid，积分 mark）
一、完成如下功能要求：（使用SQL语句完成） （1） 定义各个表的主码外码约束
（2）运动员的姓名和所属系别不能为空值。
（3）积分要么为空值，要么为 6,4,2,0, 分别代表第一、二、三名和其他名次的积分。
二、完成如下数据录入（使用SQL语句完成） 运动员表（ ） 项目表（ ） 积分表（
1001, x001, 6
x001, 男子五千米，一操场 x002，男子标枪，一操场 x003, 男子跳远，二操场 x004, 女子跳高，二操场 x005, 女子三千米，三操场
1001, 李明，男，计算机系 1002，张三，男，数学系 1003, 李四，男，计算机系 1004, 王二，男，物理系 1005, 李娜，女，心理系 1006, 孙丽，女，数学系
1002, x001, 4 1003, x001,2 1004, x001,0 1001, x003,4 1002, x003,6 1004, x003,2 1005, x004,6 1006, x004,4
)
三、完成如下查询
1、求出目前总积分最高的系名，及其积分
· 所有的系名都在 sporter 表中，所以此时需要与 grade 表进行关联查询。
SELECT s.department,SUM(g.mark) FROM sporter s ,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department;
·以上的结果通过排序，就可以求出总积分最高的。
SELECT s.department,SUM(g.mark) sum
FROM sporter s ,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department ORDER BY sum DESC ;
· 使用 ROWNUM 完成该题最为方便
SELECT * FROM (
SELECT s.department,SUM(g.mark) sum FROM sporter s ,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department ORDER BY sum DESC)
WHERE ROWNUM=1;
2、查询出在一操场进行比赛的各项目名称及其冠军的姓名 · 首先确定出一操场中的全部项目和每个项目的成绩
SELECT i.itemname,s.name,g.mark FROM item i, grade g, sporter s WHERE i.itemid=g.itemid AND
s.sporterid=g.sporterid AND i.location='一操场';
· 根据以上的结果求出最高分 SELECT i.itemname,s.name,g.mark FROM item i,grade g,sporter s WHERE i.itemid=g.itemid AND
s.sporterid=g.sporterid AND i.location='一操场' AND
g.mark=6 ;
3、查询出参加了“张三”所参加过的项目的其他同学的姓名
· 找出张三参加过的项目编号 SELECT g.itemid FROM sporter s,grade g
WHERE s.sporterid=g.sporterid AND s.name='张三' ;
· 根据此编号再从表中查询符合此编号项目的运动员编号，再从运动员表中
取出姓名
SELECT DISTINCT s.name FROM sporter s,grade g
WHERE s.sporterid=g.sporterid AND s.name<>'张三' AND g.itemid IN (SELECT g.itemid
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid AND s.name='张三') ;
4、经查“张三”因为使用了违禁药品，其成绩都记 0 分，请在数据库中作出相应修改
· 首先应该确定好张三的运动员编号，之后以此编号作为更新的条件。
5、经组委会协商，需要删除女子跳高比赛项目
· 直接执行 delete 语句即可 UPDATE grade SET mark=0 WHERE sporterid=(
SELECT sporterid FROM sporter WHERE name='张三') ;
DELETE FROM grade WHERE itemid=(SELECT itemid FROM item WHERE itemname='女子跳高')
DELETE FROM item WHERE itemname='女子跳高' ;
6、查询计算机系所有参加比赛获得冠军的人及获得冠军的项目及项目的比赛场地。
SELECT s.*,g.mark,i.itemname,i.location FROM sporter s ,grade g ,item i WHERE s.sporterid=g.sporterid AND
g.itemid=i.itemid AND s.department='计算机系' AND g.mark=6