问个sql查询语句解决思路

问个sql查询语句
Student(SId,Sname,Sgex,Ssex) 学生表
Course(CId,Cname) 课程表
SC(SCId,SId,CId,Score)成绩表

1：查询课程编号”02“成绩比课程编号“01”成绩低的所有同学的编号和姓名


[解决办法]

SQL code

select a.sid,a.snamefrom student aleft join sc b on a.sid=b.sid and b.cid='01'left join sc c on a.sid=c.sid and c.cid='02'where isnull(b.score,0)>isnull(c.score,0)
[解决办法]
SQL code
select SId,Sname    from Student S    where exists (select 1 from SC S1,SC S2        where S1.SId=S.SId and S2.SId=S.SId            and S1.Score>S2.Score             and S1.CId='01' and S2.CId='02')
[解决办法]
SQL code
select a.SId,a.Snamefrom Student ainner join(select SId,Scorefrom SC where CId='02') b on a.SId=b.SIdinner join(select SId,Scorefrom SC where CId='01') c on a.SId=c.SIdwhere b.Score<c.Score