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

三、數據操作

1、 求出目前總積分最高的系名，及其積分。

1、確定所要使用的數據表：

sporter表：求出系名稱；
grade表：找到積分；

2、確定已知的關聯字段：sporter.sporterid=grade.sporterid；

第一步：將sporter表和grade表聯合

SELECT s.department,g.mark
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid;

第二步：以上是求出每個系針對於項目獲得的積分，那麼下面將以上的查詢分組，按照系名稱分組。

SELECT s.department,SUM(g.mark)
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department;

此時已經知道各個系的成績了，那麼對於求出總積分最高的信息，有兩種做法：

做法一：不考慮相同積分的問題，所有的數據由高到低降序排列，取第一個數據。

SELECT * FROM (
    SELECT s.department,SUM(g.mark) sum
    FROM sporter s,grade g
    WHERE s.sporterid=g.sporterid
    GROUP BY s.department
    ORDER BY sum DESC)
WHERE ROWNUM=1;

做法二：考慮相同積分的問題，則必須首先進行分組函數的嵌套，求出最高的積分是多少，而後再用此內容與之前的分組進行過濾。

SELECT s.department,SUM(g.mark) sum
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department
HAVING SUM(g.mark)=(
    SELECT MAX(SUM(g.mark)) sum
    FROM sporter s,grade g
    WHERE s.sporterid=g.sporterid
    GROUP BY s.department);

2、 找出在一操場進行比賽的各項目名稱及其冠軍的姓名。

1、確定所要使用的數據表：

sporter表：運動員的姓名；
item表：項目名稱；
grade表：冠軍的信息依靠成績計算；

2、確定已知的關聯字段：

運動員和成績：sporter.sporterid=grade.sporterid；
項目和成績：item.itemid=grade.itemid；

第一步：確定一操場進行的項目的ID

SELECT itemid FROM item WHERE location='一操場';

第二步：求出冠軍的成績，因為各個項目有各個項目的冠軍分數

SELECT i.itemid,MAX(g.mark) max
FROM item i,grade g
WHERE i.location='一操場' AND i.itemid=g.itemid
GROUP BY i.itemid;

第三步：要根據這個成績，找到對應的運動員姓名

SELECT s.name,g.itemid,temp.max
FROM sporter s,grade g,(
    SELECT i.itemid iid,MAX(g.mark) max
    FROM item i,grade g
    WHERE i.location='一操場' AND i.itemid=g.itemid
    GROUP BY i.itemid) temp
WHERE s.sporterid=g.sporterid AND temp.iid=g.itemid AND g.mark=temp.max;

第四步：找到項目名稱，引入item表

SELECT s.name,g.itemid,temp.max,i.itemname
FROM sporter s,grade g,(
SELECT i.itemid iid,MAX(g.mark) max
FROM item i,grade g
WHERE i.location='一操場' AND i.itemid=g.itemid
GROUP BY i.itemid) temp,item i
WHERE s.sporterid=g.sporterid AND temp.iid=g.itemid AND g.mark=temp.max
AND g.itemid=i.itemid AND temp.iid=i.itemid;

3、 找出參加了張三所參加的所有項目的其他同學的姓名。

1、確定所要使用的數據表：

sporter表：張三的運動員ID；
grade表：根據它找到項目的ID；
sporterid：根據grade表和之前的sporter表確定出的項目ID，找到運動員的編號及姓名；

2、確定已知的關聯字段：sporter.sporterid=grade.sporterid；

第一步：確定張三的運動員編號

SELECT s.sporterid
FROM sporter s
WHERE s.name='張三';

第二步：根據運動員的編號，找到參加的項目的編號

SELECT g.itemid
FROM grade g
WHERE g.sporterid=(
    SELECT s.sporterid
    FROM sporter s
    WHERE s.name='張三');

第三步：修改以上的查詢，找到所有的運動員的編號

SELECT g.sporterid
FROM grade g
WHERE g.itemid IN(
    SELECT g.itemid
    FROM grade g
    WHERE g.sporterid=(
        SELECT s.sporterid
        FROM sporter s
        WHERE s.name='張三'));

第四步：根據運動員的編號找到運動員的姓名

SELECT name
FROM sporter
WHERE sporterid IN(
    SELECT g.sporterid
    FROM grade g
    WHERE g.itemid IN(
        SELECT g.itemid
        FROM grade g
        WHERE g.sporterid=(
            SELECT s.sporterid
            FROM sporter s
            WHERE s.name='張三')))
    AND name<>'張三';

4、 經查張三因為使用了違禁藥品，其成績都記0分，請在數據庫中作出相應修改。

現在只是知道了張三的姓名，卻不知道運動員編號，而成績需要在grade表中通過運動員編號更新。

UPDATE grade SET mark=0 WHERE sporterid=(
    SELECT sporterid FROM sporter WHERE name='張三');

5、 經組委會協商，需要刪除女子跳高比賽項目。

項目刪除之後對應的成績也應該消失，而在之前已經配置了級聯刪除了，所以直接刪除父表即可。

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