首页 > 数据库 > Oracle > 正文
Oracle基础知识笔记(11)建表、更新、查询综合练习
2014-05-20       个评论    来源: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='女子跳高';
点击复制链接 与好友分享!回本站首页
相关TAG标签 基础知识 笔记
上一篇:在Oracle11.2.0.1.0下dbms_stats.gather_table_stats收集直方图不准
下一篇:Oracle启动和关闭服务
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站