1、我们想通过查询得到每个学生选了几门课程,此时我们就可以联合使用COUNT函数与GROUP BY语句来得到这一结果
SELECT id, COUNT(course) as numcourse
FROM student
GROUP BY id
2、想查询平均分高于80分的学生记录可以这样写:
SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore
FROM student
GROUP BY id
HAVING AVG(score)>=80;
3、查询两门以上不及格课程的同学的学号及其平均成绩
select S#,avg(isnull(score,0)) from SC
where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;