Oracle11g简单建表（约束）、查询、修改脚本

记录一下简单的创建和约束脚本，还有几个一般查询的语句

等以后熟练了再来改进

题目源于“MLDN魔乐科技_Oracle课堂20_完整练习.rar”,大家应该能电驴到

发的东西和课程里讲的答案不一样，自己瞎写的 不过功能无误 

DROP TABLE grade;
DROP TABLE sporter;
DROP TABLE item;
CREATE TABLE sporter
(
	sporterid NUMBER(9),
	name VARCHAR2(50) NOT NULL,
	sex VARCHAR2(3) NOT NULL,
	department VARCHAR2(30) NOT NULL,
	CONSTRAINT sporter_sporterid_pk PRIMARY KEY(sporterid),
	CONSTRAINT sporter_sex_ck CHECK(sex IN ('男','女','中'))
);
CREATE TABLE item
(
	itemid VARCHAR2(9),
	itemname VARCHAR2(50) NOT NULL,
	location VARCHAR2(50) NOT NULL,
	CONSTRAINT item_itemid_pk PRIMARY KEY(itemid)
);
CREATE TABLE grade
(
	sporterid NUMBER(9),
	itemid VARCHAR2(9),
	mark NUMBER(9) NOT NULL,
	--CONSTRAINT grade_sporterid_pk PRIMARY KEY(sporterid),
	CONSTRAINT grade_mark_ck CHECK(mark IN(6,4,2,0)),
	CONSTRAINT sporter_grade_sportid_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
	CONSTRAINT item_grade_itemid_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE
);


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.1 分数总和最高的系名和对应分数
SELECT *
FROM 
	(SELECT s.department sd,SUM(sums.sm) summax
	FROM sporter s,
	(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sums
	WHERE s.sporterid=sums.sporterid
	GROUP BY s.department
	ORDER BY summax DESC)
WHERE ROWNUM=1
;

--1.2
SELECT *
FROM (SELECT s.department sd,SUM(sums.sm) summax
	FROM sporter s,
	(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sums
	WHERE s.sporterid=sums.sporterid
	GROUP BY s.department
	ORDER BY summax DESC)temp	
WHERE temp.summax = (SELECT MAX(SUM(sums.sm)) summax
	FROM sporter s,
	(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sums
	WHERE s.sporterid=sums.sporterid
	GROUP BY s.department
	--ORDER BY summax DESC
	)
;

第二天续

--2
SELECT name,tmpname.itemid,i.itemname
FROM sporter s,item i,
(SELECT sporterid,itemid FROM grade WHERE itemid IN (SELECT itemid FROM item WHERE location IN '一操场') AND mark=6)tmpname
WHERE s.sporterid=tmpname.sporterid AND tmpname.itemid=i.itemid
;


--3
SELECT name
FROM sporter s,
(SELECT DISTINCT sporterid FROM grade WHERE itemid IN (SELECT itemid FROM grade WHERE sporterid IN（SELECT sporterid FROM sporter WHERE name IN ('张三')）))spid
WHERE s.sporterid=spid.sporterid AND name NOT IN ('张三')
;


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

--5
DELETE FROM item
WHERE itemname IN ('女子跳高')
;

补充个序列的创建、查询、删除：

--创建序列
CREATE SEQUENCE studentPKSequence START WITH 1 INCREMENT BY 1;
--序列增长
SELECT studentPKSequence.nextVal AS studentid FROM dual;
--删除序列
DROP SEQUENCE studentPKSequence;