已知关系模式:
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')