Question08-查询没学过"张三"老师授课的同学的信息

查询没学过"张三"老师授课的同学的信息

问题分析

• 没学过，那么就说明是SC表中不存在该老师教的CID编号

  • 反之来说，学生表中不存在与该CID对应的SID编号
  • 也可以参考上一篇随笔Question07

• 所以SQL语句如下所示

  • 方案1：

    • SELECT * FROM Student WHERE SID NOT IN 
      (
        SELECT DISTINCT stu.SID FROM Student stu
        LEFT JOIN SC sc ON stu.`SID` = sc.`SID`
        LEFT JOIN Course cou ON sc.`CID` = cou.`CID`
        LEFT JOIN Teacher tea ON cou.`TID` = tea.`TID`
        WHERE tea.`Tname` = "张三"
      );
      

  • 方案2：

    • SELECT * FROM Student WHERE SID NOT IN 
      (
      SELECT DISTINCT Student.SID FROM Student , SC , Course , Teacher
      WHERE 
        Student.SID = SC.SID 
        AND SC.CID = Course.CID 
        AND Course.TID = Teacher.TID 
        AND Teacher.Tname = '张三'
      ORDER BY Student.SID
      );
      

• 查询结果如下所示

  • 

• 小结

  • 总之遇到这种带否定句意的句子，应该同时考虑其肯定的句子，观察哪个更容易写出来，只要有一个写出来了，那另一个也就很简单了