实验三四(答案)[1]
———————————————————————————————— 作者： ———————————————————————————————— 日期：
2
实验三：数据库的嵌套查询实验
实验目的：
加深对嵌套查询语句的理解。
实验内容：
使用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='信息系统')) VFP中: select sno, sname from student where sno in (select sno from sc, course where sc.cno=course.cno and 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'