Oracle的关于建表，约束，查询等的练习

从建立一个简单表，到实现一些复杂查询的例子


DROP TABLE grade;
DROP TABLE item;
DROP TABLE sporter;
CREATE TABLE sporter(
 sporterid NUMBER(4) PRIMARY KEY NOT NULL,
 name  VARCHAR(50) NOT NULL,
 sex  VARCHAR(2) NOT NULL,
 department VARCHAR(30) NOT NULL,
 CONSTRAINT sporter_sex_CK CHECK(sex IN('男','女'))
);
 
 
 
CREATE TABLE item(
 itemid  VARCHAR(4) PRIMARY KEY NOT NULL,
 itemname VARCHAR(50) NOT NULL,
 location VARCHAR(50) NOT NULL
);
 
CREATE TABLE grade(
 sporterid NUMBER(4),
 itemid  VARCHAR(4),
 mark  NUMBER(2),
 CONSTRAINT sport_grade_sporterid_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
 CONSTRAINT item_grade_sporterid_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
 CONSTRAINT grade_mark_CK CHECK(mark IN (6,4,2,0))
);
 
-- 测试数据
 
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);


要求：


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


·所有的系名都在SPORTERID表之中
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
;
 
·不使用ROWNUM
 ·求出最大的分数值
 SELECT MAX(SUM(g.mark)) max
 FROM sporter s,grade g
 WHERE s.sporterid=g.sporterid
 GROUP BY s.department
 ·把上面的最大分数值作为子查询作为子查询
 SELECT t.* FROM (
  SELECT s.department de,SUM(g.mark) sum
  FROM sporter s,grade g
  WHERE s.sporterid=g.sporterid
  GROUP BY s.department
  ORDER BY sum DESC) t
 WHERE t.sum=(
  SELECT MAX(SUM(g.mark)) max
  FROM sporter s,grade g
  WHERE s.sporterid=g.sporterid
  GROUP BY s.department) ;


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


·求出最高成绩的 项目ID 和 分数 
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 IN(
 SELECT MAX(mark) FROM (
 SELECT i.itemname itemname,s.name name,g.mark mark
 FROM item i,grade g,sporter s
 WHERE i.location='二操场'
 AND i.itemid=g.itemid
 AND s.sporterid=g.sporterid
 )GROUP BY itemname
);


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


SELECT DISTINCT s.name FROM sporter s,grade g 
WHERE s.sporterid=g.sporterid
AND s.name<>'张三'
AND g.itemid IN (
 SELECT g.itemid
 FROM sporter s,grade g
 WHERE s.sporterid=g.sporterid
 AND s.name='张三');


4.经查张三因为使用了违禁药品，其成绩都记为0分，做出修改


UPDATE grade SET mark=0
WHERE sporterid=(
 SELECT sporterid FROM sporter WHERE name='张三'
);


5.取消山粗女子跳高比赛项目


DELETE FROM item WHERE itemname='女子跳高';