数据库综合实践
1.建立数据表
范例：
到了秋天，为了让同学们增加体育锻炼，学校开始
筹备运动会，为了方便保存比赛成绩信息，所以定义了
如下几张数据表
运动员表sporter(运动员编号sporterid,运动员姓名name,
运动员性别sex,所属系号department);
项目表item(项目编号itemid,项目名称itemname,
比赛地点location);
成绩表grade(运动员编号sporterid,项目编号itemid,
积分mark)。
--练习脚本
--删除表
DROP TABLE grade PURGE;
DROP TABLE sporter PURGE;
DROP TABLE item PURGE;
PURGE RECYCLEBIN;
--创建表
CREATE TABLE sporter(
sporterid NUMBER(4),
name VARCHAR2(30) NOT NULL,
sex VARCHAR2(10) NOT NULL,
department VARCHAR2(30) NOT NULL,
CONSTRAINT pk_sporterid PRIMARY KEY(sporterid),
CONSTRAINT ck_sex CHECK (sex IN('男','女'))
);
CREATE TABLE item(
itemid VARCHAR2(4),
itemname VARCHAR2(30) NOT NULL,
location VARCHAR2(30) NOT NULL,
CONSTRAINT pk_itemid PRIMARY KEY (itemid)
);
CREATE TABLE grade(
sporterid NUMBER(4),
itemid VARCHAR2(4),
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(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(1003,'x003',0);
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(1001,'x004',2);
INSERT INTO grade(sporterid,itemid,mark)VALUES(1002,'x004',0);
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);
--事务提交
COMMIT;
数据操作：
第一题：求出目前总积分最高的系名及其积分
select s.department , sum(g.mark)
from sporter s,grade g
where s.sporterid = g.sporterid
group by s.department
having sum(g.mark)=(
select max(sum(g.mark)) from
sporter s,grade g
where s.sporterid=g.sporterid
group by s.department
)
第二题：找出在一操场进行比赛的各项目名称及其冠军的姓名
select s.name,temp.iname
from sporter s,
(select i.itemname iname,max(g.mark),g.sporterid spid
from item i ,grade g
where i.itemid=g.itemid
and i.location='一操场'
group by i.itemname,i.itemid,g.sporterid
) temp
where s.sporterid=temp.spid
select g.itemid,g.sporterid,i.itemname,s.name
from item i,grade g,sporter s
where (g.mark,g.itemid)in(
select max(mark),g.itemid
from grade g,item i
where g.itemid=i.itemid and i.location='一操场'
group by g.itemid
) and g.itemid=i.itemid
and g.sporterid=s.sporterid
;
