sql 实验报告

实验5 SQL交互式查询——分组统计查询与连接查询

实验日期和时间：2013/6/5 实验室：14#314

班级：管科0班学号：20111034 姓名：倪龙

实验环境：

1.硬件：window 7旗舰版，处理器：Intel®Core(TM) i3-2350 CPU@

2.30GHz

2.30GHz

安装内存：4.00G (3.07G可用)

2. 软件：Microsoft SQL server 2005

实验主要任务：

在“考试管理”数据库中，用SQL语句完成下列查询任务

1.基于“生源情况”表，写出完成下列查询的SELECT语句：

a)统计不同的姓的人数，列出：姓、人数，结果按人数降序排序。

b)按地区分类统计“入学成绩”的平均值、最大值、最小值，并按平均值降

序排序。

c)找出年龄平均值最高的班级。

d)列出学生人数小于100的地区。

2.按“考试管理”数据库中的各个表，写出完成下列查询的SELECT语句：

a)列举学号为“200894006”的同学所参加的考试的场次、教室和课程名称。

b)列举学号为“200894006”的同学所参加的每场考试的监考教师的姓名。

c)列举来自“浙江”的学生的所在学院名称、班级名称、学号和姓名。

d)查询“梁子平”同学所在班级的学生人数。

3.按“考试管理”数据库中的各个表，写出利用子查询完成的下列查询的SELECT

语句：

a)找出与学号“200894006”的学生同学院且同年出生的学生。

b)列举班级数少于20的那些学院的名称和班级数。

c)列举教师“罗亮”所监考的学生来自哪此班级。

d)找出“7月01日08：30-10：30”这一场次没有监考任务的教师。

以下内容填写请利用截屏图片和文字对实验原理和实验实际效果进行说明

查询1：统计不同的姓的人数，列出：姓、人数，结果按人数降序排序。

代码：select count(left(姓名,1))人数

from生源情况

group by(left(姓名,1))

order by人数desc

执行结果：

查询2：按地区分类统计“入学成绩”的平均值、最大值、最小值，并按平均值降序排序。代码：select avg(入学成绩)平均成绩,max(入学成绩)最高分数,min(入学成绩)最低分数from生源情况

group by地区

order by avg(入学成绩)desc

执行结果：

查询3：按地区分类统计“入学成绩”的平均值、最大值、最小值，并按平均值降序排序。代码：select avg(入学成绩)平均成绩,max(入学成绩)最高分数,min(入学成绩)最低分数from生源情况

group by地区

order by avg(入学成绩)desc

执行结果：

查询4：列出学生人数小于100的地区。

代码：select地区

from生源情况

group by地区

having count(*)<100

执行结果：

查询5：列举学号为“200894006”的同学所参加的考试的场次、教室和课程名称。代码：select场次,课程编号,教室或者

from学生考试

where学号='200894006'

执行结果：

查询6：列举学号为“200894006”的同学所参加的每场考试的监考教师的姓名。代码：select姓名

from学生考试,教师监考,教师

where学号='200894006'and学生考试.场次=教师监考.场次

and学生考试.课程编号=教师监考.课程编号and学生考试.教室=教师监考.教室

and教师监考.教师编号=教师.教师编号

执行结果:

查询7：列举来自“浙江”的学生的所在学院名称、班级名称、学号和姓名。代码：select学院名称,班级名称,学号,姓名

from学生,学院,班级

where学生.地区='浙江'and班级.学院编号=学院.学院编号

and学生.班级编号=班级.班级编号

执行结果：

查询8：查询“梁子平”同学所在班级的学生人数。

代码：select班级人数

from学生,班级

where学生.姓名='梁子平'and学生.班级编号=班级.班级编号

执行结果：

查询9：找出与学号“200894006”的学生同学院且同年出生的学生。代码：select姓名

from生源情况

where year(出生日期)=(select year(出生日期)

from生源情况

where学号='200894006')and学院名称=(select学院名称

from生源情况

where学号='200894006')

执行结果：

查询10：列举班级数少于20的那些学院的名称和班级数。

代码：select学院名称,(select count(*)

from班级

where学院.学院编号=班级.学院编号)as班级数

from学院

where(select count(*)

from班级

where学院.学院编号=班级.学院编号)<20

执行结果：

查询11：3c) 列举教师“罗亮”所监考的学生来自哪此班级。代码：select distinct班级名称

from班级,学生

where学生.班级编号=班级.班级编号and学号in(

select学号

from学生考试

where场次in(select场次

from教师监考

where教师编号in

(select教师编号

from教师

where姓名='罗亮') and

课程编号in(select课程编号

from教师监考

where教师编号in(select教师编号

from教师

where姓名='罗亮')and

教室in(select教室

from教师监考

where教师编号in(select教师编号

from教师

where姓名='罗亮')

))

执行结果