《表的的分组查询和统计查询.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