SQL02

1：查询查询平均成绩大于70分的同学的学号和平均成绩

思路：先查询每一个学生的平均成绩，然后加上条件 大于70.

注意点：where 和 having 不能同时存在。

a:根据学生id分组来查询出每一个学生的平均成绩和id

select student_id ,avg(score) from studentcourse group by student_id

b:加上条件，此处不能使where。因为where后面不能跟聚合函数。

最终版本:

     SELECT student_id ,avg(score) from studentcourse group by student_id having avg(score) > 70

2: 查询所有同学的学号、姓名、选课数、总成绩.

这个其实就是子查询了，把一张表的结果当作一个虚拟子表，然后从这个虚拟表中再次查询。

SELECT student.id,student.name,t2.course,t2.sumScore from student ,

(select student_id,count(course_id) course,sum(score) sumScore from studentcourse group by student_id )t2

where student.id=t2.student_id;

思路：先从学生课程表中，根据学号分组，查询每一个学生的选课数目和总成绩。

然后从学生表和上面的结果表关联查询，判断学生表的id和这个关联表中的学生id是否一致。

重点：

虚拟表中一定要查询出学生的id，要不然 column 't2.student_id' in 'where clause'。

查询数目的时候采用count函数，意思是按照指定的列统计的时候，如果一个有null，就不会进行统计。

-- 3、查询没学过关羽老师课的同学的学号、姓名

思路：从教师表和课程表中，找出关羽老师所教授的课程的id

select course.id from couser where couser.id =(select id from teacher where name='关羽')。

2：从学生选课表中找出所选课程id是关羽老师所教授的课程的id的。

3：从学生表中排除掉这部分学生

select student.id,student.name from student where student.id not in

(select student_id from studentcourse where course_id in

     (select course.id from course where teacher_id =

              (select id from teacher where name = '关羽')

       )

)

-- 6、查询各科成绩最高和最低的分

select course_id,Max(score),Min(score) from studentcourse group by course_id

-- 5、查询没有学三门课以上的同学的学号、姓名

select id,name from student ,(SELECT student_id,count(course_id) from studentcourse group by student_id having count(course_id) < 3) t2

where student.id=t2.student_id

-- 8、查询上海和北京学生数量

select count(*),city from student where city in ('北京','上海') group by city;