【SQL Server数据库】带函数查询和综合查询（1）

目录

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

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

3.查询“0203”课程的最高分的学生的学号。

4．统计各系开设班级的数目(系名称、班级数目)，并创建结果表。

5．选修了以“01”开头的课程的学生学号，姓名，选课的课程号。

7．所有成绩都在70分以上的学生姓名。

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

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

---

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

/*(1)*/select count(*) '年龄大于30岁的学生人数'from Student swhere year(getdate()) - year(Birthday) > 30;/*(2)*/select count(*) '年龄大于30岁的学生人数'from Student swhere DATEDIFF(year,Birthday,getdate()) > 30;

查询结果：

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

/*嵌套查询*/select count(*) '数据结构成绩大于或等于80的人数'from StudentGradewhere Grade >= 80and Course_id in (select Course_id from Course where Course_name = '数据结构');/*连接查询*/select count(*) '数据结构成绩大于或等于80的人数'from Course c,StudentGrade sgwhere c.Course_name = '数据结构'and c.Course_id = sg.Course_idand sg.Grade >= 80;

查询结果：

3.查询“0203”课程的最高分的学生的学号。

（请分别用TOP1和函数来进行查询，并分析它们的区别）

-- TOP1查询select top 1 Stu_idfrom StudentGradewhere Course_id = '0203'order by Grade desc;-- 函数查询select Stu_idfrom StudentGrade sgwhere Course_id = '0203'and Grade = (select max(Grade) from StudentGrade where Course_id = '0203');

        查询结果：

4．统计各系开设班级的数目(系名称、班级数目)，并创建结果表。

（需考虑没有班级的系）

/*根据对应的class_id,如果没有班级就是为null*/select d.Depar_name, count(cl.Class_id) 'classCount'into Depar_classfrom Deparment dleft join Class cl on (d.Depar_id = cl.Depar_id)group by d.Depar_name;

运行结果：

5．选修了以“01”开头的课程的学生学号，姓名，选课的课程号。

select distinct s.Stu_id, s.Stu_name, sg.Course_idfrom Student s,StudentGrade sgwhere sg.Course_id like '01%'and s.Stu_id = sg.Stu_id;

部分查询结果：

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

select c.Course_name, max(Grade) '最高分', min(Grade) '最低分', avg(Grade) '平均分', sum(Grade) '总分'from StudentGrade sg,Course cwhere sg.Course_id = c.Course_idgroup by c.Course_name;

部分查询结果：

/*没人选的科目也查出来*/select c.Course_name, max(Grade) '最高分', min(Grade) '最低分', avg(Grade) '平均分', sum(Grade) '总分'from Course cleft join StudentGrade sg on c.Course_id = sg.Course_idgroup by c.Course_name;

部分查询结果：

7．所有成绩都在70分以上的学生姓名。

（提示：使用子查询。需考虑未选课的学生）

select distinct s.Stu_namefrom Student s,StudentGrade sgwhere not exists(select sg.Gradefrom StudentGrade sgwhere sg.Stu_id = s.Stu_idand (sg.Grade is null or sg.Grade < 70))and s.Stu_id = sg.Stu_id;/*最低的一门成绩高于70*/select s.Stu_namefrom Student swhere Stu_id in (select sg.Stu_idfrom StudentGrade sggroup by sg.Stu_idhaving min(Grade) > 70);

查询结果：

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

（提示：使用子查询）。

select s.Stu_id, s.Stu_name, d.Depar_namefrom Student s,Class cl,Deparment d,StudentGrade sg,Course cwhere s.Stu_id = sg.Stu_idand d.Depar_id = cl.Depar_idand s.Class_id = cl.Class_idand sg.Course_id = c.Course_idand c.Course_name='数据库'and sg.Grade in (select max(Grade)from StudentGradewhere Course_id = (select Course_id from Course where Course_name = '数据库'));

查询结果：

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

select Stu_name, Stu_sexfrom Studentwhere Stu_id in (select Stu_id from StudentGrade group by Stu_id having count(*) > 2);

部分查询结果：