oracle：sql查询

最近遇到一些查询，自己还不是能保证一口气就写对，经过和好友一起研究，有一两个比较好的思路，尤其是最后两个查询，值得深思。为纪念这一丁丁进步和思维的一小步跨越，现记录如下：

--1.查询所有同学的学号、姓名、选课数、总成绩
select sc.sno,sname,count(sc.cno),sum(sc.grade) from student,sc
where student.sno=sc.sno
group by sc.sno,sname
order by sc.sno;
--2.查询姓“赵”的老师的个数
select count(sno) from student where sname like '赵%';
--3.查询选课门数大于2的学生的学号和姓名
select sc.sno,sname,count(sc.sno) from student,sc
where student.sno = sc.sno
group by sc.sno,sname
having count(sc.sno)>2
order by sc.sno;
--4.查询每个学生的选课最高分和最低分,列出学号和分数
select sno, max(grade),min(grade) from sc group by sno;
--5.查询选课门数最多的2个学生的学号和姓名(有问题)
select sno,num from (select sc.sno,count(sc.cno) num from sc 
group by sc.sno
order by num desc) t
where rownum<=2
--6.一句SQL查出所有课程成绩最高和最低的学生及其分数
select * from (select sc.cno,sc.sno,sc.grade from (select cno,max(grade) gr from sc group by cno) t,sc
where sc.cno = t.cno and t.gr = sc.grade
order by cno)
union all
select * from (select sc.cno,sc.sno,sc.grade from (select cno,min(grade) gr from sc group by cno) t,sc
where sc.cno = t.cno and t.gr = sc.grade
order by cno)
order by 1 asc,3 desc

实验数据：student表、course表、sc表依次如下图所示：