经典SQL学习笔记 (九)－比赛信息数据库练习

比赛信息数据库

题目背景：
    有某个学生运动会比赛信息的数据库，保存了如下的表：
    运动员sporter表：（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系department）
    项目item表（项目编号itemid，项目名称itemname，loc场地）
    成绩grade表（运动员编号sporterid，项目编号itemid，积分mark）
功能要求：
1、建表
    定义各个表的主键和外键约束；
    运动员的姓名和所属系别不能为空值
    积分要么为空值，要么是6,4,2,0，分别代表第一，二，三名和其他名次的积分

    create table sporter(
    sporterid Integer auto_increment,  --运动员编号
    name varchar(20) not null,         --运动员姓名
    sex char(4),          --运动员性别
    department varchar(20) not null,   --所属系
    primary key(sporterid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    create table item(
    itemid Integer auto_increment,     --项目编号
    itemname varchar(45),              --项目名称
    loc varchar(45),                   --场地
    primary key(itemid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    create table grade(
    sporterid Integer,
    itemid Integer,
    mark Integer,
    constraint fk_sporter foreign key (sporterid) REFERENCES sporter (sporterid),
    constraint fk_item foreign key (itemid) REFERENCES item (itemid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;


2、插入数据
运动员（
     1001，李盼，女，计算机系
     1002，王玥，女，数学系
     1003，丁赛，男，计算机系
     1004，汪丽，女，物理系
     1005，李娜，女，心理系
     1006，王骋，女，化学系）
项目（
     x001，男子五千米，一操场
     x002，男子标枪，一操场
     x003，男子跳远，二操场
     x004，女子跳高，二操场
     x005，女子三千米，三操场）
积分（
     1001，x001,6
     1002，x001,4
     1003，x001,2
     1004, x001,0
     1001, x003,4
     1002, x003,6
     1004, x003,2
     1004, x004,6
     1006, x004,4)

     insert into sporter values
     (1001,'李盼','女','计算机系'),
     (1002,'王玥','女','数学系'),
     (1003,'丁赛','男','计算机系'),
     (1004,'汪丽','女','物理系'),
     (1005,'李娜','女','心理系'),
     (1006,'王骋','女','化学系');

     insert into item values
     (001,'男子五千米','一操场'),
     (002,'男子标枪','一操场'),
     (003,'跳远','二操场'),
     (004,'女子跳高','二操场'),
     (005,'女子三千米','三操场');

     insert into grade values
     (1001,001,6),
     (1002,001,4),
     (1003,001,2),
     (1004,001,0),
     (1001,003,4),
     (1002,003,6),
     (1004,003,2),
     (1004,004,6),
     (1006,004,4);





要求：
1.求出总积分最高的系名及总积分
select A.* from 
(select department dept, sum(mark) m from sporter s,grade g where s.sporterid = g.sporterid group by department) A 
where A.m = (
select max(m) from 
(select department dept, sum(mark) m from sporter s,grade g where s.sporterid = g.sporterid group by department) T);



select department dept, sum(mark) m 
from sporter s,grade g where s.sporterid = g.sporterid 
group by department 
order by m desc 
limit 1;


2.查询在一操场进行比赛的项目名称及其冠军的姓名

select (select itemname from item where itemid=T.itemid) itname, T.max 
from (select max(mark) max,sporterid,itemid from grade where itemid in 
(select itemid from item where itemname in
(select itemname from item where loc ='一操场')) group by itemid) T;


3.找出参加了王玥所参加过的项目的其他同学的姓名
select name from sporter where sporterid in (
select sporterid from grade where itemid in (
select itemid from grade where sporterid = (
select sporterid from sporter where name='王玥'))
);

4.经查,王玥因为使用了违禁药品，其成绩都记为0分，请在数据库中做出相应修改f
update grade set mark=0 where sporterid = (select sporterid from sporter where name = '王玥');

5.经组委会协商，需要删除女子跳高比赛项目
delete from grade where itemid = (select itemid from item where itemname='女子跳高');
delete from item where itemname ='女子跳高';