数据库原理及应用实验指导书
可以将本查询中的IN谓词用比较运算符‘=’来代替：
SELECT Sno, Sname, Sdept FROM Student WHERE Sdept =
(SELECT Sdept FROM Student
WHERE Sname = '刘晨'); /* 解法二*/ 也可以使用自身连接完成以上查询：
SELECT s1.Sno, s1.Sname, s1.Sdept FROM Student s1, Student s2 WHERE s1.Sdept = S2.Sdept AND
s2.Sname = '刘晨'; /* 解法三*/ 还可以使用EXISTS谓词完成本查询：
SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS (SELECT *
FROM Student S2
WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨'); /* 解法四*/
又如：查询选修了课程名为“信息系统”的学生号和姓名。
SELECT Sno, Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC
WHERE Cno IN
(SELECT Cno FROM Course
WHERE Cname = '信息系统' ) );
也可以使用连接查询来完成上述查询：
SELECT Student.Sno, Sname FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = '信息系统';
（6）嵌套查询（带有比较运算符的子查询）
例如：找出每个学生超过他所选修课程平均成绩的课程号。
SELECT Sno, Cno FROM SC x
WHERE Grade >= ( SELECT AVG(Grade) FROM SC y
13
数据库原理及应用实验指导书
WHERE y.Sno = x.Sno); （7）嵌套查询（带有ANY或ALL谓词的子查询）
例如：查询其他系中比计算机系某个学生年龄小的学生的姓名和年龄。
SELECT Sname, Sage FROM Student
WHERE Sage WHERE Sdept = 'CS') AND Sdept <> 'CS'; 本查询也可以使用聚集函数来实现： SELECT Sname, Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept = 'CS') AND Sdept <> 'CS'; 又如：查询其他系中比计算机系所有学生年龄都小的学生的姓名和年龄。 SELECT Sname, Sage FROM Student WHERE Sage WHERE Sdept = 'CS') AND Sdept <> 'CS'; 也可以使用聚集函数来实现： SELECT Sname, Sage FROM Student WHERE Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept = 'CS') AND Sdept <> 'CS'; （8）嵌套查询（带有EXISTS谓词的子查询） 例如：查询所有选修了1号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); 又如：查询所有未选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC 14 数据库原理及应用实验指导书 WHERE Sno=Student.Sno AND Cno='1'); 可以使用带有EXISTS谓词的子查询实现全称量词或蕴涵逻辑运算功能： 例如：查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno)); 又如：查询至少选修了学生200215122选修的全部课程的学生号码。 SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno='200215122' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)); （9）集合查询 例如：查询计算机系的学生以及年龄不大于19岁的的学生。 SELECT * FROM Student WHERE Sdept='CS' UNION /*并集运算*/ SELECT * FROM Student WHERE Sage<=19; 可以改用多重条件查询： SELECT * FROM Student WHERE Sdept='CS' OR Sage<=19; 又如：查询既选修了课程1又选修了课程2的学生（交集运算）。 SELECT Sno FROM SC WHERE Cno='1' INTERSECT /*交集运算*/ SELECT Sno 15 数据库原理及应用实验指导书 FROM SC WHERE Cno='2'; 可以使用嵌套查询： SELECT Sno FROM SC WHERE Cno='1' AND Sno IN (SELECT Sno FROM SC WHERE Cno='2'); 思考：能不能改用多重条件查询？ SELECT Sno FROM SC WHERE Cno='1' AND Cno='2'; 再如：查询计算机系的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept='CS' EXCEPT /*差集运算*/ SELECT * FROM Student WHERE Sage<=19; 可以改用多重条件查询： SELECT * FROM Student WHERE Sdept='CS' AND Sage>19; 4.2.2 扩展练习（要求写出并执行SQL语句完成以下各种操作，记录查询结果） （1）查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生学号及成绩--如果没有学生选择该课，则相应的学生学号及成绩为空值）。 （2）查询与“张立”同岁的学生的学号、姓名和年龄。（要求使用至少3种方法求解） （3）查询选修了3号课程而且成绩为良好（80~89分）的所有学生的学号和姓名。 （4）查询学生200215122选修的课程的课程号、课程名 （思考：如何查询学生200215122选修的课程的课程号、课程名及成绩？） （5）找出每个学生低于他所选修课程平均成绩5分以上的课程号。（输出学号和课程号） （6）查询比所有男生年龄都小的女生的学号、姓名和年龄。 （7）查询所有选修了2号课程的学生姓名及所在系。 16