实验4 数据的高级查询(二)
实验目的:
加深对嵌套查询语句的理解。
实验内容:
使用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. 查询选修了课程名为’信息系统’ 的学生的学号和姓名:
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 (selectsnofrom 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'
二. 使用带比较运算的子查询
4. 查询比’刘晨’年龄小的所有学生的信息:
select * from student where sage<
(select sage from student where sname='刘晨')
三. 使用带Any, All谓词的子查询
5. 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;
select sname, sage from student where sage and sdept<>'IS' 6. 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄: select sname, sage from student where sage 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') 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=course.cno)) 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=B.cno)) 12. 求没有人选修的课程号cno和cnamecname: select cno,cname from course C where not exists (select * from sc where sc.cno=C.cno ) 13*. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程 select sno,cno from student,course where not exists (select * from sc where cno=course.cno and sno=student.sno) 14*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade): select * from sc A where grade= (select max(grade) from sc where sno=A.sno ) 思考: 如何查询所有学生都选修了的课程的课程号cno? 因篇幅问题不能全部显示,请点此查看更多更全内容