oracle数据库学习（教师，学生，课程，成绩）实例问题之（一）

1、查询"001"课程比"002"课程成绩高的学生的信息及课程分数

思路：同表多次查询

select s.*,s1.score from student s,sc s1,sc s2
where s1.cid='001' and s2.cid='002'
and s.sid=s1.sid
and s1.sid=s2.sid
and s1.score>s2.score

或

select s.*,s1.score from student s,
(select sid, score from sc where cid='001') s1,
(select sid,score from sc where cid='002') s2
where s1.score>s2.score
and s.sid=s1.sid
and s1.sid=s2.sid

注：第二种方法虽然繁琐但是对日后的多表子查询有所帮助

 

2.查询"001"课程比"002"课程成绩低的学生的信息及课程分数且不存在"001"课程但存在"002"课程

思路：不存在001但存在002 右链接，同样，sc表多次查询

select s.*,s2.score from student s,
(select sid,score from sc 
where cid='001') s1
right join
(select sid,score from sc
 where cid='002') s2
on s1.sid=s2.sid
where s.sid=s