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

转载 2017年10月13日 14:42:36
从建立一个简单表,到实现一些复杂查询的例子


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='女子跳高';

Oracle SQL题目及其解答(学生、课程、成绩、教师)

题目来源于http://blog.csdn.net/lifetragedy/article/details/9935699,由于原文题目中的数据在我自己建的DB里没有数据,所以解答中的条件可能是符合我...
  • tu451953337
  • tu451953337
  • 2015年04月20日 11:13
  • 24317

超经典SQL练习题,做完这些你的SQL就过关了

SQL 联系题 使用方法:我用的数据库是 Ms SQL Server 2008 ,练习时应当自己建数据,自己先思考,切勿急躁翻答案!否则效果减半,做完这些,恭喜你,你的 SQL 就算过关了。...
  • flycat296
  • flycat296
  • 2017年03月19日 10:19
  • 3668

CSDN博客的积分计算方法和博客排名规律

开通博客一段时间了,最近莫名其妙得获得“持之以恒”的勋章,看着日益增长的积分,既兴奋又好奇,本人对CSDN博客积分的计算方法很疑惑,也不知其中怎么回事,好奇度娘一番,并结合CSDN的一篇博客《csdn...
  • yang_best
  • yang_best
  • 2014年11月19日 12:51
  • 2037

对oracle当中子查询建表,merge操作,创建,修改,删除约束,创建使用触发器的复习练习

Sql代码 /** 对oracle当中子查询建表,merge操作,创建,修改,删除约束,创建使用触发器的复习练习**/  /**    本例子的作用是熟悉使用oracle当中的触发器:有两...
  • dingbo365
  • dingbo365
  • 2012年04月29日 19:51
  • 596

Oracle11g简单建表(约束)、查询、修改脚本

记录一下简单的创建和约束脚本,还有几个一般查询的语句 等以后熟练了再来改进 题目源于“MLDN魔乐科技_Oracle课堂20_完整练习.rar”,大家应该能电驴到 发的东西和课程里讲的答...
  • ruantao1989
  • ruantao1989
  • 2012年09月16日 23:49
  • 1899

oracle笔记, 建表,修改,索引,约束

--创建一张空表 CREATE TABLE department( deptNo NUMBER(2) NOT NULL, deptName VARCHAR2(20) NOT NULL, ...
  • u011847748
  • u011847748
  • 2014年08月11日 21:03
  • 289

oracle 建表 约束 constraint

约束命名规则        如果不指定约束名Oracle server 自动按照SYS_Cn 的格式指定约束名,也可手动指定,        推荐的约束命名是:约束类型_表名_列名。      ...
  • y112102
  • y112102
  • 2011年10月08日 11:22
  • 6133

Oralce学习总结--oracle建表和约束

一.概念:       DBMS -- DataBase Management System       DBMS 是一种操纵和管理数据库的大型软件,是用于建立、使用和维护数据库        ...
  • qq_35192224
  • qq_35192224
  • 2017年05月21日 15:13
  • 232

Oracle笔记(十一) 建表、更新、查询综合练习

有某个学生运动会比赛信息的数据库,保存了如下的表: 运动员sporter(运动员编号sporterid,运动员姓名name,运动员性别sex,所属系号department)项目item(项目编号it...
  • u012141686
  • u012141686
  • 2013年11月18日 09:04
  • 1014

T-SQL语句:建库,建表,建约束,简单编程, 各种查询,事务,视图,索引,存储过程···

use master go --允许数据中的调用DOS命令 exec sp_configure 'show advanced options' , 1 go reconfigure go exec...
  • fendoudexiaomao
  • fendoudexiaomao
  • 2014年04月04日 16:34
  • 2310
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle的关于建表,约束,查询等的练习
举报原因:
原因补充:

(最多只允许输入30个字)