求一条SQL语句，另外，请帮忙注明为什么这么写，思路是什么..xiexie:)
S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
条件-----求出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

------解决方案--------------------
if object_id('S') is not null
drop table s
if object_id('C') is not null
drop table C
if object_id('SC') is not null
drop table SC
go
create table s(SNO int,SNAME varchar(20))
insert S select '2003081122','姓名1'
union select '2003081123','姓名2'
union select '2003081124','姓名3'
union select '2003081125','姓名4'
create table C(CNO int,CNAME varchar(20),CTEACHER varchar(20))
insert C select '1','数学','教师1'
union select '2','语文','教师2'
union select '3','英语','教师3'
union select '4','生物','教师4'
create table SC(SNO int,CNO int,SCGRADE float)
insert SC select '2003081122',1,55
union select '2003081122',2,80
union select '2003081122',3,44
union select '2003081122',4,33
union select '2003081123',1,58
union select '2003081123',2,59
union select '2003081123',3,75
union select '2003081124',4,59
union select '2003081124',2,86
union select '2003081125',4,99
select * from SC

select distinct S.SName,a.不及格门数 ,avg(SC.SCGRADE) from SC 
inner join
(select SNO,count(1)as 不及格门数 from SC where SC.SCGRADE<60 group by SNO) a
on SC.SNO = a.SNO and a.不及格门数>=2
inner join 
S
on SC.SNO = S.SNO
group by SC.SNO,a.不及格门数,S.SNAME
------解决方案--------------------

SQL code

select a.sname 姓名,avg(b.scgrade) 平均成绩 from S a,SC b
where a.sno=b.sno
and a.sno in (
                select sno from SC
                where scgrade<60
                group by sno
                having count(sno)>=2
            )
group by a.sname

------解决方案--------------------
SQL code

--多练习练习就熟了!!
--1.取出所有学生的姓名,成绩
select a.sname,b.scgrade from S a,SC b
where a.sno=b.sno
--2.条件:需要有两门成绩不及格的学生
select sno from SC
where scgrade<60
group by sno
having count(sno)>=2
--3.根据姓名分组,得出平均成绩
select a.sname,avg(b.scgrade) from S a,SC b
where a.sno=b.sno
and a.sno in (
                select sno from SC
                where scgrade<60
                group by sno
                having count(sno)>=2
            )
group by a.sname