1：查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
    select s.SId,s.Sage,s.Sname,s.Ssex,Re.c1score,Re.c2Score from student s join
    (select c1.sid , c1.score c1Score,c2.score c2Score from
    (select Sid ,score from sc where cid = ‘01’) AS c1
    join
    (select sid , score from sc where cid = ‘02’) AS c2
    on c1.sid = c2.sid
    and c1.score > c2.score) As Re
    on s.SId = Re.sid;
    image.png
    2：1.1 查询同时存在” 01 “课程和” 02 “课程的情况
    select s.SId,s.Sage,s.Sname,s.Ssex,Re.c1score,Re.c2Score from student s join
    (select c1.sid , c1.score c1Score, c2.score c2Score from
    (select Sid ,score from sc where cid = ‘01’) AS c1
    join
    (select sid , score from sc where cid = ‘02’) AS c2
    on c1.sid = c2.sid ) As Re
    on s.SId=Re.sid;
    image.png

    3：1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
    select s.SId,s.Sage,s.Sname,s.Ssex,Re.c1score,Re.c2Score from student s join
    (select c1.sid , c1.score c1Score, c2.score c2Score from
    (select Sid ,score from sc where cid = ‘01’) AS c1
    left join
    (select sid , score from sc where cid = ‘02’) AS c2
    on c1.sid = c2.sid ) As Re
    on s.SId=Re.sid;
    image.png
    4：1.3 查询不存在” 01 “课程但存在” 02 “课程的情况
    select s.SId,Sage,Ssex,Sname from student s join
    (select sid from sc where cid = ‘02’
    and sid not in (select sid from sc where cid = ‘01’)) As Re
    on s.sid=Re.sid；
    image.png
    5： 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    select s.sid,s.sname,Re.avgScore from student s join
    (select sid,ROUND(avg(score),2) as avgScore from sc group by sid) As Re
    on s.sid = Re.sid
    AND Re.avgScore>=60;
    image.png
    6：查询在 SC 表存在成绩的学生信息
    select sid from sc where score is not null group by sid;
    image.png
    7:查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
    select s.sid,s.Sname,Re.classNum,Re.sumScore from student s join
    (select sid , count() as classNum, IFNULL(sum(score),null) as sumScore from sc GROUP BY sid) AS Re
    on s.sid = Re.sid;
    image.png
    8:查有成绩的学生信息
    select s.SId,s.Sage,s.Sname,s.Ssex from student s join
    (select sid ,sum(score) as sumScore from sc group by sid) as Re
    on s.sid = Re.sid
    and sumScore is not null;
    image.png
    9:查询「李」姓老师的数量
    select count(    ) from teacher where Tname like ‘李%’;
    image.png
    10:查询学过「张三」老师授课的同学的信息
    select s.SId,s.Sage,s.Sname,s.Ssex from student s join
    (select Sid from sc join
    (select cid from course join
    (select Tid from teacher where Tname = ‘张三’) as te
    on course.TId = te.Tid) AS co
    on sc.CId = co.cid) As Re
    on s.sid = Re.Sid;
    image.png
    11:查询没有学全所有课程的同学的信息
    select s.SId,s.Sage,s.Sname,s.Ssex from student s join
    (select SId, count() Scount from sc group by Sid HAVING Scount<(select count() from course)) Re
    on Re.SId = s.SId;
    image.png
    12:查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
    select * from student where student.SId in (
    select sid from sc where cid in (select cid from sc where SId=’01’) GROUP BY SId);
    image.png
    13: