Oracle基础知识笔记（11）建表、更新、查询综合练习 - 数据库编程

有某个学生运动会比赛信息的数据库，保存了如下的表：
运动员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、经组委会协商，需要删除女子调高比赛项目。

一、建表

DROP TABLE grade PURGE;
DROP TABLE sporter PURGE;
DROP TABLE item PURGE;
CREATE TABLE sporter (
    sporterid NUMBER(4)     PRIMARY KEY,
    name                 VARCHAR2(20)         NOT NULL,
    sex            VARCHAR2(10)         CHECK (sex IN('男','女')),
    department     VARCHAR2(20)         NOT NULL
);
CREATE TABLE item (
    itemid                VARCHAR2(4)  PRIMARY KEY,
    itemname         VARCHAR2(20)         NOT NULL,
    location    VARCHAR2(20)         NOT NULL
);
CREATE TABLE grade (
    sporterid NUMBER(4)     REFERENCES sporter(sporterid) ON DELETE CASCADE,
    itemid                VARCHAR2(4)  REFERENCES item(itemid) ON DELETE CASCADE,
    mark                  NUMBER(1)     CHECK (mark IN (0,2,4,6))
);

而且在Oracle之中要考虑回收站的问题。

二、增加数据

1、增加运动员数据：

INSERT INTO sporter(sporterid,name,sex,department) VALUES (1001,'李明','男','计算机系');
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1002,'张三','男','数学系');
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1003,'李四','男','计算机系');
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1004,'王二','男','物理系');
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1005,'李娜','女','心理系');
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1006,'孙丽','女','数学系');

2、增加项目数据

INSERT INTO item(itemid,itemname,location) VALUES ('x001','男子五千米','一操场');
INSERT INTO item(itemid,itemname,location) VALUES ('x002','男子标枪','一操场');
INSERT INTO item(itemid,itemname,location) VALUES ('x003','男子跳远','二操场');
INSERT INTO item(itemid,itemname,location) VALUES ('x004','女子跳高','二操场');
INSERT INTO item(itemid,itemname,location) VALUES ('x005','女子三千米','三操场');

3、增加运动员的项目成绩

INSERT INTO grade(sporterid,itemid,mark) VALUES (1001,'x001',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1002,'x001',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1003,'x001',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1004,'x001',0);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1001,'x003',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1002,'x003',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1004,'x003',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1005,'x004',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1006,'x004',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1003,'x002',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1005,'x002',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES (1006,'x002',2);
INS

Oracle基础知识笔记（11）建表、更新、查询综合练习(一)