先有班级，学生表个一张，如何通过sql实现：每个班级下学生总数

CREATE TABLE student_tab(
SID NUMBER(6), SNAME VARCHAR2(20), CID NUMBER(6),
CONSTRAINT pk_student_tab PRIMARY KEY(SID)
);
INSERT INTO student_tab VALUES(1,'A11',1);
INSERT INTO student_tab VALUES(2,'A21',1);
INSERT INTO student_tab VALUES(3,'A31',1);
INSERT INTO student_tab VALUES(4,'A41',1);
INSERT INTO student_tab VALUES(5,'A51',1);

INSERT INTO student_tab VALUES(6,'B21',2);
INSERT INTO student_tab VALUES(7,'B31',2);
INSERT INTO student_tab VALUES(8,'B41',2);

CREATE TABLE class_tab(
CID NUMBER(6), CNAME VARCHAR2(20),
CONSTRAINT pk_class_tab PRIMARY KEY(CID)
);
INSERT INTO class_tab VALUES(1,'一年级1班');
INSERT INTO class_tab VALUES(2,'一年级2班');
INSERT INTO class_tab VALUES(3,'一年级3班');

SELECT * FROM class_tab;
SELECT * FROM student_tab;

-- 需要考虑班级中没有学生以及学生的情况
-- 写法一
SELECT c.cname,DECODE(s.cid,null,0,COUNT(*)) studentCount
FROM student_tab s, class_tab c
WHERE s.cid(+)=c.cid
GROUP BY s.cid,c.cname

-- 写法二
SELECT c.cname,
CASE WHEN s.cid IS NULL THEN 0
ELSE COUNT(*)
END AS studentCount
FROM student_tab s
RIGHT OUTER JOIN class_tab c ON (s.cid=c.cid)
GROUP BY s.cid,c.cname

-- 写法三
SELECT c.cname,DECODE(s.cid,null,0,COUNT(*)) studentCount
FROM student_tab s
RIGHT OUTER JOIN class_tab c ON (s.cid=c.cid)
GROUP BY s.cid,c.cname

select count(学号字段) as num , 班级字段名 from 学生表 group by 班级字段名