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

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

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

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

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 

select c.cname,sum(sc.score)/count(s.no),max(s.core),s.sname from tb_teacher t left join course c on t.tno = c.tno
left join tb_sc sc left join tb_student s on sc.sno = s.sno
oracle的，思路是这样的。手打出来的，具体没数据我也不清楚对不对

select c.cname,sum(sc.score)/count(s.no),max(s.core),s.sname from tb_teacher t left join course c on t.tno = c.tno
left join tb_sc sc left join tb_student s on sc.sno = s.sno 可以试一下

select t.tname,c.cname, a.maxnum,a.avgnum,s.sname from (SELECT max(sc.score) maxnum,avg(sc.score) avgnum,sc.cno,sc.sno from sc group by sc.cno) a left join sc on sc.score=a.maxnum LEFT join course c on sc.cno=c.cno LEFT JOIN student s on sc.sno=s.sno LEFT JOIN teacher t on t.tno=c.tno
mysql的