oracle中的sql语言查询有关问题_Oracle管理

oracle中的sql语言查询问题
现有三张表：
学生表：s41071030(sno,sname,ssex,sage,sdept)
课程表：c41071030(cno,cname,cpno,ccredit)
选课表：sc41071030(sno,cno,grade)
现做查询：查询sdept为CS的学生中选课数最多的学生的信息。

求各位大侠指点！！求最优解！！

------解决方案--------------------
SELECT B.SNO,B.CNT FROM
(SELECT MAX(CNT) MT
FROM
(SELECT SNO,COUNT(CNO) CNT
FROM SC41071030
GROUP BY SNO
))A,
(SELECT SNO,COUNT(CNO) CNT
FROM SC41071030
GROUP BY SNO
)B
WHERE A.MT=B.CNT
------解决方案--------------------
select *
from (select t1.*, row_number() over(order by total asc) as cn
from (select sno, count(1) over(partition by sno) as total
from sc41071030 group by sno) t,
s41071030 t1
where t.sno = t1.sno
and t1.sdept = 'CS')
where cn = 1
------解决方案--------------------
看起来比较冗余，但是这个应该可以才对..

SQL code

SELECT * FROM s41071030 t WHERE EXISTS (SELECT 1 FROM (SELECT sno FROM (SELECT sno,Count(1)num FROM sc41071030 GROUP BY sno) WHERE num=(SELECT Max(num) FROM (SELECT sno,Count(1)num FROM sc41071030 GROUP BY sno))) WHERE sno=t.sno);
------解决方案--------------------
SQL codecreate table s41071030(    sno int ,    sname varchar2(10),    ssex char(1),    sage int,    sdept varchar2(10));/create table c41071030(    cno int,    cname varchar2(10),    cpno int,    ccredit varchar2(10));/create table sc41071030(    sno int,    cno int,    grade int );/insert into sc41071030select 1001,1,40 from dual union allselect 1001,2,45 from dual union allselect 1001,3,50 from dual union allselect 1002,1,44 from dual union allselect 1002,2,40 from dual union all--select 1002,3,50 from dual union allselect 1003,1,60 from dual;/insert into s41071030(sno,sdept)select 1001,'CS' from dual union allselect 1002,'CS' from dual union allselect 1003,'CS' from dual union allselect 1004,'dd' from dual;/with cte as(      select a.sno ,count(1) as v_count from sc41071030 a      inner join s41071030 b on a.sno=b.sno      where b.sdept='CS'      group by a.sno      order by count(1) desc )select a.* from cte a inner join (     select max(v_count) as v_count from cte)b  on a.v_count=b.v_count      /*drop table s41071030;drop table c41071030;drop table sc41071030;*/