Oracle学生运动会案例

╰半橙微兮° 2022-06-09 09:50 155阅读 0赞

-- 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 = '张三');