统计出每个教师每门课的及格人数和及格率

create table rock (教师ID number,学生ID number,学科名称 varchar2(40),成绩 number);
insert into rock values(1,1,'数学',80);
insert into rock values(1,2,'数学',50);
insert into rock values(2,3,'英语',61);
insert into rock values(2,4,'英语',59);
insert into rock values(3,5,'语文',62);
insert into rock values(3,6,'语文',58);
insert into rock values(1,7,'数学',81);
commit;

select * from rock t;

select t.教师ID,t.学科名称,
 sum(case when t.学科名称='语文' and t.成绩>=60 then 1 else 0 end ) chinese,
 sum(case when t.学科名称='数学' and t.成绩>=60 then 1 else 0 end ) maths,
 sum(case when t.学科名称='英语' and t.成绩>=60 then 1 else 0 end ) english,
 sum(decode(教师ID,'1',1)) chinse_num,
 sum(decode(教师ID,'2',1)) maths_num,
 sum(decode(教师ID,'3',1)) english_num
from rock t group by t.教师ID,t.学科名称;

select t.教师ID,t.学科名称,
       sum(case when t.成绩>=60 then 1 else 0 end ) 及格人数,
       round(sum(case when t.成绩>=60 then 1 else 0 end )/count(t.成绩),2)*100||'%' 及格率
from rock t group by t.教师ID,t.学科名称;

select 教师ID "教师ID",
       学科名称 "学科名称",
       sum(decode(sign(成绩-60), -1, 0, 1)) "及格人数",
       round((sum(decode(sign(成绩-60), -1, 0, 1)) / count(1)) * 100) || '%' "及格率"
from rock
group by 教师ID, 学科名称;

     
select 教师ID,学科名称,sum(及格人数) count,round(sum(及格人数)/sum(人数),2)*100||'%' 及格率
from
(select 教师ID,学科名称,case when 成绩>=60 then 1 else 0 end 及格人数,1 人数  from rock)
group by 教师ID,学科名称

select
        教师ID,
        学科名称,
        count(成绩) as 及格人数, 
        count(成绩)/count(*) as 及格率
      from
       ( select 教师ID,学生ID,学科名称,decode(sign(成绩-60),1,60,0,60,null)  as 成绩 from rock )
      group by 学科名称,教师ID
      order by 教师ID