select a.sid, b.sname,

sum(case when  c.cname="course01" then a.score end) as "course01",

sum(case when  c.cname="course02" then a.score end) as "course02",

sum(case when  c.cname="course03" then a.score end) as "course03",

sum(case when  c.cname="course04" then a.score end) as "course04",

sum(a.score) as "总分",

avg(a.score) as "平均分"

from scores a

left join stu b

on a.sid=b.sid

left join coures c

on a.cid=c.cid

group by b.sname

order by a.sid