数据库原理与应用实验-数据库查询实验

目录

一、实验目的

二、实验内容

三、实验环境

四、实验步骤

五、实验过程

1、 使用带IN谓词的子查询

2、使用带比较运算的子查询

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

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

5、使用聚集函数：

6、分组查询

---

实验报告
开课学院及实验室：
学 院			年级、专业、班		姓名		学号
实验课程名称		数据库原理与应用实验					成绩
实验项目名称		数据库查询实验					指 导     教 师

一、实验目的

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

二、实验内容

1、在SQL Server Query Analyzer中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。

2、分组查询实验。该实验包括分组条件表达、选择组条件表达的方法。

3、使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。

4、组合查询实验。

5、计算和分组计算查询的实验。

三、实验环境

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

三个数据表：Student、Course、SC

Sname：

Course：

SC：

四、实验步骤

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）查询与’王刚’在同一个系学习的学生的信息:
 

select * from Student
where Sdept in(select Sdept from Student where Sname='王刚')

（2）查询选修了课程名为’电子商务’ 的学生的学号和姓名:

select SC.Sno,student.Sname

from Student,SC

where cno in(select cno from Course where course.Cname='电子商务'and course.cno=sc.cno and Student.Sno=sc.Sno)

（3）查询选修了课程号’004’和课程号’012’的学生的学号:

select Sno

from Student

where Sno in(select Sno from SC where sc.Cno=4 ) and sno in(select sno from SC where sc.Cno=12)

2、使用带比较运算的子查询

(4) 查询比’王刚’年龄小的所有学生的信息:

select *

from Student

where Sage<(select Sage from Student where Sname='王刚')

/*前面插入王刚的年龄是20*/

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

（5）查询比计算机系某一学生年龄小的学生姓名和年龄;

select sname,sage

from Student

where Sage < any(select Sage from Student where Sdept='sc')

（6）查询其他系中比计算机系所有学生年龄都小的学生姓名和年龄:

select sname,sage

from Student

where Sage<all(select Sage from Student where Sdept='sc') and Sdept<>'sc'

（7）查询与计算机系所有学生的年龄均不同的学生学号, 姓名和年龄:

select sno,sname,sage

from Student

where sage<>all(select sage from Student where Sdept='sc')

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

（8） 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄:

这个可以转换一下，查询与其他学生年龄均不同的学生，就是查询的结果不存在年龄相同的学生，要排除自己和自己相同。

select sno,sname,sage

from Student as A

where not exists(select * from Student as B where A.Sage=B.Sage and A.Sno<>b.Sno)

（9） 查询所有选修了004号课程的学生姓名:

select sname

from Student

where exists (select * from SC where sno=Student.Sno and cno=4)

（10）查询没有选修004号课程的学生姓名:

这个就是在上面的exists前面加个not就是没选004的课的学生

select sname

from Student

where not exists (select * from SC where sno=Student.Sno and cno=4)

（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))

（12）查询至少选修了学生201105122选修的全部课程的学生的学号:

select distinct Sno

from SC as A

where not exists

    (select *

    from SC as B

    where sno='201105122' and not exists

        (select * from SC where sno=A.Sno and cno=B.Cno))

（13）求没有人选修的课程号和课程名:

select Cno,Cname

from Course as A

where not exists

    (select *

    from SC

    where SC.Cno=A.Cno)

5、使用聚集函数：

（14）查询学生总人数：

select COUNT (*) as 学生人数 from Student

（15）查询选修了课程的学生总数：

select COUNT (distinct Sno) as 选课学生 from SC

（16）查询所有课程的总学分数和平均学分数,以及最高学分和最低学分：

select sum(Course.Ccredit) as 总学分,AVG(Course.Ccredit) as 平均,max(course.Ccredit) as 最大,min(Course.Ccredit) as 最小 from Course

（17）计算201105124号课程的学生的平均成绩, 最高分和最低分:

select avg(sc.Grade) as 平均成绩,max(sc.Grade) as 最高分,min(sc.Grade) as 最低分 from SC

where sno=201105124

（18）查询’计算机系’学生”数据结构”课程的平均成绩:

select avg(sc.Grade) as 平均成绩

from Student,Course,SC

where Student.Sno=sc.Sno and Course.Cno=sc.Cno and Student.Sdept='sc' and Cname='数据结构'

（19）查询每个学生的课程成绩最高的成绩信息(学号,课程号,成绩):

select *

from SC AS A

WHERE Grade = (SELECT MAX(Grade) FROM SC WHERE Sno=A.Sno)

（20）求成绩低于该门课程平均成绩的学生的成绩信息(学号,课程号,成绩)

select *

from SC AS A

WHERE Grade<(SELECT avg(Grade) FROM SC WHERE cno=A.cno)

6、分组查询

（21）查询各系的学生的人数并按人数从多到少排序 :

select student.Sdept,COUNT(*) as 人数

from Student

group by Sdept

order by 人数 desc

（22）查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前:

select student.Sdept,COUNT(*) as 人数,Ssex as 性别

from Student

group by Sdept,Ssex

order by Sdept,Ssex desc

（23）查询选修了3门课程以上的学生的学号和姓名:

select sno,sname

from Student

where sno in

    (select sno from SC group by (sno) having count(*)>3)

（24）查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数:

select sno,avg(grade) as 平均分,max(grade) as 最高分,min(grade) as 最低分,count(*) as 选课数量

from SC

group by sno

（25）查询至少选修了2门课程的学生的平均成绩:

select sno,AVG(Grade) as 平均分

from SC

group by sno having count(*)>=2

（26）查询平均分超过80分的学生的学号和平均分:

select sno,AVG(Grade) as 平均分

from SC

group by sno having avg(Grade)>=80

（27）求各学生的60分以上课程的平均分:

select sno,AVG(Grade) as 平均分

from SC

where Grade>60

group by sno

（28）查询”计算机系”中选修了5门课程以上的学生的学号:

select sno

from sc

where sno in

    (select sno from Student where Sdept='SC')

group by sno having count(*)>5