sql面试题详解

 结果如下：

select a.sno,a.sname,avg(b.score) from student a left JOIN sc b on a.sno = b.sno GROUP BY a.sno having avg(score)>60

<pre name="code" class="sql">select a.sno,a.sname,count(b.cno),sum(b.score) from student a left join sc b on a.sno = b.sno group by sno

select a.sno,a.sname  from student a where a.sno not in (select a.sno from sc a inner join course b on a.cno = b.cno left join teaher c on b.tno = c.tno  where tname = '叶平')  

select a.sno,a.sname  from student a left join sc b on a.sno = b.sno group by a.sno having max(b.score)<60或者

<span style="font-family: Arial, Helvetica, sans-serif;">select a.sno,a.sname  from student a left join sc b on a.sno = b.sno group by a.sno having count(b.score) = sum( case when b.score<60 then 1 else 0 end)</span>

select cno,max(score),min(score) from sc group by cno