数据库Chapter05数据操纵.ppt

( SELECT * FROM Branch b -- a branch b such that WHERE s.branchNo = b.branchNo -- s works in b, AND city=‘Landon’ ); -- and b is in Landon, WHERE NOT EXISTS -- if there does not exist FROM Staff s -- For each staff s, Solution 3( Subquery using NOT EXISTS ): 8. EXISTS and NOT EXISTS Example 5.31(b) Query using NOT EXISTS Find all staff who do not work in a London branch, show staffNo and name. … … … … … … … Staff B003 Ford David SG14 B003 Beech Ann SG37 B005 White John SL21 B007 Howe Mary SA9 B003 Brand Susan SG5 Julie fName B005 branchNo Lee SL41 lName staffNo Branch … Glasgow … B003 … Aberdeen … B007 … London … B005 … Bristol … B004 … … … … London B002 city branchNo SELECT staffNo, fName, lName -- then list s’s info s b s b Beech Ann SG37 Ford David SG14 Result table Howe Mary SA9 Susan fName Brand SG5 lName staffNo s b s b s b s b 试比较：查询没有选修数据库课程的学生学号： SELECT Sno FROM Student S WHERE NOT EXISTS ( SELECT * FROM SC, Course C WHERE S.Sno=SC.Sno AND SC.Cno = C.Cno AND Cname= ‘数据库’ ) ( ) ( ) SELECT * FROM Course WHERE Cno NOT IN 解法1：如果学生s没有选的课不存在，则s选了全部课程！ 1）给定学生s， SELECT Cno FROM SC WHERE Sno=s.Sno 2）找出学生s没有选的课 如果一门课的课程号不在s选修的课程号列表中， 则s没有选修这门课程（用NOT IN）。 例42 查询选修了全部课程的学生姓名(难题)。 WHERE NOT EXISTS /*s没有选的课不存在?s选了全部课程*/ 3）用WHERE条件表达“s没有选的课不存在” 4）完成SELECT语句 SELECT Sname 关键：找出学生s没有选的课 查询学生s选修的课程号列表 FROM Student AS s ( ) ( ) SELECT * FROM Course WHERE Cno NOT IN 解法1：如果学生s没有选的课不存在，则s选了全部课程！