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

一、实验目的

1．掌握Management Studio的使用。

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

二、实验内容及要求

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

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

SELECT COUNT(*) AS 人数

FROM Student

WHERE (datepart(year,getdate())-datepart(year,Birthday))>30

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

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

SELECT COUNT(*) AS 人数

FROM StudentGrade

WHERE Course_id IN(SELECT Course_id

FROM Course

WHERE Course_name='数据结构'

)

AND Grade>80

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

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

--Top 1

SELECT Top 1 Stu_id

FROM StudentGrade

WHERE Course_id='0203'

--Max 函数

SELECT Stu_id

FROM StudentGrade

WHERE Grade=(SELECT Max(Grade) FROM StudentGrade)

AND Course_id='0203'

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

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

select Depar_name as 系名称,count(*) as 班级数目 into DeparNumber

from Deparment LEFT JOIN Class ON Deparment.Depar_id=Class.Depar_id

group by Depar_name

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

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

--Top 3

SELECT Top 3 Course_id ,COUNT(*) AS 选修人数

FROM StudentGrade

GROUP BY Course_id ORDER BY 选修人数 DESC

--优化方案
--内部查询首先在选课表中进行分组，按照课程编号计算每门课程的选修人数，并使用 RANK() 窗口函数为每个课程编号进行排名，从而获得对应的排名号。

--外部查询该查询从内部查询的结果集（被命名为结果表）获取每门课程的编号和选修人数，并选择排名前三名的课程数据输出。

SELECT Course_id

FROM (SELECT Course_id,COUNT(*) AS 选修人数,

RANK() OVER (ORDER BY COUNT(*) DESC) AS 名次

FROM StudentGrade

GROUP BY Course_id) AS 结果表

WHERE 结果表.名次<=3

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

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

SELECT

Course.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_id

GROUP BY Course.Course_name

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

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

--这里使用了子查询来查找未选课的学生，并过滤掉成绩低于70分的学生。如果没有任何一门成绩低于70分，则该学生将被返回

SELECT Student.Stu_name

FROM Student

WHERE NOT EXISTS (

    SELECT *

    FROM StudentGrade

    WHERE StudentGrade.Stu_id = Student.Stu_id AND StudentGrade.Grade < 70

)

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

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

--材料无“数据库”将其改为“数据库原理”

SELECT Top 1 Student.Stu_id,Student.Stu_name,Depar_name

FROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_id

JOIN Class ON Class.Class_id=Student.Class_id

JOIN Deparment ON Class.Depar_id=Deparment.Depar_id

JOIN Course ON StudentGrade.Course_id=Course.Course_id

WHERE Course_name='数据库原理'

GROUP BY Student.Stu_id,Student.Stu_name,Depar_name

ORDER BY MAX(StudentGrade.Grade)DESC

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

--【选做】至少选修了两门课及以上的学生姓名和性别。
--内部子查询先对学生进行分组（GROUP），并求出（COUNT）每个学生选修不同科目的数量，再（HAVING）筛出符合条件的学生

SELECT DISTINCT Stu_name ,Stu_sex

FROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_id

JOIN Course ON StudentGrade.Course_id=Course.Course_id

WHERE Student.Stu_id IN (

    SELECT StudentGrade.Stu_id

    FROM StudentGrade

    GROUP BY StudentGrade.Stu_id

    HAVING COUNT(DISTINCT StudentGrade.Course_id) >= 2

)