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

思路：
1)首先查询01 ， 02 课程的课程分数
(SELECT sid ,score AS score1 FROM sc WHERE sc.CId = ‘01’)AS t1
(SELECT sid ,score AS score2 FROM sc WHERE sc.CId = ‘02’)AS t2

2）然后利用查出来的这两张表关联查询：
查询01 课程
SELECT t1.sid , score1 , score2 FROM
(SELECT sid ,score AS score1 FROM sc WHERE sc.CId = ‘01’)AS t1,
(SELECT sid ,score AS score2 FROM sc WHERE sc.CId = ‘02’)AS t2
WHERE t1.sid = t2.sid AND t1.score1 > t2.score2) AS tem1
3）查询所有学生信息和分数
SELECT * FROM student RIGHT JOIN
(
SELECT t1.sid , class1 , class2 FROM
(SELECT sid , score AS class1 FROM sc WHERE cid = ‘01’) AS t1,
(SELECT sid , score AS class2 FROM sc WHERE cid = ‘02’)AS t2
WHERE t1.sid = t2.sid AND t1.class1 > t2.class2) AS rr
ON student.sid = rr.sid

1.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.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

SELECT FROM
(SELECT FROM sc WHERE sc.CId = ‘01’) AS t1
LEFT JOIN
(SELECT * FROM sc WHERE sc.CId = ‘02’) AS t2
ON t1.SId = t2.SId;

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

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