《表的的分组查询和统计查询.doc》由会员分享，可在线阅读，更多相关《表的的分组查询和统计查询.doc（4页珍藏版）》请在金锄头文库上搜索。
1、1、使用聚集函数u 查询学生总人数select count(*)from studentu 查询选修了课程的学生总数；select count(distinct sno)from scu 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分；select sum(cast(credit as int) as 总credit,avg(cast(credit as int) as 课程平均学分,max(credit) as 最高学分,min(credit) as 最低学分from courseu 计算1号课程的学生的平均成绩, 最高分和最低分select avg(grade)as 平均成绩,
2、 max(grade) as 最高分,min(grade) as 最低分from sc where cno=1u 查询信息系(IS)学生”数据结构”课程的平均成绩；select avg(grade) from student,course,scwhere student.sno=o and o=o and sdept=IS and cname=数据结构;u 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade) ；select *from sc Awhere grade=(select max(grade)from sc where sno=A.sno)u 求成绩低于该门课程平均成
3、绩的学生的成绩信息(sno,cno,grade)。select *from sc Awhere grade=(select avg(grade)from scwhere cno=A.cno)2、分组查询u 查询各系的学生的人数并按人数从多到少排序select sdept,count(*) as 人数from student group by sdept order by 人数descu 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前select sdept,ssex,count(*) as 人数from student group by sdept, ssex order by
4、sdept,ssex descu 查询选修了3门课程已上的学生的学号和姓名select sno,snamefrom student where sno in(select sno from sc group by(sno) having count(*)3)u 查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数select sno,avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分,count(*) as 选课门数from sc group by snou 查询至少选修了2门课程的学生的平均成绩select sno,avg
5、(grade) as 平均成绩from sc group by sno having count(*)=2u 查询平均分超过80分的学生的学号和平均分；select sno,avg(grade) as 平均成绩from sc group by sno having avg(grade)=80u 查询”信息系”(IS)中选修了5门课程以上的学生的学号。select sno from sc where sno in (select sno from student where sdept=IS group by sno having count(*)=2 )3、集合查询u 查询数学系和信息系的学生的信息select *from student where sdept=MA union select * from student where sdept=ISu 查询选修了1号课程或2号课程的学生的学号。select snofrom scwhere cno=1union select snofrom sc where cno=2
