简单查询

create table student(
sid number(8) primary key,
sname varchar(20)
);

create table class(
cid number(10) primary key,
cname varchar2(20)
);

create table score(
sid number(8)
constraint score_fk_student
references student(sid),
cid number(10)
constraint score_fk_class
references class(cid),
score number(2),
remarks varchar2(50),
constraint score_pk primary key(sid,cid)
);
--student数据
insert into student(sid,sname)
values(12082110,'李辉');
insert into student(sid,sname)
values(12082214,'黄汉文');
insert into student(sid,sname)
values(12082401,'李明');
insert into student(sid,sname)
values(12093312,'龙少化');

--class数据
insert into class(cid,cname)
values(11101,'算法分析与设计');
insert into class(cid,cname)
values(11125,'Java程序设计');
insert into class(cid,cname)
values(11500,'计算机网络');
insert into class(cid,cname)
values(11250,'网页设计');
--score数据
insert into score(sid,cid,score)
values(12082110,11250,85);
insert into score(sid,cid,score)
values(12082214,11125,78);
insert into score(sid,cid,score)
values(12082401,11101,65);
insert into score(sid,cid,score)
values(12093312,11500,98);
--
--A:查询成绩大于90分的学生姓名及课程名称
select student.sname,class.cname
from student,class,score
where score.sid=student.sid and
score.cid=class.cid and
score.score>90;

SNAME CNAME
-------------------- --------------------
龙少化 计算机网络

--B:将成绩表中成绩介于70－80的记录的remark字段更新为“良”
update score set remarks='良'
where score between 70 and 80;

SQL> select * from score;

SID CID SCORE REMARKS
--------- ----------- ----- --------------------------------------------------
12082110 11250 85
12082214 11125 78 良
12082401 11101 65
12093312 11500 98

--将成绩表中sid未曾在student或cid未曾在class表出现过的记录删除
delete from score
where not exits(
(select 1 from student where score.sid=student.sid) and
(select 1 from class where score.cid=class.cid))

对于最后一个问题，因为score表中的sid和cid是参照student(sid)和class(cid)的，

所以不存在score表中有信息，而找不到这个学生和这门课程。


原帖：http://topic.youkuaiyun.com/u/20110310/22/0b9df662-a017-4cc6-84ff-2f2a4a04a202.htm