oracle 运动会练习

oracle 运动会练习
有某个学生运动会比赛信息的数据库，保存了如下的表：
运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系号department）
项目item（项目编号itemid，项目名称itemname，项目比赛地点location）
成绩grade（运动员编号sporterid，项目编号itemid，积分mark）
请用SQL语句完成如下功能：
1. 建表，注意满足如下要求：
 a) 定义各个表的主码外码约束
 b) 运动员的姓名和所属系别不能为空值。
 c) 积分要么为空值，要么为6,4,2,0, 分别代表第一，二，三名和其他名次的积分，注意名次可以有并列名次，后面的排名不往前提升，例如，如果有两个并列第一，则没有第二名。

--删除表
drop table grade;
drop table item;
drop table sporter;

--运动员
create table sporter(
sporterid number(4) primary key,
name varchar2(20) not null, --b) 运动员的姓名不能为空值。
sex char(2),
department varchar2(20) not null -- b) 运动员所属系别不能为空值。
);

--项目
create table item(
itemid char(4) primary key,
itemname varchar2(20),
location varchar2(20)
);

--成绩
create table grade(
sporterid number(4) references sporter(sporterid),
itemid char(4) references item(itemid),
mark number(1),
primary key (sporterid,itemid)
);

 

2. 往表中插入数据：
运动员（
   1001, 李明，男，计算机系
   1002，张三，男，数学系
   1003, 李四，男，计算机系
   1004, 王二，男，物理系
   1005, 李娜，女，心理系
   1006, 孙丽，女，数学系）
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,'孙丽','女','数学系');

项目（
   x001, 男子五千米，一操场
   x002，男子标枪，一操场
   x003, 男子跳远，二操场
   x004, 女子跳高，二操场
   x005, 女子三千米，三操场）
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','女子三千米','三操场');

积分（  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)
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);

3. 完成如下查询
a) 求出目前总积分最高的系名，及其积分。

 

b) 找出在一操场进行比赛的各项目名称及其冠军的姓名

c) 找出参加了张三所参加的所有项目的其他同学的姓名

4. 经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。

5. 经组委会协商，需要删除女子跳高比赛项目。