MySQL条件查询

student(Sid,Sname,Sage,Ssex)学生表
Sid：学号
Sname：学生姓名
Sage：学生年龄
Ssex：学生性别

Course(Cid,Cname,T#)课程表
Cid：课程编号
Cname：课程名称
Tid：教师编号

SC(Sid,Cid,score)成绩表
Sid：学号
Cid：课程编号
score：成绩

Teacher(Tid,Tname)教师表
Tid：教师编号：
Tname：教师名字

1、查询“001”课程比“002”课程成绩高的所有学生的学号
select a.Sid from SC a, SC b where a.Sid=b.Sid and a.Cid=001 and b.Cid=002 and a.score>b.score;

2、查询平均成绩大于60分的同学的学号和平均成绩
select Sid,avg(score) from SC group by Sid having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩
select SC.Sid,Sname,count(Cid),sum(score) from SC,student where SC.Sid=student.Sid group by SC.Sid;

4、查询姓‘李’的老师的个数：
select count(Tname) fromTeacher where tname like '李%';

5、查询没有学过“叶平”老师可的同学的学号、姓名：
select Sid,Sname from student where Sid not in (select Sid from SC where Cid in(select c.Cid from Course c,Teacher t where c.Tid=t.Tid and Tname='李老师' )); 

6、查询学过李老师所教的所有课的同学的学号、姓名：

select student.Sid,Sname from student,SC where student.Sid=SC.Sid and Cid in (select Cid from Course ,Teacher where Course.Tid=Teacher.Tid and Tname='李老师')  group by SC.Sid having count(Cid)= (select count(Cid) from Course,Teacher where Course.Tid=Teacher.Tid and Tname='李老师') ;

7、查询学过语文并且也学过编号2课程的同学的学号、姓名：

select s.Sid,s.Sname from student s,SC sc1,SC sc2 where s.Sid=sc1.Sid and s.Sid=sc2.Sid and sc1.Cid=2 and sc2.Cid in (select Cid from Course where Cname='语文');

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：

select Sid,Sname from student where Sid in (select sc1.Sid from SC sc1,SC sc2 where sc1.Sid=sc2.Sid and sc1.Cid=1 and sc2.Cid=2 and sc1.score>sc2.score);

9、查询所有课程成绩小于60的同学的学号、姓名：

select Sid,Sname fr