Mysql-练习题

思路：
    获取所有有生物课程的人（学号，成绩） - 临时表
    获取所有有物理课程的人（学号，成绩） - 临时表
    根据【学号】连接两个临时表：
        学号  物理成绩   生物成绩
 
    然后再进行筛选

SELECT a_name,h_name,h_number FROM 
(select (student.student_name)as a_name, (class.class_name)as w_name, (score.number)as w_number from score
left join class on class.class_nid = class_sid
left join student on student_sid = student.student_nid
where class_name = ‘温泉‘) as a
LEFT JOIN
(select (student.student_name)as b_name, (class.class_name)as h_name, (score.number)as h_number  from score
left join class on class.class_nid = class_sid
left join student on student_sid = student.student_nid
where class_name = ‘化学‘) as b
on a_name = b_name
WHERE h_number > w_number 

SELECT a_name,avg(w_number) FROM 
(select (student.student_name)as a_name, (class.class_name)as w_name, (score.number)as w_number from score
left join class on class.class_nid = class_sid
left join student on student_sid = student.student_nid
) as a

GROUP BY a_name having avg(w_number) > 60

分组后对聚合函数进行操作 用having

SELECT * FROM 
(select (student.student_name)as a_name, (class.class_name)as w_name, (score.number)as w_number,(teacher.teacher_name)as t_name from score
left join class on class.class_nid = class_sid
left join student on student_sid = student.student_nid
left join teacher on class_teacher = teacher.teacher_nid
) as a
where t_name = ‘波多‘