SQL语句书写：四表连接查询

Question

 学生表：student(sno,sname,sage,ssex)
 成绩表：sc(sno,cno,score)
 课程表：course(cno,cname,tno)
 教师表：teacher(tno,tname）

有点晕了，哪位大佬帮忙研究一下

查询出每个教师所教课程名称、对应课程的平均分、成绩最高的学生和分数，放在一张结果表展示。

Answer 1

select
	t.tname,a.*,s.sname 
from teacher t
left join course c on t.tno=c.tno 
left join 
(
select 
	cno
	,avg(score) as avg_score
	,max(score) as max_score
from sc 
group by cno 
) a on c.cno=a.cno 
left join sc on a.cno=sc.cno and a.max_score=sc.score
left join student s on sc.sno=s.sno