数据库实验三：综合查询.doc

实验3综合查询

一、实验目的
进一步掌握SQL Server Query Analyzer的使用方法，加深对SQL Server语言查询语句的理解。熟练掌握数据查询中嵌套查询、分组、统计、计算和组合查询等高级查询的操作方法，并学会综合运用。

二、实验内容
1、在SQL Server Query Analyzer中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。
2、分组查询实验。该实验包括分组条件表达、选择组条件表达的方法。
3、使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。
4、组合查询实验。
5、计算和分组计算查询的实验。

三、实验步骤
1、检查Microsoft SQL Server服务器已启动；
2、进入SQL Server Management Studio，启动查询分析工具；
3、将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。

四、实验方法
1、 使用带IN谓词的子查询
（1）查询与’王刚’在同一个系学习的学生的信息:
（2）查询选修了课程名为’电子商务’ 的学生的学号和姓名:
（3）查询选修了课程号’004’和课程号’012’的学生的学号:
2、使用带比较运算的子查询
(4) 查询比’王刚’年龄小的所有学生的信息:
3、 使用带Any, All谓词的子查询
（5）查询比计算机系某一学生年龄小的学生姓名和年龄;
（6）查询其他系中比计算机系所有学生年龄都小的学生姓名和年龄:
（7）查询与计算机系所有学生的年龄均不同的学生学号, 姓名和年龄:
4、 使用带Exists谓词的子查询和相关子查询
（8） 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄:
（9） 查询所有选修了004号课程的学生姓名:
（10） 查询没有选修004号课程的学生姓名:
（11）查询选修了全部课程的学生姓名:
（12） 查询至少选修了学生20040002选修的全部课程的学生的学号:
（13） 求没有人选修的课程号和课程名:
5、使用聚集函数：
（14）查询学生总人数：
（15）查询选修了课程的学生总数：
（16）查询所有课程的总学分数和平均学分数,以及最高学分和最低学分：
（17）计算004号课程的学生的平均成绩, 最高分和最低分:
（18）查询’计算机系’学生”数据结构”课程的平均成绩:
（19）查询每个学生的课程成绩最高的成绩信息(学号,课程号,成绩):
（20）求成绩低于该门课程平均成绩的学生的成绩信息(学号,课程号,成绩)
6、分组查询
（21）查询各系的学生的人数并按人数从多到少排序 :
（22） 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前:
（23） 查询选修了3门课程以上的学生的学号和姓名:
（24）查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数:
（25）查询至少选修了2门课程的学生的平均成绩:
（26）查询平均分超过80分的学生的学号和平均分:
（27）求各学生的60分以上课程的平均分:
（28） 查询”计算机系”中选修了5门课程以上的学生的学号:

五、实验环境
PC计算机，Microsoft Windows操作系统，Microsoft SQL Server数据库管理系统个人版、标准版或企业版。

六、实验要求
1、根据实验内容和每一步骤实验的结果，按课程实验报告的撰写规范完成实验报告。报告可用手写或打印，格式要规范。
2、实验报告至少包括以下内容：①实验目的；②实验环境；③实验内容、步骤、结果和实验过程中出现的问题；④实验过程中主要使用的Transact-SQL语句。

