Oracle从零开始13——表的管理03——ROWNUM与完整练习

3.ROWNUM(重点)

表示行号，实际上这是一个列，但是是一个伪列，此列可以在每张表中出现。

范例：在查询雇员表上，加入ROWNUM

    从运行结果看，ROWNUM采用自动编号的形式出现

范例：只想显示前5条记录

范例：查询中间5条记录

ROWNUM没有这样的功能，这个查询只能使用子查询

范例：要求每页显示5条，第二页应该显士6-10条，那么对于数据库操作来讲，它在查询应该首先查询出1-10条，之后再在查询的结果中取出后5条。

范例：输出最后的四条。

4.综合练习

1）题目背景

某个学生运动会比赛信息的数据库，保存了如下的表：

运动员sporter（运动员编号sporterid，运动员姓名name，性别sex，所属系号department）

项目item（项目编号itemid，项目名称itemname，项目比赛地点location）

成绩grade（运动员编号sporterid，项目编号itemid，积分mark）

2）功能要求

a）建表

要求：定义各个表的主键外键约束

运动员的姓名和所属系别不能为空值

积分要么为控制，要么为6，4，2，0，分别代表第一、二、三名和其他名次的积分。

完整的脚本还要在建表语句之前加上：

--先删子表，再删主表

DROP TABLE grade;

DROP TABLE sporter;

DROP TABLE item;

b）数据

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);

c）查询要求

1. 求出目前总积分最高的系名，及其积分

   第一步：求出所有的系名及其积分，系名都在sporter表中，所以要与grade表关联

   

   第二步：降序排序

   

   第三步（1）：使用ROWNUM，使用子查询，求得最高的积分的系

   

   第三步（2）：如果不使用ROWNUM，第三步如何做？

   

2. 找出在一操场进行比赛的各项目名称及其冠军的姓名

   第一步：item表找出项目名称和每个项目的最高成绩

   SELECT i.itemname,s.name,g.mark

   FROM item i,grade g,sporter s

   WHERE i.location='一操场' AND i.itemid=g.itemid

               AND s.sporterid=g.sporterid;

   第二步：根据以上结果求最高分

   SELECT i.itemname,s.name,g.mark

   FROM item i,grade g,sporter s

   WHERE i.location='一操场' AND i.itemid=g.itemid

               AND s.sporterid=g.sporterid AND g.mark=6;

3. 找出参加了张三所参加过的项目的其他同学的姓名

   第一步：找出张三参加过的项目

   

   

   第二步：找出这些项目的参加的同学的姓名，除过张三

   

4. 经查张三因为使用了违禁药品，其成绩都记零分，请在数据库中作出相应修改

   确定张三的运动员编号，依此编号作为更新的条件

   

5. 经组委会写上，需要删除女子跳高比赛项目

   执行delete语句即可