五十道MySql 自己的答案

1.1
select * from (select SId,score from sc where CId='01') as t1 ,(select SId,score from sc where CId='02') as t2 where t1.score>t2.score and t1.SId=t2.Sid;
1.2
select * from (select * from sc where CId='01') as t1,(select * from sc where CId='02') as t2 where t1.SId=t2.SId;
1.3
select * from (select * from sc where CId='01') as t1 left join (select * from sc where CId='02') as t2 on t1.SId=t2.SId;
1.4
select * from sc where sc.SId not in (select SId from sc where CId='01')  and sc.CId='02';
2
select student.SId,student.Sname,student.Sage,student.Ssex,t1.averge from (select SId,SUM(score)/COUNT(CId) as averge from sc group by SId) as t1,student where t1.SId=student.SId and t1.averge>=60;
3
select student.SId,student.Sname,student.Sage,student.Ssex from student where student.SId in (select sc.SId from sc);
4
select student.SId ,student.Sname,t1.scoreAll,t1.CouseCount from Student left join (select sc.SId as StuId,COUNT(sc.CId) as CouseCount,SUM(sc.score) as scoreAll from SC group by sc.SId) as t1 on student.SId=t1.StuId;
4.1
select student.SId ,student.Sname,t1.scoreAll,t1.CouseCount from Student , (select sc.SId as StuId,COUNT(sc.CId) as CouseCount,SUM(sc.score) as scoreAll from SC group by sc.SId) as t1 where student.SId=t1.StuId;
5
select COUNT(teacher.Tname) from Teacher where teacher.Tname like '李%';
6
select student.* from Student,sc,
(select CId from Course,
    (select TId from teacher where Teacher.Tname='张三') as t2
    where course.TId=t2.TId) as t3 where sc.CId=t3.CId and  sc.SId=Student.SId;
7
select student.* from student,(select allScoreSId,count(CId) as courseCount from (select student.SID as allScoreSId,sc.CId from student left join sc on student.SId=sc.SId) as allScore group by allScore.allScoreSId) as t1 where student.SId=t1.allScoreSId and t1.courseCount<3;