Oracle笔记（十一）建表、更新、查询综合练习(二)
nt) 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、 增加运动员的项目成绩
www.2cto.com
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;
做法一：不考虑相同积分的问题，所有的数据由高到低降序排列，取第一个数据。
www.2cto.com
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、确定已知的关联字段： www.2cto.com
运动员和成绩：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表
www.2cto.com
SELECT s.name,g