sql数据库的嵌套查询
实验四：数据库的嵌套查询实验
学号：姓名：
实验四：数据库的嵌套查询实验
实验目的：
加深对嵌套查询语句的理解。
实验内容：
使用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='刘晨') andsname<>'刘晨V
比较: select S1.* from student S1, student S2 where S1.sdept=S2.sdept and S2.sname='刘晨'
2.查询选修了课程名为’信息系统’的学生的学号和姓名:
SQL Server中: select sno, sname from student where sno in
(select sno from sc where cno in
(select cno from course where 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 y
where x.sno=y.sno and https://www.wendangku.net/doc/01708383.html,o='1' and https://www.wendangku.net/doc/01708383.html,o='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
https://www.wendangku.net/doc/01708383.html,o='1' and
https://www.wendangku.net/doc/01708383.html,o='2'
二. 使用带比较运算的子查询
4.查询比’刘晨’年龄小的所有学生的信息:
select * from student where sage<
(select sage from student where sname='刘晨')
三. 使用带Any, All谓词的子查询(对于ALL全称量词，建议改成否定之否定存在量词)
5.查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;
select sname, sage from student where sage (select sage from student where sdept='IS') and sdept<>'IS' 6.查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄: select sname, sage from student where sage (select sage from student where sdept='IS') and sdept<>'IS' 7.查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓 名和年龄: select sno,sname,sage from student where sage<>all (select sage from student where sdept='CS') 四. 使用带Exists谓词的子查询和相关子查询 8.查询与其他所有学生年龄均不同的学生学号, 姓名和年龄: select sno,sname,sage from student A where not exists (select * from student B where A.sage=B.sage and A.sno<> B.sno) 9.查询所有选修了1号课程的学生姓名: select sname from student where exists (select * from sc where sno=student.sno and cno='1') select sname from student where sno in (select sno from sc where cno='1') 10.查询没有选修了1号课程的学生姓名: select sname from student where not exists (select * from sc where sno=student.sno and cno='1') 11.查询选修了全部课程的学生姓名: select sname from student where not exists 不存在一门课没有选的学生. (select * from course where not exists 一门课都没有选的, ( select * from sc where sno=student.sno and cno=https://www.wendangku.net/doc/01708383.html,o)) 选某门课, 11. 查询至少选修了学生95002选修的全部课程的学生的学号: select distinct sno from sc A where not exists (select * from sc B where sno='95002' and not exists (select * from sc C where sno=A.sno and cno=https://www.wendangku.net/doc/01708383.html,o)) 12. 求没有人选修的课程号cno和cname: select cno,cname from course C where not exists (select * from sc where https://www.wendangku.net/doc/01708383.html,o=https://www.wendangku.net/doc/01708383.html,o ) 13*. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程 select sno,cno from student,course where not exists (select * from sc where cno=https://www.wendangku.net/doc/01708383.html,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 思考: 如何查询所有学生都选修了的课程的课程号cno? 试用几种可能的方法实现。