ORACLE经典练习：三表联查：sporter，item，grade

--运动员表
create table sporter(
sporterid number(8) primary key,
sportername varchar2(50) not null,
sex varchar2(10),
department varchar2(50) not null
);

--项目表
create table item(
itemid varchar2(50) primary key,
itemname varchar2(50),
itemloc varchar2(50)
);

--积分表
create table grade(
sporterid  number(8),
itemid varchar2(50),
mark number(8) check(mark in (0,2,4,6)),
constraint grade_sporter_fk foreign key(sporterid) references sporter(sporterid),
constraint grade_item_fk foreign key(itemid) references item(itemid) on delete cascade
);
--on delete cascade 级联删除,删除父表数据时，会自动级联删除子表中的关联数据

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);
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);

commit;

select * from sporter;
select * from item;
select * from grade;

A.求出目前总积分最高的系名，及其积分。
1.求出每个系各自有多少积分
select s.department dp,sum(g.mark) summark
from sporter s,grade g
where s.sporterid=g.sporterid
group by s.department
order by summark desc

2.积分最高的系
select dp,summark
from (select s.department dp,sum(g.mark) summark
        from sporter s,grade g
        where s.sporterid=g.sporterid
        group by s.department
        order by summark desc)
where rownum=1

B.找出在一操场进行比赛的各项目名称及其冠军的姓名。
select i.itemname,s.sportername
from item i,sporter s,grade g
where i.itemid=g.itemid and s.sporterid=g.sporterid
      and i.itemloc='一操场' and g.mark=6

C.找出参加了张三所参加的所有项目的其他同学的姓名。
select s.SPORTERNAME
from sporter s
where s.SPORTERNAME<>'张三'

张三所参加的所有项目
select g.itemid
from sporter s,grade g
where s.SPORTERID=g.SPORTERID and s.SPORTERNAME='张三'

找出参加了x001和x003两个项目的同学的名字(张三除外)
select distinct s.SPORTERNAME
from sporter s,grade g
where s.SPORTERID=g.SPORTERID and s.SPORTERNAME<>'张三'
      and g.ITEMID in (select g.itemid
                        from sporter s,grade g
                        where s.SPORTERID=g.SPORTERID and s.SPORTERNAME='张三')      
      
遇到查询结果有重复，两种办法：
1.第一种办法去重复(group by)
select s.SPORTERNAME
from sporter s,grade g
where s.SPORTERID=g.SPORTERID and s.SPORTERNAME<>'张三'
      and g.ITEMID in ('x001','x003')
group by s.SPORTERNAME

2.第二种办法去重复（distinct）
select distinct s.SPORTERNAME
from sporter s,grade g
where s.SPORTERID=g.SPORTERID and s.SPORTERNAME<>'张三'
      and g.ITEMID in ('x001','x003')
      
select s.SPORTERNAME
from sporter s,grade g
where s.SPORTERID=g.SPORTERID and s.SPORTERNAME<>'张三'
      and g.ITEMID =all('x001','x003')--这里条件永远不能成立
      
如果要求其他同学必须全部参与了张三的项目
select s.SPORTERNAME
from sporter s,grade g
where s.SPORTERID=g.SPORTERID and s.SPORTERNAME<>'张三'
      and g.ITEMID in ('x001','x003')
group by s.SPORTERNAME
having count(s.SPORTERNAME)=(select count(g.itemid)
                                from sporter s,grade g
                                where s.SPORTERID=g.SPORTERID and s.SPORTERNAME='张三')

D.经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。
update grade set mark=0 where sporterid=(select sporterid from sporter where sportername='张三')
commit;
select * from grade;      
      
E.经组委会协商，需要删除女子跳高比赛项目。
delete from item where itemname='女子跳高';

可检验成果：select * from item;select * from grade;