-- 1)查询每科最好的前两名学生信息
with t as
(select row_number() over(partition by Cid order by Score desc) rn,sid,Cid
 from SC
)
select c.Cname,a.rn,b.Sname
from t a
inner join Student b on a.Sid=b.Sid
inner join Course c on a.Cid=c.Cid
where a.rn<=2

-- 2）查询两门以上不及格的同学学号及平均成绩
select a.sid,avg(Score) 'avgScore'
from Sc a
inner join
(select Sid from Sc where Score<60
 group by Sid having count(*)>2) b
on a.Sid=b.Sid