实验4数据库的嵌套查询.docVIP

实验四：数据库的嵌套查询实验 学号： 姓名： 实验四：数据库的嵌套查询实验 实验目的： 加深对嵌套查询语句的理解。 实验内容： 使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。 实验步骤： 一. 使用带IN谓词的子查询 查询与’刘晨’在同一个系学习的学生的信息: select * from student where sdept in (select sdept from student where sname=刘晨) 比较: select * from student where sdept = (select sdept from student where sname=刘晨) 的异同 比较: select * from student where sdept = (select sdept from student where sname=刘晨) and sname刘晨’ 比较: select S1.* from student S1, student S2 where S1.sdept=S2.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 = 信息系统 ) ) 查询选修了课程’1’和课程’2’的学生的学号(姓名): select sno from student where sno in (select sno from sc where cno=1) and sno in (select sno from sc where cno=2) select x.sno from SC x ,SC y where x.sno=y.sno and x.cno=1 and y.cno=2 select sno from SC where cno=1 and sno in (select sno from SC where cno=2) 比较: 查询选修了课程’1’或课程’2’的学生的sno: select sno from sc where cno=1 or cno=2 比较连接查询: select A.sno from sc A, sc B where A.sno=B.sno and A.cno=1 and B.cno=2 结果：第一类的查询结果为’95001’和’95004’；第二类查询结果有7种（包含重复）；最后一类利用自连接查询结果有两种。 二. 使用带比较运算的子查询 查询比’刘晨’年龄小的所有学生的信息: 答：select * from student where sage (select sage from student where sname = 刘晨 )select sname, sage from student where sdept IS and sage any (select sage from student where sdept = IS ) 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄: 答：select sname, sage from student where sdept IS and sage all (select sage from student where sdept = IS ) 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄: 答：select sno, sname, sage from student where sage all (select sage from student where sdept = IS ) 四. 使用带Exists谓词的子查询和相关子查询 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄: select sno,sname,sage from student A where not exists (select * from student B where A.sage=B.sage and A.snoB.sno) 查询所有选修了1号课程的学生姓名: 答：select sname from student where exists (select * from sc where sc.sno = student.sno and cno = 1