Oracle学生运动会案例

-- 1.有某个学生运动会比赛信息的数据库，保存了如下的表：
-- 运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系号department）
-- 项目item（项目编号itemid，项目名称itemname，项目比赛地点location）
-- 成绩grade（运动员编号sporterid，项目编号itemid，积分mark）

CREATE TABLE sporter (
  sporterid  NUMBER(4),
  name       VARCHAR2(18) NOT NULL,
  sex        VARCHAR2(3)  NOT NULL,
  department VARCHAR2(20) NOT NULL,
  CONSTRAINT pk_sporter_sporterid PRIMARY KEY (sporterid)
);

CREATE TABLE item (
  itemid   VARCHAR2(6),
  itemname VARCHAR2(20) NOT NULL,
  location VARCHAR2(30) NOT NULL,
  CONSTRAINT pk_item_itemid PRIMARY KEY (itemid)

);

CREATE TABLE grade (
  sporterid NUMBER(4),
  itemid    VARCHAR2(6),
  mark      NUMBER(1) DEFAULT 0,
  CONSTRAINT fk_grade_sporterid FOREIGN KEY (sporterid) REFERENCES sporter (sporterid) ON DELETE CASCADE,
  CONSTRAINT fk_grade_itemid FOREIGN KEY (itemid) REFERENCES item (itemid) ON DELETE SET NULL,
  CONSTRAINT ch_grade_mark CHECK (mark IN (6, 4, 2, 0))
);
--注：ON DELETE CASCADE 表示主表的数据删除时，从表的数据也跟着删除；ON DELETE SET NULL 表示主表的数据删除时，从表关联的数据字段设置为NULL
-- 2.请用SQL语句完成如下功能：
-- 建表，并在相应字段上增加约束；
-- 定义各个表的主键和外键约束；
-- 积分要么为空值，要么为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
-- 1003,  x002, 6
-- 1005 ,  x002,4
-- 1006,  x002,2
-- 1001,  x002,0)

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, '孙俪', '女', '数学系');

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', '女子三千米', '二操场');

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);
INSERT INTO grade (sporterid, itemid, mark) VALUES (1001, 'x002', 0);

COMMIT;
-- 3.完成如下的查询要求：
-- A、 求出目前总积分最高的系名，及其积分。
--方法一
--1.按照系名分组查询最高分
SELECT max(sum(g.mark)) su
FROM sporter s, grade g
WHERE s.sporterid = g.sporterid
GROUP BY s.department;

--2.按照系名分组查询各系的分组和系名
SELECT
  sum(g.mark) su,
  s.dname     dn
FROM sporter s, grade g
WHERE s.sporterid = g.sporterid
GROUP BY s.department;

--3.查询（当分数之和等于最高分时确定该系为分数最高的系）
SELECT
  t1.dn,
  t2.su
FROM (SELECT
        sum(g.mark) su,
        s.dname     dn
      FROM sporter s, grade g
      WHERE s.sporterid = g.sporterid
      GROUP BY s.dname
     ) t1, (SELECT max(sum(g.mark)) su
            FROM sporter s, grade g
            WHERE s.sporterid = g.sporterid
            GROUP BY s.dname
           ) t2

WHERE t1.su = t2.su;

-- 方法二（按照系名分组查询各系的分数总和和系名，然后降序排列，最后取出用伪列取出第一条数据）
SELECT
  sum(g.mark) su,
  s.department
FROM grade g, sporter s
WHERE g.sporterid = s.sporterid AND ROWNUM = 1
GROUP BY s.department
ORDER BY su DESC;

-- B、找出在一操场进行比赛的各项目名称及其冠军的姓名。
-- 1.得到一操场进行比赛的各项目编号

SELECT itemid
FROM item
WHERE LOCATION = '一操场';

--2.得到一操场进行比赛的各项目最高分和项目的编号，和项目名称（按照项目编号和项目名称分组）
SELECT DISTINCT
  i.itemid,
  i.itemname,
  max(g.mark)
FROM grade g, item i
WHERE g.itemid IN (SELECT itemid
                   FROM item
                   WHERE LOCATION = '一操场') AND i.itemid = g.itemid
GROUP BY i.itemid, i.itemname;

--3.多表联合查询（注意消除笛卡尔积）项目和，和
SELECT
  temp.iname 项目名称,
  s.name     冠军姓名,
  temp.m     得分
FROM grade g, sporter s, item i2, (SELECT DISTINCT
                                     i.itemid    iid,
                                     i.itemname  iname,
                                     max(g.mark) m
                                   FROM grade g, item i
                                   WHERE g.itemid IN (SELECT itemid
                                                      FROM item
                                                      WHERE LOCATION = '一操场') AND i.itemid = g.itemid
                                   GROUP BY i.itemid, i.itemname) temp
WHERE s.sporterid = g.sporterid AND temp.iid = g.itemid AND i2.itemid = temp.iid AND g.itemid = i2.itemid AND
      g.mark = temp.m;

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

DELETE FROM item
WHERE itemname = '女子跳高';

-- C、找出参加了张三所参加的所有项目的其他同学的姓名
--1.张三的编号
SELECT sporterid
FROM sporter
WHERE name = '张三';

--3.张三参加的项目
SELECT itemid
FROM grade
WHERE sporterid = (SELECT sporterid
                   FROM sporter
                   WHERE name = '张三');

--4.参加张三项目的同学的编号
SELECT DISTINCT sporterid
FROM grade
WHERE itemid IN (SELECT itemid
                 FROM grade
                 WHERE sporterid = (SELECT sporterid
                                    FROM sporter
                                    WHERE name = '张三'));
--5.根据4查询结果开始查询
SELECT sp.name
FROM sporter sp
WHERE sporterid IN (SELECT DISTINCT sporterid
                    FROM grade
                    WHERE itemid IN (SELECT itemid
                                     FROM grade
                                     WHERE sporterid = (SELECT sporterid
                                                        FROM sporter
                                                        WHERE name = '张三')));

-- D、经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。
UPDATE grade
SET mark = 0
WHERE sporterid = (SELECT sporterid
                   FROM sporter
                   WHERE name = '张三');