实验四：数据库的各类数据查询操作

一、实验目的

掌握SQL程序设计基本规范，熟练运用SQL语言实现数据的各种查询和设计，包括连接查询、嵌套查询、集合查询等。 二、实验内容和要求

针对KingbaseES数据库设计单个表针对自身的连接查询，设计多个表的连接查询；设计各种嵌套查询和集合查询。了解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。 三、实验步骤

? 连接查询

1. 查询每个学生及其选修课程的情况:

select student.*, sc.* from student, sc where student.sno=sc.sno 比较: 笛卡尔集: select student.*, sc.* from student, sc

自然连接: select student.sno, sname, ssex, sdept, cno, grade from student, sc where student.sno=sc.sno

2. 查询每一门课程的间接先行课(只求两层即先行课的先行课):

select First.cno, Second.pcno 间接先行课 from course First, course Second where First.pcno=Second.cno 比较:

select First.cno, Second.pcno 间接先行课 from course First, course Second where First.pcno=Second.cno and Second.pcno is not null

3. 列出所有学生的基本情况和选课情况, 若没有选课,则只列出基本情况信息: SQL Server 中: select s.sno, sname, ssex,sdept, cno, grade from student s, sc sc where s.sno*=sc.sno

4. 查询每个学生的学号, 姓名, 选修的课程名和成绩:

select S.sno, sname, cname, grade from student S, course C, sc SC where S.sno=SC.sno and C.cno=SC.cno

5. 查询平均成绩在80分以上的学生姓名

Select sname from student，sc where student.sno=sc.sno GROUP BY sc.sno HAVING AVG(sc.grade)>80;

? 高级查询

使用带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. 查询选修了课程名为’信息系统’ 的学生的学号和姓名: 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')

比较: 查询选修了课程’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'

使用带比较运算的子查询

1. 查询比’刘晨’年龄小的所有学生的信息:

select * from student where sage<

(select sage from student where sname='刘晨')

使用带Any, All谓词的子查询

2. 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;

select sname, sage from student where sage

(select sage from student where sdept='IS') and sdept<>'IS'

3. 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄:

select sname, sage from student where sage

(select sage from student where sdept='IS')

and sdept<>'IS'

4. 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄:

select sno,sname,sage from student where sage<>all

(select sage from student where sdept='CS')

使用带Exists谓词的子查询和相关子查询

5. 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄: select sno,sname,sage from student A where not exists

(select * from student B where A.sage=B.sage and

A.sno<>B.sno)

6. 查询所有选修了1号课程的学生姓名:

select sname from student where exists

(select * from sc where sno=student.sno and cno='1')

7. 查询没有选修了1号课程的学生姓名:

select sname from student where not exists

(select * from sc where sno=student.sno and cno='1')

8. 查询选修了全部课程的学生姓名:

select sname from student where not exists

(select * from course where not exists

( select * from sc where sno=student.sno and

cno=course.cno))

12. 查询至少选修了学生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))

13. 求没有人选修的课程号cno和cnamecname:

select cno,cname from course C where not exists

(select * from sc where sc.cno=C.cno )

14. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程

select sno,cno from student,course where not exists

(select * from sc where cno=course.cno and sno=student.sno)

15. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade):

select * from sc A where grade=

(select max(grade) from sc where sno=A.sno )

? 集合查询

1. 查询数学系和信息系的学生的信息;

select * from student where sdept='MA' union select * from student where sdept='IS'

2. 查询选修了1号课程或2号课程的学生的学号:

select sno from sc where cno='1' Union

select sno from sc where cno='2'

思考：

1. 连接查询速度是影响关系数据库性能的关键因素。请讨论如何提高连接查询速度，并进行实验验证。

2. 试分析不相关子查询和相关子查询的区别。 3. 试分析什么类型的查询只能用嵌套查询实现？