结果如下:
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