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

实验四：数据库的嵌套查询实验学号： 姓名： 实验四：数据库的嵌套查询实验实验目的：加深对嵌套查询语句的理解。实验内容：使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。实验步骤：一. 使用带IN谓词的子查询1. 查询与刘晨在同一个系学习的学生的信息: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=刘晨结果：2. 查询选修了课程名为信息系统 的学生的学号和姓名:答：select sno, sname from studentwhere sno in(select sno from scwhere cno in(select cno from coursewhere cname = 信息系统)3. 查询选修了课程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 ywhere x.sno=y.sno and o=1 and o=2select 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种（包含重复）；最后一类利用自连接查询结果有两种。二. 使用带比较运算的子查询4. 查询比刘晨年龄小的所有学生的信息:答：select * from studentwhere sage (select sage from student where sname = 刘晨 )三. 使用带Any, All谓词的子查询(对于ALL全称量词，建议改成否定之否定存在量词)5. 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;答：select sname, sagefrom studentwhere sdept IS andsage any (select sage from studentwhere sdept = IS)6. 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄:答：select sname, sagefrom studentwhere sdept IS andsage all(select sage from studentwhere sdept = IS)7. 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄:答：select sno, sname, sagefrom studentwhere sage all(select sage from studentwhere sdept = IS)四. 使用带Exists谓词的子查询和相关子查询8. 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄:select sno,sname,sage from student A where not exists(select * from student B where A.sage=B.sage and A.snoB.sno) 9. 查询所有选修了1号课程的学生姓名:答：select sname from studentwhere exists(select * from scwhere sc.sno = student.sno and cno = 1)10. 查询没有选修了1号课程的学生姓名:答：select sname from studentwhere not exists(select * from scwhere sc.sno = student.sno and cno = 1)11. 查询选修了全部课程的学生姓名:答：select sname from studentwhere not exists(select * from coursewhere not exists (select * from scwhere sc.sno = student.sno and o = o)11. 查询至少选修了学生95002选修的全部课程的学生的学号:答：SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.SNO = 95002 AND NOT EXISTS (SELECT *FROM SC SCZ WHERE SCZ.SNO=SCX.SNO AND SCZ.CNO=SCY.CNO)12. 求没有人选修的课程号cno和cname:答：select cno,cnamefrom coursewhere not exists(select *from scwhere o = o)13*. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程select sno,cno from student,course where not exists(select * from sc where cno=o and sno=student.sno)14*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade):select * from sc A where grade=(select max(grade) from sc where sno=A.sno ) order by A.sno ASC (此处可否用GROUP by)select * from sc where grade in (select max(grade) from sc group by sno) (此答案有点问题: 当在数据库中同时有两个相同的最高分的,就出现判断错误.如95001里有一个最高分85, 95002里假如也有一个学生某门课成绩为85,这时问题就出现了.)select sc.sno, maxgrade from sc , (select sno, max(grade) as maxgrade from sc group by sno) as A where sc.sno=A.sno and grade=maxgrade 15. 如何查询所有学生都选修了的课程的课程号cno? 试用几种可能的方法实现。答：select cno from scgroup by cnohaving count(grade) = (sele