day37 mysql练习题自己的答案

3.查询平均成绩大于60分的同学的学号和平均成绩；姓名
SELECT student.s_id,a,student.s_name FROM
(select s_id,avg(s_score)as a from score GROUP BY s_id having avg(s_score)>60) as y
LEFT JOIN student on y.s_id=student.s_id

5.查询姓“李”的老师的个数；
SELECT * FROM  teacher WHERE t_name LIKE '李%'

7.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT b.s_id,s_name FROM (SELECT a.s_id FROM 
(SELECT s_id,c_id FROM score WHERE c_id=01 or c_id=02) as a
GROUP BY a.s_id
HAVING count(a.s_id)>1) as b
left outer join student on student.s_id=b.s_id

8.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT a.s_id,s_name FROM 
(SELECT s_id from score WHERE c_id in
(SELECT c_id FROM course LEFT JOIN teacher on teacher.t_id=course.t_id WHERE t_name="李四"))