1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

    SELECT * FROM (
    SELECT t1.SId,class1,class2
    FROM(SELECT SId,score AS class1 FROM SC WHERE SC.CId = ‘01’)AS t1,
    (SELECT SId,score AS class2 FROM SC WHERE SC.CId = ‘02’)AS t2
    WHERE t1.SId = t2.SId AND t1.class1 > t2.class2
    )r
    LEFT JOIN Student ON Student.SId = r.SId

    1. 查询同时存在” 01 “课程和” 02 “课程的情况

    SELECT FROM
    (SELECT     FROM SC WHERE SC.CId=’01’)AS t1,
    (SELECT * FROM SC WHERE SC.CId=’02’)AS t2
    WHERE t1.SId = t2.SId

    1. 查询不存在” 01 “课程但存在” 02 “课程的情况

    SELECT * FROM SC
    WHERE SC.SId NOT IN (
    SELECT SId FROM SC
    WHERE SC.CId = ‘02’)
    AND SC.CId= ‘01’;

    1. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

    SELECT S.SId,S.Sname,AV FROM Student S,
    (SELECT SId,AVG(score) AV FROM SC GROUP BY SId HAVING AV >60) t1
    WHERE S.SId = t1.SId;

    1. 查询在 SC 表存在成绩的学生信息

    SELECT DISTINCT Student.*
    FROM Student,SC
    WHERE Student.SId=SC.SId