有某个学生运动会比赛信息的数据,保存了如下表:
运动员 sporter (运动员编号 sporterid , 运动员姓名 name , 运动员性别 sex , 所属系号 department)
项目 item (项目编号 itemid , 项目名称 itemname , 项目比赛地点 location)
成绩 grade (运动员编号 id , 项目编号 itemid , 积分 mark)
请用 SQL 语句完成如下功能:
1、建表,并在相应字段上增加约束;
| - 定义各表的主键个外键约束;
| - 运动员的姓名和所属系别不能为空
| - 积分要么为空值,要么为 6,4,2,0, 分别代表第一,二,三名和其他名次的积分,注意名次可以有并列名次,后面的排名不往前提升,例如,如果有两个并列第一,则没有第二名。
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
1005, x004, 6
1006, x004, 4
1003, x002, 6
1005 , x002,4
1006, x002,2
1001, x002,0)
3、完成如下查询要求:
A、 求出目前积分最高的惜命,及其积分。
B、 找出在以操场进行比赛的各项目名称及其冠军的姓名。
C、 找出参加了张三所参加的所有项目的其他同学的姓名。
D、 经查张三因为使用了违禁药品,其成绩记0分,请在数据库中做出相应修改。
E、 经组委会协商,需要删除女子跳高比赛项目。
1、建表:
建表的时候一定需要存在约束,同时按照数据库脚本的创建标准来完成。
// 删除数据表
DROP TABLE grade PURGE;
DROP TABLE item PURGE;
DROP TABLE sporter PURGE;
// 创建数据表
CREATE TABLE sporter(
sporterid NUMBER(4) ,
name VARCHAR2(20) NOT NULL ,
sex VARCHAR2(10) ,
department VARCHAR2(20) NOT NULL ,
CONSTRAINT pk_sporterid PRIMARY KEY(sporterid) ,
CONSTRAINT ck_sex CHECK(sex IN('男','女'))
);
CREATE TABLE item(
itemid VARCHAR2(4) ,
itemname VARCHAR2(20) NOT NULL ,
location VARCHAR2(20) NOT NULL ,
CONSTRAINT pk_item PRIMARY KEY(itemid)
);
CREATE TABLE grade(
sporterid NUMBER(4) ,
itemid VARCHAR2(2) ,
mark NUMBER(1) ,
CONSTRAINT fk_sporterid FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE ,
CONSTRAINT fk_itemid FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE