Oracle基础知识笔记(11) 建表、更新、查询综合练习
来源:互联网 发布:网络课程有哪些 编辑:程序博客网 时间:2024/04/30 11:18
有某个学生运动会比赛信息的数据库,保存了如下的表:
- 运动员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.markFROM sporter s,grade gWHERE s.sporterid=g.sporterid;
第二步:以上是求出每个系针对于项目获得的积分,那么下面将以上的查询分组,按照系名称分组。
SELECT s.department,SUM(g.mark)FROM sporter s,grade gWHERE s.sporterid=g.sporteridGROUP 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) sumFROM sporter s,grade gWHERE s.sporterid=g.sporteridGROUP BY s.departmentHAVING 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) maxFROM item i,grade gWHERE i.location='一操场' AND i.itemid=g.itemidGROUP BY i.itemid;
第三步:要根据这个成绩,找到对应的运动员姓名
SELECT s.name,g.itemid,temp.maxFROM 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) tempWHERE s.sporterid=g.sporterid AND temp.iid=g.itemid AND g.mark=temp.max;
第四步:找到项目名称,引入item表
SELECT s.name,g.itemid,temp.max,i.itemnameFROM sporter s,grade g,(SELECT i.itemid iid,MAX(g.mark) maxFROM item i,grade gWHERE i.location='一操场' AND i.itemid=g.itemidGROUP BY i.itemid) temp,item iWHERE s.sporterid=g.sporterid AND temp.iid=g.itemid AND g.mark=temp.maxAND 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.sporteridFROM sporter sWHERE s.name='张三';
第二步:根据运动员的编号,找到参加的项目的编号
SELECT g.itemidFROM grade gWHERE g.sporterid=( SELECT s.sporterid FROM sporter s WHERE s.name='张三');
第三步:修改以上的查询,找到所有的运动员的编号
SELECT g.sporteridFROM grade gWHERE g.itemid IN( SELECT g.itemid FROM grade g WHERE g.sporterid=( SELECT s.sporterid FROM sporter s WHERE s.name='张三'));
第四步:根据运动员的编号找到运动员的姓名
SELECT nameFROM sporterWHERE 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) 建表、更新、查询综合练习
- Oracle学习笔记(11)----------建表、更新、查询综合练习
- Oracle学习笔记(11)----------建表、更新、查询综合练习
- Oracle笔记(十一) 建表、更新、查询综合练习
- Oracle笔记(十一) 建表、更新、查询综合练习
- Oracle笔记(十一) 建表、更新、查询综合练习
- Oracle笔记(十一) 建表、更新、查询综合练习
- Oracle笔记十一: 建表、更新、查询综合练习
- 建表、更新、查询综合练习
- Oracle的综合查询练习
- Oracle基础知识3 : 单表查询练习
- Oracle基础知识3 : 单表查询练习
- 数据库实战完全笔记综合练习(1)授课信息查询
- Oracle基础知识笔记(6) 多表查询
- Oracle基础知识笔记(8) 复杂查询及总结
- (05)oracle新人笔记------子查询练习(重点)
- 数据库学习笔记和小练习(6)sql查询和更新练习
- Oracle之综合查询
- Python内使用OpenCV显示图像
- Android中为textView添加边框
- C#解析JSON
- String,StringBuffer与StringBuilder的区别??
- 71 Java 枚举类(2)
- Oracle基础知识笔记(11) 建表、更新、查询综合练习
- 习近平在沪会见国外元首 国宾礼炮首次出京鸣放
- oracle获取周几以及每周对应得开始日期和结束日期
- 最全的常用正则表达式大全分享
- 测试题4
- ios nil、NULL和NSNull 的使用
- leetcode -day19 Convert Sorted List to Binary Search Tree
- 生产者消费者模型c++版本
- 中国消费者信心指数及消费趋势解读报告