建表、更新、查询综合练习
有某个学生运动会比赛信息的数据,保存了如下表:
请用 SQL 语句完成如下功能: 运动员( 1001,李明,男,计算机系 1002,张三,男,数学系 1003,李四,男,计算机系 1004,王二,男,物理系 1005,李娜,女,心理系 1006,孙丽,女,数学系) 项目( x001,男子五千米,一操场 x002,男子标枪,一操场 x003,男子跳远,二操场 x004,女子跳高,二操场 x005,女子三千米,三操场) 积分( 1001,x001,6 1002,4 1003,2 1004,0 1001,x003,4 1002,6 1004,2 1005,x004,6 1006,4 1003,x002,6 1005,4 1006,2 1001,0) 3、完成如下查询要求: 1、建表:建表的时候一定需要存在约束,同时按照数据库脚本的创建标准来完成。 // 删除数据表
DROP TABLE grade PURGE;
DROP TABLE item PURGE;
DROP TABLE sporter PURGE;
// 创建数据表
CREATE TABLE sporter( sporterid NUMBER(4),name VARCHAR2(20) NOT NULL,sex VARCHAR2(10),department VARCHAR2(20) NOT NULL,CONSTRAINT pk_sporterid PRIMARY KEY(sporterid),CONSTRAINT ck_sex CHECK(sex IN('男','女')) );
CREATE TABLE item( itemid VARCHAR2(4),itemname VARCHAR2(20) NOT NULL,location VARCHAR2(20) NOT NULL,CONSTRAINT pk_item PRIMARY KEY(itemid) );
CREATE TABLE grade( sporterid NUMBER(4),itemid VARCHAR2(2),mark NUMBER(1),CONSTRAINT fk_sporterid FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,CONSTRAINT fk_itemid FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,CONSTRAINT ck_mark CHECK(mark IN(0,2,4,6)) );
2、增加数据:// 增加运动员数据
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1001,'李明','男','计算机系') ;
INSERT INTO sporter(sporterid,department) VALUES (1002,'张三','数学系') ;
INSERT INTO sporter(sporterid,department) VALUES (1003,'李四',department) VALUES (1004,'王二','物理系') ;
INSERT INTO sporter(sporterid,department) VALUES (1005,'李娜','女','心理系') ;
INSERT INTO sporter(sporterid,department) VALUES (1006,'孙丽','数学系') ;
// 增加项目数据
INSERT INTO item(itemid,itemname,location) VALUES ('x001','男子五千米','一操场') ;
INSERT INTO item(itemid,location) VALUES ('x002','男子标枪',location) VALUES ('x003','男子跳远','二操场') ;
INSERT INTO item(itemid,location) VALUES ('x004','女子跳高',location) VALUES ('x005','女子三千米','三操场') ;
// 增加成绩数据
INSERT INTO grade(sporterid,itemid,mark) VALUES (1001,'x001',6) ;
INSERT INTO grade(sporterid,mark) VALUES (1002,4) ;
INSERT INTO grade(sporterid,mark) VALUES (1003,2) ;
INSERT INTO grade(sporterid,mark) VALUES (1004,0) ;
INSERT INTO grade(sporterid,'x003',mark) VALUES (1005,'x004',mark) VALUES (1006,'x002',0) ;
// 提交事物
COMMIT ;
2、数据操作:1、 求出目前总积分最高的系名,及其积分 第一步:首先列出各个系的积分,直接使用sporter表和grade表进行多表查询即可。 select s.department,g.mark FROM sporter s,grade g WHERE s.sporterid=g.sporterid;
第二步:此时已经出现了重复数据,那么自然可以分组,按照系分组,求出每个系的总分,使用SUM()函数完成。 select s.department,SUM(g.mark) FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department;
第三步:先抛开最终的问题不谈,如果按照以上的查询,要想求出最高的总积分,肯定使用统计函数嵌套,但是统计函数嵌套有一个要求:不允许出现任何的分组字段。 SELECT MAX(SUM(g.mark)) FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department ;
第四步:以上的结果返回的是一个单行单列,而这个值是可以唯一筛选出第二步之中最高分的重要数据,证明现在应该要对第二步分组后的数据执行过滤,使用HAVING子句 select s.department,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department HAVING SUM(g.mark)=( select MAX(SUM(g.mark)) max FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department) ;
· 确定需要使用的数据表: SELECT itemid FROM item WHERE location='一操场';
第二步:知道了项目的编号,那么就应该确定出每个项目的最高成绩是多少,最高成绩 = 冠军。 SELECT itemid,MAX(mark) FROM grade WHERE itemid IN( SELECT itemid FROM item WHERE location='一操场') GROUP BY itemid;
第三步:以上的结果返回的是多行多列,可以按照表使用,如果说现在要找到每一个参加项目的运动员的名字、成绩、项目名称,直接让sporter、item和grade关联(未使用到第二步) SELECT s.name,i.itemname,g.mark
FROM sporter s,grade g,item i
WHERE s.sporterid=g.sporterid AND g.itemid=i.itemid ;
第四步:将第二步取得的信息和第三步结合在一起 SELECT s.name,g.mark,i.itemname,temp.max FROM sporter s,item i,( SELECT itemid iid,MAX(mark) max FROM grade WHERE itemid IN( SELECT itemid FROM item WHERE location='一操场') GROUP BY itemid) temp WHERE s.sporterid=g.sporterid AND i.itemid=g.itemid AND i.itemid=temp.iid AND g.mark=temp.max;
第一步:要想知道张三参加的项目是那个,那么必须首先知道张三的编号,因为有了编号才可以通过grade表查看有那些项目 SELECT sporterid FROM sporter WHERE name='张三';
第二步:根据编号找到张三所参加过的项目编号,grade表中存在 SELECT itemid FROM grade WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三');
第三步:知道了项目的编号,那么可以继续利用grade表找到那些运动员参加了此项目 SELECT sporterid FROM grade WHERE itemid IN( SELECT itemid FROM grade WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三'));
第四步:根据运动员的编号找到运动员的姓名 SELECT name FROM sporter WHERE sporterid IN( SELECT sporterid FROM grade WHERE itemid IN( SELECT itemid FROM grade WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三'))) AND name<>'张三';
UPDATE grade SET mark=0 WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三') ;
DELETE FROM item WHERE itemname='女子跳高' ; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |