实验4：数据库的高级查询操作
实验四：数据库的各类数据查询操作
一、实验目的
掌握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 www./doc/3d4429586.html,o, Second.pcno 间接先行课from course First, course Second where First.pcno=www./doc/3d4429586.html,o
比较:
select www./doc/3d4429586.html,o, Second.pcno 间接先行课from course First, course Second where First.pcno=www./doc/3d4429586.html,o 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 www./doc/3d4429586.html,o=www./doc/3d4429586.html,o
5. 查询平均成绩在80分以上的学生姓名
Select sname from student，sc where student.sno=sc.sno GROUP BY sc.sno
HAVING ade)>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 www./doc/3d4429586.html,o='1' and
exists子查询www./doc/3d4429586.html,o='2'
使用带比较运算的子查询
1.查询比’刘晨’年龄小的所有学生的信息:
select * from student where sage<
(select sage from student where sname='刘晨')
使用带Any, All谓词的子查询
2.查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;
select sname, sage from student where sage <any< p="">
(select sage from student where sdept='IS')
and sdept<>'IS'
3.查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄:
select sname, sage from student where sage <all< p="">
(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=www./doc/3d4429586.html,o))
12. 查询至少选修了学生*****选修的全部课程的学生的学号:
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=www./doc/3d4429586.html,o))
13. 求没有人选修的课程号cno和cnamecname:
select cno,cname from course C where not exists
(select * from sc where www./doc/3d4429586.html,o=www./doc/3d4429586.html,o )
14. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno 的课程
select sno,cno from student,course where not exists
(select * from sc where cno=www./doc/3d4429586.html,o 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号课程的学生的学号:

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。