数据库原理实验报告(6)(含答案)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
南京晓庄学院
《数据库原理与应用》
课程实验报告
实验六分组统计查询和集合查询设计
所在院(系):数学与信息技术学院
班级：
学号：
姓名：
1.实验目的
(1)熟练掌握数据查询中分组条件表达、选择组条件的表达方法。
(2)熟练使用统计函数和分组函数。
(3)熟练各类计算和分组计算的查询操作方法。
(4)掌握集合查询的实现方法。
2.实验要求
(1)针对“TM”数据库，在SQL Server查询分析器中，用T-SQL语句实现以下查询操作：
a)查询各个院系学生的总人数，并按人数进行降序排列。
b)查询各系各门课程的平均成绩。
c)查询每个院系各种职称的教师人数，输出院系、职称、人数。
d)查询数学与信息技术学院学生的平均年龄。
e)查询07294003课程的最高分和最低分。
f)查询选修人数超过30人，且课程号以07开头的课程号、课程名称和选
修人数。按选修人数降序排列。
g)查询选修了5门以上课程的学生学号。
h)查询年龄大于女同学平均年龄的男同学姓名和年龄。
i)查询SC表中最高分与最低分之差大于20分的课程号。
j)查询平均成绩大于75分的课程的课程号、课程名、平均分。
k)查询期末考试平均分排名前10%的学生，输出学号和平均分。
l)查询教师人数最多的前3个院系，输出院系和教师人数。
m)查询全校老师和学生的姓名，输出姓名和类别两列（类别中显示教师或学生），结果按类别排序。
n)用集合查询实现同时讲授过07294003和07295007两门课的老师的工号。
o)用集合查询实现教师表中职称不是教授的老师的详情。
(2)按要求完成实验报告。
3.实验步骤、结果和总结实验步骤/结果
将调试成功的T-SQL语句写在下方（标明题号）。
a)查询各个院系学生的总人数，并按人数进行降序排列。
select DEPT_ID, COUNT(*) 总人数 from dbo.Student
group by DEPT_ID
order by DEPT_ID desc
b)查询各系各门课程的平均成绩。
select DEPT_ID, AVG(EXAM_Grade) as '平均成绩' from
dbo.SC,dbo.Student
where dbo.SC.S_ID=dbo.Student.S_ID
group by DEPT_ID
c)查询每个院系各种职称的教师人数，输出院系、职称、人数。
select DEPT_ID,Academic_Title,count(Academic_Title)as '人数' from dbo.Teacher
group by DEPT_ID,Academic_Title
d)查询数学与信息技术学院学生的平均年龄。
select AVG(year(getdate())-year(Date_of_Birth)) from Student,De partment
where Student.DEPT_ID=Department.DEPT_ID and DEPT_Name='数学与信息技术学院'
e)查询07294003课程的最高分和最低分。
select MAX(EXAM_Grade) as 最高分,MIN(EXAM_Grade) as 最低分 from SC
where C_ID='07294003'
f)查询选修人数超过30人，且课程号以07开头的课程号、课程名称和选修人数。按选修人数降序排列。
select Course.C_ID,Course.C_Name,count(*) 人数 from SC,Course where SC.C_ID=Course.C_ID and Course.C_ID like '07%'
group by Course.C_ID,Course.C_Name
having count(*)>30
order by count(*) desc
g)查询选修了5门以上课程的学生学号。
select S_ID from SC
group
y S_ID
having COUNT(*) >5
h)查询年龄大于##学平均年龄的男同学姓名和年龄。
select S_NAME,year(getdate())-year(Date_of_Birth) from dbo.student where Gender='男'and year(getdate())-year(Date_of_Birth)=
(select avg(year(getdate())-year(Date_of_Birth)) from dbo.student where Gender='女')
i)查询SC表中最高分与最低分之差大于20分的课程号。
select C_ID form SC
group by C_ID
having MAX(EXAM_Grade)-MIN(EXAM_Grade) >20
j)查询平均成绩大于75分的课程的课程号、课程名、平均分。
select SC.C_ID,C_Name,AVG_Grade from Student,SC,Course
where Student.S_ID=SC.S_ID and SC.C_ID=Course.C_ID and AVG_Grade>'75'
k)查询期末考试平均分排名前10%的学生，输出学号和平均分。
select top 10percent S_ID,avg(avg_grade) 平均成绩 from SC
group by S_ID
order by avg(avg_grade) desc
l)查询教师人数最多的前3个院系，输出院系和教师人数。
select top 3 Department.DEPT_ID, COUNT(*) 人数 from Teacher,Department where Teacher.DEPT_ID=Department.DEPT_ID