实验六分组统计查询和集合查询设计.doc

南 京 晓 庄 学 院 《数据库原理与应用》 课程实验报告 实验六 分组统计查询和集合查询设计 所在院(系): 数学与信息技术学院 班级： 学号： 姓名： 1.实验目的 熟练掌握数据查询中分组条件表达、选择组条件的表达方法。 熟练使用统计函数和分组函数。 熟练各类计算和分组计算的查询操作方法。 掌握集合查询的实现方法。 2.实验要求 针对 “TM”数据库，在SQL Server查询分析器中，用T-SQL语句实现以下查询操作： 查询各个院系学生的总人数，并按人数进行降序排列。 查询各系各门课程的平均成绩。 查询每个院系各种职称的教师人数，输出院系、职称、人数。 查询数学与信息技术学院学生的平均年龄。 查程的最高分和最低分。 查询选修人数超过30人，且课程号以07开头的课程号、课程名称和选修人数。按选修人数降序排列。 查询选修了5门以上课程的学生学号。 查询年龄大于女同学平均年龄的男同学姓名和年龄。 查询SC表中最高分与最低分之差大于20分的课程号。 查询平均成绩大于75分的课程的课程号、课程名、平均分。 查询期末考试平均分排名前10%的学生，输出学号和平均分。 查询教师人数最多的前3个院系，输出院系和教师人数。 查询全校老师和学生的姓名，输出姓名和类别两列（类别中显示教师或学生），结果按类别排序。 用集合查询实现同时讲授07295007两门课的老师的工号。 用集合查询实现教师表中职称不是教授的老师的详情。 按要求完成实验报告。 3.实验步骤、结果和总结实验步骤/结果 将调试成功的T-SQL语句写在下方（标明题号）。 (1) 针对 “TM”数据库，在SQL Server查询分析器中，用T-SQL语句实现以下查询操作： a) 查询各个院系学生的总人数，并按人数进行降序排列。 select DEPT_ID, COUNT(*) 总人数from Student group by DEPT_ID order by COUNT(*) desc b) 查询各系各门课程的平均成绩。 select s.dept_id,sc.c_id,c_name,avg(exam_grade) as avg_grade from sc,course c,student s where sc.c_id=c.c_id and s.s_id=sc.s_id group by s.dept_id,sc.c_id,c_name c) 查询每个院系各种职称的教师人数，输出院系、职称、人数。 select DEPT_ID,Academic_Title,count(Academic_Title)as 人数 from Teacher group by DEPT_ID,Academic_Title d) 查询数学与信息技术学院学生的平均年龄。 select AVG(year(getdate())-year(Date_of_Birth)) as 平均年龄from Student,Department where Student.DEPT_ID=Department.DEPT_ID and DEPT_Name=数学与信息技术学院 select MAX(EXAM_Grade) as 最高分,MIN(EXAM_Grade) as 最低分from SC where C_IDelect 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(*) descselect s.s_id from student s join sc on s.s_id=sc.s_id group by s.s_id having count(*)5; h) 查询年龄大于女同学平均年龄的男同学姓名和年龄。 select S_NAME,year(getdate())-year(Date_of_Birth)as年龄 from student where Gender=男and year(getdate())-year(Date_of_Birth) (select avg(year(getdate())-year(Date_of_Birth)) from student where Gender=女)select c_id f