oracle练习（mldn视频课程）三

综合练习
学生运动会比赛信息数据库
1，运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系号department）
2，项目item（项目编号itemid，项目名称itemname，项目比赛地点location）
3，成绩grade（运动员编号sporterid，项目编号itemid，积分mark）
建表
1，定义各个表的主码外码约束
2，运动员的姓名和所属惜别不能为空值
3，积分要么为空值，要么为6，4，2，0，分别代表第一二三名的和其他名次的积分
运动员表
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
运动员表：
create table sporter(sporterid number(4) primary key not null,name varchar2(50) not null,
sex varchar2(2) check (sex in('男','女')),department varchar2(30) not null);
项目表：
create table item(itemid varchar2(4) primary key not null,
itemname varchar2(50) not null,location varchar2(50) not null);
成绩表：
create table grade(sporterid number(4),itemid varchar2(4),
constraint sporter_grade_sporterid_fk foreign key(sporterid)
references sporter(sporterid) on delete cascade,
constraint sporter_item_itemid_fk foreign key(itemid)
references item(itemid) on delete cascade,
mark number(2) check(mark in('6','4','2','0')));
插入数据：
运动员表
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','孙丽','女','数学系');
项目表
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','女子三千米','三操场');
积分表
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);
题目：
1，求出目前总积分最高的系名及其积分。
第一种方法：
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;
第二种方法：
select distinct s.department,temp.sum
from (select s1.department dept,sum(g.mark) sum
from sporter s1,grade g
where s1.sporterid=g.sporterid
group by s1.department
order by sum desc) temp,sporter s,grade g
where temp.sum=(select max(sum(g1.mark)) su
from sporter s,grade g1
where s.sporterid=g1.sporterid
group by s.department)
and s.department=temp.dept;
2,找出在一操场进行比赛的各项目名称及冠军的姓名
第一种方法(自己)：
select s.itemname,sp.name,g.mark from
(select itemname,itemid from item where location='一操场') s,
grade g,sporter sp,(select max(mark) max from grade) sg
where s.itemid=g.itemid and sp.sporterid=g.sporterid
and sg.max=g.mark
order by g.mark desc;
第二种方法(自己)：
select s.itemname,sp.name,g.mark from
(select itemname,itemid from item where location='一操场') s,
grade g,sporter sp
where s.itemid=g.itemid and sp.sporterid=g.sporterid
and rownum=1
order by g.mark desc;
第三种方法（答案）：
select i.itemname,s.name,g.mark
from item i,grade g,sporter s
where i.location='一操场' and i.itemid= g.itemid
and s.sporterid=g.sporterid
and g.mark=6;
3,找出参加了张三所参加过的项目的其他同学的名字
第一种方法（自己）：
select sp.name,temp.itd,temp.itm from sporter sp,
(select i.itemid itd,i.itemname itm from sporter s,item i,grade g
where s.name='张三' and i.itemid=g.itemid and
s.sporterid=g.sporterid) temp,grade gg,item it
where it.itemid=temp.itd and sp.name !='张三' and
gg.sporterid=sp.sporterid and it.itemid=gg.itemid;
第二种方法（答案）：
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分，请在数据库中作出相应修改
update grade set mark=0
where sporterid=(select sporterid from sporter
where name='张三');
5,经组委会协商，需要删除女子跳高比赛项目
delete from item where itemname='女子跳高';