sql经典40题（下）

26. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.sid, sname, avg_score
from (
  select sid, count(if(score<60, score, null)) count, avg(score) avg_score
  from SC 
  group by sid 
  having count(if(score<60, score, null)) >= 2
) t 
join Student s 
on s.sid = t.sid;

27. 检索"01"课程分数小于60，按分数降序排列的学生信息

---方法一：
select sid, sname, sage 
from Student 
where sid in (
  select sid 
  from SC 
  where cid = '01'and score < 60
  order by score desc
);
---方法二：
select s.sname, s.sid, s.sage, sc.score 
from Student s right join (
  select sid, score 
  from SC 
  where cid = '01' and score < 60
) sc 
on sc.sid = s.sid;