sql新手，请问如何将一个查询结果的某元素与另一个的某元素关联

Question

student是学生表，sc是学生(sid)在课程(cid)上的得分表
题目是查询平均成绩大于65的学生的sid，名字和平均分
我像这样写了。

select * from (select sid,avg(score) as avg_score from sc group by sid having avg_score>65) t

里面可以查询达标学生的姓名，sid

select * from (select sid,avg(score) as avg_score from sc group by sid having avg_score>65) t

达标学生的平均分
但是不知道怎么将两表的sid innner join 起来
像下面这样会语法错误

select * from (select sid,sname from student where sid in (select sid from sc group by sid having avg(score)>65)) t inner join sid on (select sid,avg(score) as avg_score from sc group by sid having avg_score>65) t t.sid

Answer 1

select s.sid,max(s.sname) as sname, avg(sc.score) as avg_score
from student s inner join sc sc on s.sid = sc.sid
group by s.sid having avg(sc.score)>65