Mysql几个小问题，几个查询小语句

S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

1. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

select sname,avg(scgrade)

from s,sc

where s.sno=sc.sno and sc.scgrade<60

group by sno,sname

having count(*)>=2

2. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

如果１号课程指　cno =1

select s.sname

from s, sc

where s.sno=sc.sno and sc.cno in (1,2)

group by s.sname

having count(*)=2

如果１号课程指　cname='1号'

select s.sname

from s, sc ,c

where s.sno=sc.sno and sc.cno = c.cno and c.cname in ('1号','2号')

group by s.sname

having count(*)=2

3.

列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

如果１号课程指　cno =1

select sno

from (

select A.sno,A.Grade1,B.Grade 2

from

(select sno,scgrade as Grade1 from s,sc where s.sno=sc.sno and sc.cno=1) A,

(select sno,scgrade as Grade2 from s,sc where s.sno=sc.sno and sc.cno=2) B

where

A.sno = B.sno

) C

where Grade1>Grade2

如果１号课程指　cname ='1号'

select sno

from (

select A.sno,A.Grade1,B.Grade 2

from

(select sno,scgrade as Grade1 from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and c.cname='1号') A,

(select sno,scgrade as Grade2 from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and c.cname='2号') B

where

A.sno = B.sno

) C

where Grade1>Grade2