{access数据库}查询典型语句(2)

select avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分 from scwhere cno='1'

5. 查询’信息系’(IS)学生”数据结构”课程的平均成绩:

select avg(grade) from student, course, sc where student.sno=sc.sno and

(select max(grade) from sc where sno=A.sno )

7*. 求成绩低于该门课程平均成绩的学生的成绩信息(sno,cno,grade)

select * from grade A where grade=

(select avg(grade) from sc where cno=o )

二. 分组查询

8. 查询各系的学生的人数并按人数从多到少排序 :

selectsdept, 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)

1选课门数:

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(*)>=2