sql的一个查询，问一下该怎么查
问题及描述：
 --1.学生表
 Student(Snun,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

SC(Snum,Cnum,score) --S# 学生编号,C# 课程编号,score 分数

create table Student(Snum varchar(10),Sname varchar(10),Sage date,Ssex varchar(10))
 insert into Student values('01' , '赵雷' , '1990-01-01' , '男')
 insert into Student values('02' , '钱电' , '1990-12-21' , '男')
 insert into Student values('03' , '孙风' , '1990-05-20' , '男')
 insert into Student values('04' , '李云' , '1990-08-06' , '男')
 insert into Student values('05' , '周梅' , '1991-12-01' , '女')
 insert into Student values('06' , '吴兰' , '1992-03-01' , '女')
 insert into Student values('07' , '郑竹' , '1989-07-01' , '女')
 insert into Student values('08' , '王菊' , '1990-01-20' , '女')

create table SC(Snum varchar(10),Cnum varchar(10),score number(18,1))
 insert into SC values('01','01',80);
 insert into SC values('01','02',90);
 insert into SC values('01','03',99);
 insert into SC values('02','01',70);
 insert into SC values('02','02',60);
 insert into SC values('02','03',80);
 insert into SC values('03','01',80);
 insert into SC values('03','02',80);
 insert into SC values('03','03',80);
 insert into SC values('04','01',50);
 insert into SC values('04','02',30);
 insert into SC values('04','03',20);
 insert into SC values('05','01',76);
 insert into SC values('05','02',87);
 insert into SC values('06','01',31);
 insert into SC values('06','03',34);
 insert into SC values('07','02',89);
 insert into SC values('07','03',98)；

求查询"01"课程比"02"课程成绩高的学生的信息及课程分数


------解决方案--------------------

SQL code

select s.*,s1.Cnum,s1.score,s2.Cnum,s2.score from Student s,SC s1, SC s2
    where s.Snum=s1.Snum and s.Snum=s2.Snum
        and s1.Cnum='01' and s2.Cnum='02' and s1.score>s2.score;
/*
SNUM                 SNAME                SAGE           SSEX                 CNUM                      SCORE CNUM                      SCORE         
-------------------- -------------------- -------------- -------------------- -------------------- ---------- -------------------- ----------         
02                   钱电                 21-12月-90     男                   01                           70 02                           60         
04                   李云                 06-8月 -90     男                   01                           50 02                           30 
*/

------解决方案--------------------

select * from student  
left join sc
on student.snum=sc.snum
where student.snum in  
( select sc01.s1 from
   (select snum s1,cnum c1,score sc1 from sc where cnum='01') sc01
    join
   (select snum s2,cnum c2,score sc2 from sc where cnum='02') sc02
   on sc01.s1=sc02.s2
   where sc02.sc2 > sc01.sc1
)

------解决方案--------------------
探讨

SQL code


select s.*,s1.Cnum,s1.score,s2.Cnum,s2.score from Student s,SC s1, SC s2
    where s.Snum=s1.Snum and s.Snum=s2.Snum
        and s1.Cnum='01' and s2.Cnum='02' and s1.score>s2.score;
/……