为学生运动会比赛信息建立数据库表

需要保存的数据如下：

 运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系号department）

项目item（项目编号itemid，项目名称itemname，项目比赛地点location）

成绩grade（运动员编号id，项目编号itemid，积分mark）

建表要求如下：

1. 每个表都要有主键

2. 根据需要自行决定增加哪些约束

3. 成绩表要有外键约束

二、给上一步建好的表插入如下数据

运动员（          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          1005，x004，6          1006，x004，4          1003，x002，6          1005，x002，4          1006，x002，2          1001，x002，0）

--运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系号department）
create table sporter   
(  
    sporterid varchar2(10)primary key,  
    name varchar2(10) not null,  
    sex varchar2(3),  
    department varchar(20) not null  
);  

--项目item（项目编号itemid，项目名称itemname，项目比赛地点location）

create table item(  
    itemid varchar2(10) primary key,  
    itemname varchar2(20),  
    location varchar2(20)  
);  

-- 成绩grade（运动员编号id，项目编号itemid，积分mark）
create table grade(  
    sporterid varchar2(10) ,   
    itemid varchar2(10),  
    mark number,  
    primary key (sporterid,itemid),  
    foreign key (sporterid) references  sporter(sporterid),  
    foreign key (itemid)  references item(itemid)  
);  

  

-- 2. 往表中插入数据：  
insert into sporter values('1001', '李明','男','计算机系');  
insert into sporter values('1002', '张三','男','数学系');  
insert into sporter values('1003', '李四','男','计算机系');  
insert into sporter values('1004', '王二','男','物理系');  
insert into sporter values('1005', '李娜','女','心理系');  
insert into sporter values('1006', '孙丽','女','数学系');  
select * from SPORTER;  
  
insert into item values('x001', '男子五千米','一操场');  
insert into item values('x002', '男子标枪','一操场');  
insert into item values('x003', '男子跳远','二操场');  
insert into item values('x004', '女子跳高','二操场');  
insert into item values('x005', '女子三千米','三操场');  
select * from item;  
  
insert into grade values('1001',  'x001', 6);  
insert into grade values('1002',  'x001', 4);  
insert into grade values('1003',  'x001', 2);  
insert into grade values('1004',  'x001', 0);  
insert into grade values('1001',  'x003', 4);  
insert into grade values('1002',  'x003', 6);  
insert into grade values('1004',  'x003', 2);  
insert into grade values('1005',  'x004', 6);  
insert into grade values('1006',  'x004', 4);  
insert into grade values('1003'，'x002'，6);  
insert into grade values( '1005'，'x002'，4);  
insert into grade values('1006'，'x002'，2);  
insert into grade values('1001'，'x002'，0);  
select * from grade;  

--求出目前总积分最高的系名，及其积分。
select *   
from(select department,sum(mark) sum_mark  
  from sporter s 
  join grade g on s.sporterid=g.sporterid  
  group by department  
  order by sum_mark desc) 
where rownum <2

--找出在一操场进行比赛的各项目名称及其冠军的姓名。
select i.location,i.itemname,s.name
from grade g,sporter s,item i,(select i.itemid,max(mark) max_mark  
  from item i ,grade g  
  where i.itemid=g.itemid 
        and  i.location='一操场'   
  group by i.itemid) t
where s.sporterid = g.sporterid
      and g.itemid = i.itemid
      and i.itemid = t.itemid
      and g.mark = t.max_mark
 

     
--找出参加了张三所参加的所有项目的其他同学的姓名。
select distinct s.name
from sporter s,item i,grade g,(select i.itemname
    from sporter s,item i,grade g
    where s.name = '张三' and i.itemid = g.itemid and s.sporterid = g.sporterid) t
where i.itemid = g.itemid and s.sporterid = g.sporterid and t.itemname = i.itemname and s.name<>'张三'

      
--经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。
update grade g set g.mark=0  
where g.sporterid= (select s.sporterid  from sporter s  where name='张三');  
  
select * from grade  
where g.sporterid= (select s.sporterid  from sporter s  where name='张三');  

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

delete from item i where i.itemname='女子跳高';  

select * from item;  

--列出李四参加了那些项目以及获得了那些积分
select s.name,i.itemname,g.mark
from sporter s,grade g,item i
where s.name = '李四' and s.sporterid = g.sporterid and i.itemid = g.itemid

--列出参与项目最多的同学的名字
select *
from (
select s.name,count(*)
from grade g,sporter s
where s.sporterid = g.sporterid
group by s.name
order by count(*) desc)
where rownum<2

--找出积分最高的同学的名字及他的积分

select *   
from(select s.name,sum(mark) sum_mark  
  from sporter s 
  join grade g on s.sporterid=g.sporterid  
  group by s.name  
  order by sum_mark desc) 
where rownum <4

--列出参与男子标枪的同学的名字和在该项目上的积分，按积分从高到底排序
select s.name,g.mark
from item i,grade g,sporter s
where i.itemid = g.itemid and g.sporterid = s.sporterid and i.itemname = '男子标枪'
order by g.mark desc

--列出每个系的总积分情况，积分由高到底排序
select department,sum(mark) sum_mark  
  from sporter s 
  join grade g on s.sporterid=g.sporterid  
  group by department  
  order by sum_mark desc