sql笔试题（in和exits区别）

问题描述：
已知关系模式：
S (SNO,SNAME）                      学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE)        选课关系。SCGRADE 为成绩
要求实现如下3个处理：
  1． 找出没有选修过“李明”老师讲授课程的所有学生姓名
  2． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

  3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

我分别用in各exits进行了查询，大家可以体会两者之间的差别。

答案：

1. 

SELECT s.SNAME FROM s WHERE s.SNO NOT IN (SELECT sc.SNO  FROM c,sc WHERE c.CNO = sc.CNO 
AND CTEACHER='李明')

SELECT s.SNAME FROM s WHERE NOT EXISTS (SELECT sc.SNO  FROM c,sc WHERE c.CNO = sc.CNO AND 
s.SNO = sc.SNO AND CTEACHER='李明')（这里在子查询里面要加上“s.SNO = sc.SNO”否则查询的结果不是本题想要的）

2.

SELECT s.SNAME,AVG(sc.SCGRADE) FROM s,sc WHERE s.SNO = sc.SNO 
AND s.SNO IN (SELECT sno FROM sc WHERE SCGRADE < 60 GROUP BY sno HAVING COUNT(sno) >=2  )
GROUP BY sc.SNO


SELECT s.SNAME,AVG(sc.SCGRADE) FROM s INNER JOIN sc ON   s.SNO = sc.SNO  
WHERE  EXISTS (SELECT sc.SNO FROM sc WHERE sc.SNO = s.SNO AND SCGRADE < 60  GROUP BY sc.SNO HAVING
COUNT(sc.SNO) >=2) GROUP BY sc.SNO（注意where、group by、having的顺序）

3.

SELECT s.SNAME FROM s WHERE s.SNO IN (SELECT sno FROM sc WHERE cno='1') AND s.SNO IN
(SELECT sno FROM sc WHERE cno='2')


SELECT s.SNAME FROM s WHERE EXISTS (SELECT sno FROM sc WHERE sc.SNO=s.SNO AND cno='1') AND EXISTS
(SELECT sno FROM sc WHERE sc.SNO=s.SNO AND cno='2')