day01

SELECT
    student.*,
    sc.CId,
    sc.score 
FROM
    student,(
    SELECT
        t1.SId 
    FROM
        ( SELECT * FROM sc WHERE CId = 01 ) t1,(
        SELECT
            * 
        FROM
            sc 
        WHERE
            CId = 02 
        ) t2 
    WHERE
        t1.SId = t2.SId 
        AND t1.score > t2.score 
    ) t3,
    sc 
WHERE
    student.SId = t3.SId 
    AND sc.SId = t3.SId

SELECT
    t2.SId,
    t2.Sname,
    t1.Avg 
FROM
    ( SELECT SId, CAST( AVG( score ) AS DECIMAL ( 10, 2 )) Avg FROM sc GROUP BY SId ) t1,
    student t2 
WHERE
    t1.SId = t2.SId 
    AND t1.Avg >= 60