实验6 数据库的分组查询和统计查询
-
6.1实验目的及要求
熟练掌握数据查询中的分组、统计、计算和集合的操作方法
-
6.2实验内容
使用聚集函数查询、分组计算查询、集合查询
- 6.3实验步骤
6.3.1使用聚集函数
1.查询学生总人数；
Select Count(*) as 学生总数 from student;
2.查询选修了选修课程的学生总数
Select count(distinct sno)as 选课学生总数 from sc;
3.查询所有课程的总学分数和平均学分数，以及最高学分和最低学分
Select sum(credit) as 总credit,avg(credit) as 课程平均分，max(credit) as 最高学分，min(credit) as 最低分学 from course;
4.计算1号课程的学生的平均成绩，最高分和最低分；
Select avg(grade) as 平均成绩，max(grade) as 最高分，min(grade) as 最低分 from sc where cno=’1’;
5.查询‘信息系’（IS）学生“数据结构”课程的平均成绩：
Select avg(grade) from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and sdept=’IS’and cname=’数据结构’;
6.查询每个学生的课程成绩最高的成绩信息（sno,cno,grade）;
Select * from sc A where grade=(select max(grade) from sc where sno=A.sno);
7.求成绩低于该门课程平均成绩的学生的成绩信息（sno,cno,grade）
Select * from sc A where grade=(select avg(grade) from sc where cno=A.cno);
6.3.2分组查询
8.查询各系的学生的人数并按人数从多到少排序；
Select sdept,Count(*) as 人数 from student group by sdept order by 人数 desc;
9.查询各系的男女生学生总数，并按系别，升序排列，女生排在前面；
Select sdept,ssex,Count(*) as 人数 from student group by sdept,ssex order by sdept,ssex desc;
10.查询选修了3门课程已上的学生的学号和姓名；
select sno,sname from student where sno in(select sno from sc group by(sno)having count(
)>3);
11.查询每个学生所选课程的平均成绩，最高分，最低分，和选课门数；
Select sno,avg(grade) as 平均成绩，max(grade) as 最高分，min(grade) as 最低分，
Count（
）as 选课门数 from sc group by sno;
12.查询至少选修了2门课程的学生的平均成绩：
Select sno,avg(grade)as 平均成绩 from sc group by sno having count(sno)>=2;
13.查询平均分超过80分的学生的学号和平均分：
Select sno,avg(grade) as 平均成绩 from sc group by sno having avg(grade)>=80;
比较：求各学生60分以上课程的平均分：
Select sno,avg(grade) as 平均成绩 from sc where grade>=60 group by sno;
14.查询“信息系”（IS）中选修了5门课程以上的学生的学号：
Select sno from sc where sno in(select sno from student where sdept=’IS’)group by sno having count(*)>=2;
6.3.3集合查询
15.查询数学系和信息系的学生的信息；
Select * from student where sdept=’网络工程’union select * from student where sdept’软件工程’;
16.查询选修了1号课程或2号课程的学生的信息；
Select sno from sc where cno=’1’;
Union
Select sno from sc where cno=’2’;
思考：
1.用两种方法查询平均成绩少于80分的学生的学号。
Select sno from sc group by sno having avg(grade)<80;
Select distinct sno from sc a where(select avg(grade)from sc where sno=a.sno)<80;
2.求各系的“数学”课程的成绩最高的学生的姓名和成绩
Select sname,grade from student A,sc B where A.sno=B.sno and B.grade in(select max(grade) from sc,course,student where student.sno=sc.sno=sc.sno snd sc.cno=course.cno and cname=’数学’group by sdept);