123

1.查询"01 “课程比” 02 "课程成绩高的学生的信息及课程分数
SELECT s.*,c1.score,c2.score FROM Student s,SC c1,SC c2 WHERE s.SId = c1.SId AND s.SId = c2.SId AND c1.CId=‘01’ AND c2.CId=‘02’ AND c1.score>c2.score;

1.1查询同时存在" 01 "课程和" 02 "课程的情况
SELECT * FROM SC sc1,SC sc2 WHERE sc1.CId='01' AND sc2.CId='02' AND sc1.SId = sc2.SId;


1.2查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT s.*,c1.score,c2.score FROM Student s LEFT JOIN SC c1 ON s.SId = c1.SId AND c1.CId = '01'  LEFT JOIN SC c2 ON s.SId = c2.SId AND c2.CId = '02' 

1.3	查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM SC  WHERE SId NOT IN (SELECT SId FROM SC WHERE CId = '01') AND CId = '02';

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.SId,s.Sname,AVG(sc.score) FROM Student s,SC sc WHERE s.SId = sc.SId GROUP BY s.SId HAVING AVG(sc.score)>60 ;

3.查询在 SC 表存在成绩的学生信息
SELECT * FROM Student WHERE SId IN (SELECT DISTINCT SId FROM SC);

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.SId,s.Sname,count(sc.CId),sum(sc.score) FROM Student s LEFT JOIN SC sc ON s.SId = sc.SId GROUP BY sc.SId;

4.1	查有成绩的学生信息
SELECT * FROM Student WHERE SId IN (SELECT DISTINCT SId FROM SC);

5. 查询学过「张三」老师授课的同学的信息
   SELECT s.* FROM Student s,Course c,Teacher t,SC sc WHERE s.SId = sc.SId AND sc.CId = c.CId AND c.TId = t.TId AND t.Tname = ‘张三’;

6. 查询没有学全所有课程的同学的信息
   SELECT s.* FROM Student s WHERE s.SId NOT IN (SELECT sc.SId FROM SC sc GROUP BY sc.SId HAVING count(sc.CId) = (SELECT count(CId) FROM Course));

7. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
   SELECT * FROM Student WHERE SId IN(SELECT SId FROM SC WHERE CId IN (SELECT DISTINCT CId FROM SC WHERE SId=‘01’) );

8. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
   SELECT * FROM Student WHERE SId IN (SELECT SId FROM SC WHERE CId IN (SELECT CId FROM SC WHERE SId=‘01’) GROUP BY SId HAVING count(CId)= (SELECT count(CId) FROM SC WHERE SId=‘01’);

9. 查询没学过"张三"老师讲授的任一门课程的学生姓名
   SELECT Sname FROM Student WHERE SId NOT IN (SELECT sc.SId FROM SC sc,Teacher t,Course c WHERE sc.CId = c.CId AND c.TId = t.TId AND t.Tname=‘张三’ );

10. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    SELECT s.SId,s.Sname,AVG(sc.score) FROM Student s,SC sc WHERE s.SId = sc.SId AND sc.score<60 GROUP BY sc.SId HAVING count(*)>1 ;

https://www.cnblogs.com/imsoft/p/10182126.html