附语句参考：
1、 使用带IN谓词的子查询
（1）查询与’王刚’在同一个系学习的学生的信息:
select * from 学生 where 系编号 in
(select 系别 from 学生 where 姓名='王刚')
（2）查询选修了课程名为’电子商务’ 的学生的学号和姓名:
select 学号, 姓名 from 学生 where 学号 in
(select 学号 from 选修 where 课程号 in
(select 课程号 from 课程 where课程名='电子商务'))
（3）查询选修了课程号’004’和课程号’012’的学生的学号:
select 学号 from 学生 where 学号 in (select 学号 from 选修 where 课程号='004')
and 学号 in (select 学号 from 选修 where 课程号='012')
2、使用带比较运算的子查询
(4) 查询比’王刚’年龄小的所有学生的信息:
select * from 学生 where 年龄<
(select 年龄 from 学生 where 姓名='王刚')
3、 使用带Any, All谓词的子查询
（5）查询比计算机系任一学生年龄小的学生姓名和年龄;
select 姓名, 年龄 from 学生 where 年龄 <Any
(select 年龄 from 学生 where 系编号 in
（select 系编号 from 系别 where 系名=‘计算机系’）)
（6）查询其他系中比计算机系所有学生年龄都小的学生姓名和年龄:
select姓名, 年龄from学生where年龄 <ALL
(select年龄from 学生 where系编号 in
（select 系编号 from 系别 where 系名=‘计算机系’）)
and系编号 not in
（select 系编号 from 系别 where 系名=‘计算机系’）)
（7）查询与计算机系所有学生的年龄均不同的学生学号, 姓名和年龄:
select 学号,姓名,年龄 from 学生 where 年龄<>all
(select年龄from 学生 where系编号 in
（select 系编号 from 系别 where 系名=‘计算机系’）)
4、 使用带Exists谓词的子查询和相关子查询
（8） 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄:
select 学号,姓名,年龄 from 学生 A where not exists
(select * from 学生 B where A.年龄=B.年龄 and A.学号<>B.学号)
（9） 查询所有选修了004号课程的学生姓名:
select 姓名 from 学生 where exists
(select * from 选修 where 学号=学生.学号 and 课程号='004')
（10） 查询没有选修004号课程的学生姓名:
select 姓名 from 学生 where not exists
(select * from 选修 where 学号=学生.学号 and 课程号='004')
（11）查询选修了全部课程的学生姓名:
select 姓名 from 学生 where not exists
(select * from 课程 where not exists
( select * from 选修 where 学号=学生.学号 and 课程号=课程.课程号))
（12） 查询至少选修了学生20040002选修的全部课程的学生的学号:
select distinct 学号 from 选修 A where not exists
(select * from 选修 B where 学号='20040002'and not exists
(select * from 选修 C where 学号=A.学号 and 课程号=B.课程号))
（13） 求没有人选修的课程号课程号和课程名:
select 课程号,课程名 from 课程 C where not exists
(select * from 选修 where 选修.课程号=C.课程号 )
5、使用聚集函数：
（14）查询学生总人数：
Select Count(*) as 学生总数 from 学生
（15）查询选修了课程的学生总数：
select count(distinct 学号) as 选课学生总数 from 选修
（16）查询所有课程的总学分数和平均学分数,以及最高学分和最低学分：
select sum(学分) as 总学分,avg(学分) as 课程平均学分,max(学分) as 最高学分,
min(学分) as 最低学分 from 课程
（17）计算004号课程的学生的平均成绩, 最高分和最低分:
select avg(成绩) as 平均成绩,max(成绩) as 最高分, min(成绩) as 最低分
from 选修where 课程名='004'
（18）查询’计算机系’学生”数据结构”课程的平均成绩:
select avg(成绩) from 学生, 课程, 选修，系别 where 学生.学号=选修.学号 and
课程.课程号=选修.课程号 and 学生.系编号=系别.系编号and 系名='计算机系' and 课程名='数据结构'
（19）查询每个学生的课程成绩最高的成绩信息(学号,课程号,成绩):
select * from 选修 A where grade =
(select max(成绩) from 选修 where 学号=A.学号 )
（20）求成绩低于该门课程平均成绩的学生的成绩信息(学号,课程号,成绩)
select * from 选修 A where grade <
(select avg(成绩) from 选修 where 课程号=A.课程号 )
6、分组查询
（21）查询各系的学生的人数并按人数从多到少排序 :
Select 系编号, Count(*) as 人数 from 学生 group by 系编号 order by 人数 desc
（22） 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前:
select 系编号,性别,Count(*) as 人数 from 学生 group by 系编号, 性别 order by 系编号,性别 desc
（23） 查询选修了3门课程已上的学生的学号和姓名:
select 学号, 姓名 from 学生 where 学号 in
(select 学号 from 选修 group by (学号) having count(*)>3)
（24）查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数:
select 学号, avg(成绩) as 平均成绩,max(成绩) as 最高分, min(成绩) as 最低分,
count(*) as 选课门数 from 选修 group by 学号
（25）查询至少选修了2门课程的学生的平均成绩:
select 学号, avg(成绩) as 平均成绩, from 选修 group by 学号 having count(*)>=2
（26）查询平均分超过80分的学生的学号和平均分:
Select 学号, avg(成绩) as 平均成绩from 选修 group by 学号 having avg(*)>=80
（27）求各学生的60分以上课程的平均分:
select 学号, avg(成绩) as 平均成绩 from 选修 where 成绩>=60 group by 学号
（28） 查询”计算机系”中选修了5门课程以上的学生的学号:
select 学号 from 选修 where 学号 in (select 学号 from 学生where 系编号in
（select 系编号 from 系别 where 系名=‘计算机系’）) group by 学号 having count(*)>=5