SQL实验 带函数查询和综合查询

一、实验目的

1．掌握Management Studio的使用。

2．掌握带函数查询和综合查询的使用。

二、实验内容及要求

1．统计年龄大于30岁的学生的人数。

--统计年龄大于30岁的学生的人数。SELECT COUNT(*) AS 人数FROM StudentWHERE (datepart(year,getdate())-datepart(year,Birthday))>30

2．统计数据结构有多少人80分或以上。

--统计数据结构有多少人80分或以上。SELECT COUNT(*) AS 人数FROM StudentGradeWHERE Course_id IN(SELECT Course_idFROM CourseWHERE Course_name='数据结构')AND Grade>80

3.查询“0203”课程的最高分的学生的学号。（请分别用TOP1和函数来进行查询，并分析它们的区别）

--查询“0203”课程的最高分的学生的学号。（请分别用TOP1和函数来进行查询，并分析它们的区别）--Top 1SELECT Top 1 Stu_idFROM StudentGradeWHERE Course_id='0203'--Max 函数SELECT Stu_idFROM StudentGradeWHERE Grade=(SELECT Max(Grade) FROM StudentGrade)AND Course_id='0203'

4．统计各系开设班级的数目(系名称、班级数目)，并创建结果表。（需考虑没有班级的系）

--统计各系开设班级的数目(系名称、班级数目)，并创建结果表。（需考虑没有班级的系）select Depar_name as 系名称,count(*) as 班级数目 into DeparNumberfrom Deparment LEFT JOIN Class ON Deparment.Depar_id=Class.Depar_idgroup by Depar_name

5．统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。（TOP 3，但不能处理人数并列的情况。试考虑更好的方法。）

--统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。（TOP 3，但不能处理人数并列的情况。试考虑更好的方法。）--Top 3SELECT Top 3 Course_id ,COUNT(*) AS 选修人数FROM StudentGradeGROUP BY Course_id ORDER BY 选修人数 DESC--优化方案
--内部查询首先在选课表中进行分组，按照课程编号计算每门课程的选修人数，并使用 RANK() 窗口函数为每个课程编号进行排名，从而获得对应的排名号。--外部查询该查询从内部查询的结果集（被命名为结果表）获取每门课程的编号和选修人数，并选择排名前三名的课程数据输出。SELECT Course_idFROM (SELECT Course_id,COUNT(*) AS 选修人数,RANK() OVER (ORDER BY COUNT(*) DESC) AS 名次FROM StudentGradeGROUP BY Course_id) AS 结果表WHERE 结果表.名次<=3

6．统计每科目的最高分、最低分，平均分、总分，并以中文列名显示。

--统计每科目的最高分、最低分，平均分、总分，并以中文列名显示。SELECTCourse.Course_name AS 学科,MAX (StudentGrade.Grade) AS 最高分,MIN (StudentGrade.Grade) AS 最低分,AVG (StudentGrade.Grade) AS 平均分,SUM (StudentGrade.Grade) AS 总分FROM Course JOIN StudentGrade ON Course.Course_id=StudentGrade.Course_idGROUP BY Course.Course_name

7．【选做】所有成绩都在70分以上的学生姓名（提示：使用子查询。需考虑未选课的学生）。

--【选做】所有成绩都在70分以上的学生姓名（提示：使用子查询。需考虑未选课的学生）。--这里使用了子查询来查找未选课的学生，并过滤掉成绩低于70分的学生。如果没有任何一门成绩低于70分，则该学生将被返回SELECT Student.Stu_nameFROM StudentWHERE NOT EXISTS (SELECT *FROM StudentGradeWHERE StudentGrade.Stu_id = Student.Stu_id AND StudentGrade.Grade < 70)

8．【选做】“数据库”课程得最高分的学生的学号、姓名和所在系。
 

--【选做】“数据库”课程得最高分的学生的学号、姓名和所在系。--材料无“数据库”将其改为“数据库原理”SELECT Top 1 Student.Stu_id,Student.Stu_name,Depar_nameFROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_idJOIN Class ON Class.Class_id=Student.Class_idJOIN Deparment ON Class.Depar_id=Deparment.Depar_idJOIN Course ON StudentGrade.Course_id=Course.Course_idWHERE Course_name='数据库原理'GROUP BY Student.Stu_id,Student.Stu_name,Depar_nameORDER BY MAX(StudentGrade.Grade)DESC

9．【选做】至少选修了两门课及以上的学生姓名和性别。
 

--【选做】至少选修了两门课及以上的学生姓名和性别。
--内部子查询先对学生进行分组（GROUP），并求出（COUNT）每个学生选修不同科目的数量，再（HAVING）筛出符合条件的学生SELECT DISTINCT Stu_name ,Stu_sexFROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_idJOIN Course ON StudentGrade.Course_id=Course.Course_idWHERE Student.Stu_id IN (SELECT StudentGrade.Stu_idFROM StudentGradeGROUP BY StudentGrade.Stu_idHAVING COUNT(DISTINCT StudentGrade.Course_id) >= 2)