new《数据库原理及应用》实验指导书

数据库原理及应用实验指导书

可以将本查询中的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

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4