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

ERT 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='女子跳高';

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