유용한 oracle 쿼리 (partition by 사용법)

문제 1> 선수 과목이 있는 과목 리스트를 선수과목명을 포함하여 경로를 출력하시오.
      (조회 대상 데이터 : 과목번호, 과목명 , 선수과목 번호, 과목과정 경로)
      Hierachical Query를 활용 할 것.
답안 > select clss_no, clss_nm, prttn_clss_no, path
from (select level lvl ,clss_no, clss_nm, prttn_clss_no,
      sys_connect_by_path(clss_nm,'--') path
from tb_class
where prttn_clss_no is not null
connect by nocycle prior clss_no=prttn_clss_no)
where lvl !=1

문제 2> “강의” 테이블에서 교수별 과목별 결강건수를 구하시오
      (조회 대상 데이터 : 교수번호, 과목번호, 결강건수)      
       Hint : Scalar 쿼리를 활용해 보자. 다른 방법이 있다면 OK
       TB_LECTURE : LCTR_STTS='결강‘
     
답안 > select a.prfss_no, a.prfss_nm, c.clss_nm, round(b.cancel_class,2)*100 "결강율(%)"
select b.prfss_no, b.clss_no,
            (select count(*)
             from tb_lecture a
             where a.lctr_stts='결강'
             and a.prfss_no=b.prfss_no
             and a.clss_no=b.clss_no) cancel_class
     from tb_lecture b
     group by b.prfss_no, b.clss_no

문제 3> 학기별로 학생들 평균 점수가 가장 높은 과목을 추출 할 것.(중복 가능)
       (조회 대상 데이터 : 학기 번호, 과목 이름, 가장 높은 평균 학점(학기당 과목점수 합계 /학기당 과목 수강학생수), 과목 구분)
       Hint : rank 함수를 활용해 보자.
       
답안 > select a.trm_no, a.clss_no, b.clss_nm, a.avg_grd ,b.clss_type
from (select trm_no, clss_no, sum(clss_grd)/count(*) avg_grd,
            rank() over (partition by trm_no order by sum(clss_grd)/count(*) desc) clss_rank
     from tb_attend
     group by trm_no, clss_no) a,
     tb_class b
where a.clss_rank=1
 and a.clss_no=b.clss_no