一、实验目的
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
)