有某个学生运动会比赛信息的数据库,保存了如下的表:
请用SQL语句完成如下功能: 1、 建表,并在相应字段上增加约束;
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、 增加运动员数据: NSERT 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、确定所要使用的数据表:
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、确定所要使用的数据表:
2、确定已知的关联字段:
第一步:确定一操场进行的项目的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、确定所要使用的数据表:
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='女子跳高'; (责任编辑:IT) |