数据库原理与应用实验指导
SELECT Sname FROM Student
WHERE Sage>(SELECT MAX(Sage) FROM Student
WHERE Sdept='IS') AND Sdept<>'IS'
ORDER BY Sname;
[例18] 查询哪些课程只有女生选读。(本题有多于两种表达法)
SELECT DISTINCT CNAME
FROM COURSE C
WHERE '女'=ALL( SELECT SSEX FROM SC,STUDENT
WHERE SC.SNO=STUDENT.SNO AND http://www.77cn.com.cnO=http://www.77cn.com.cnO);
或 SELECT DISTINCT CNAME FROM COURSE C
WHERE NOT EXISTS
( SELECT * FROM SC,STUDENT
WHERE SC.SNO=STUDENT.SNO AND http://www.77cn.com.cnO=http://www.77cn.com.cnO AND STUDENT.SSEX='男');
[例19] 查询所有未修1号课程的学生姓名。
SELECT Sname FROM Student
WHERE NOT EXISTS
( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
或 SELECT Sname FROM Student
WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='1');
但如下是错的：
SELECT Sname FROM Student,SC
WHERE SC.Sno=Student.Sno AND Cno<>'1';
[例20] 查询选修了全部课程的学生姓名(为了有查询结果，自己可调整一些表的)。 SELECT Sname FROM Student
WHERE NOT EXISTS
( SELECT * FROM Course
WHERE NOT EXISTS
(SELECT * FROM SC WHERE Sno=SC.Sno AND Cno=http://www.77cn.com.cno));
由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学生姓名没有一门课程是他不选的。
本题的另一操作方法是：
SELECT Sname FROM Student,SC WHERE Student.Sno=SC.Sno
Group by Student.Sno,Sname having count(*)>=(SELECT count(*) FROM Course);
[例21] 查询至少选修了学生98001选修的全部课程的学生号码。
本题的查询要求可以做如下解释，不存在这样的课程y，学生98001选修了y，而要查询的学生x没有选。写成的SELECT语句为：
SELECT Sno
FROM Student SX
WHERE NOT EXISTS
6