1. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

    SELECT S.* FROM Student S,SC
    WHERE SC.CId IN (SELECT SC.CId FROM SC,Student S
    WHERE SC.SId = S.SId
    AND S.SId = ‘01’)
    AND SC.SId = S.SId
    GROUP BY S.SId

    1. 查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息

      — 取出不是01学生的cid 并排序 和 取出01学生的cid并排序 进行比较
      SELECT * FROM Student WHERE SId IN
      (
      SELECT sid FROM SC WHERE sid<>’01’ GROUP BY sid
      HAVING GROUP_CONCAT(cid ORDER BY cid) = (SELECT GROUP_CONCAT(cid ORDER BY cid) FROM SC WHERE sid = ‘01’)
      )

    2. 查询没学过”张三”老师讲授的任一门课程的学生姓名

    SELECT S.* FROM Student S WHERE S.SId NOT IN
    (SELECT SC.SId FROM Course C ,Teacher T,SC
    WHERE SC.CId = C.CId
    AND T.TId = C.TId
    AND T.Tname = ‘张三’)

    1. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

    SELECT Student.SId,Student.Sname,b.ag FROM Student RIGHT JOIN
    (SELECT sid,AVG(score) ag FROM SC WHERE sid IN
    (SELECT sid FROM SC WHERE score < 60 GROUP BY sid HAVING COUNT(score)>1) GROUP BY sid)b
    ON Student.SId = b.sid

    15. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
    SELECT Student.* FROM SC,Student
    WHERE SC.SId = Student.SId
    AND SC.CId = ‘01’
    AND SC.score < 60
    ORDER BY SC.score DESC