SQLP 시험 문제 7일차

시험문제 7일차 

4. (결과 테스트 해봐야 함) 

select deptno, LTRIM (sys_connect_by_path(ename,','),',') ename 

 from ( select deptno 

               , ename 

               , row_number() over (partition by deptno order by deptno) rnum 

               , count(*) over (partition by deptno ) cnt 

        from emp 

      ) 

 where level = cnt 

 start with rnum = 1

 connect by prior deptno = deptno 

 and prior rnum = rnum - 1

;

-- 답은 이것으로 예상 

select a.ename, b.ename 

 from emp a, 

       ( select mgr, LTRIM (sys_connect_by_path(ename,','),',') ename 

         from ( select  ename

                     , mgr   

                     , row_number() over (partition by mgr order by empno) rnum 

                 from emp 

               ) 

        start with rnum = 1

        connect by prior mgr = mgr 

        and prior rnum = rnum - 1

       ) b 

where a.empno = b.mgr 

;

5. 

select b.aaa, b.saumsal, sum(sumsal) over (order by aaa ) cumulative 

  from ( select a.aaa , sum(a.sal) sumsal 

           from ( select to_char(hiredate, 'yyyy') aaa , sal 

                    from emp

                 ) a

           group by aaa ) b

 ;

or 

 

select b.aaa, b.saumsal, sum(sumsal) over (order by aaa rows between unbounded preceding and current row ) cumulative 

  from ( select a.aaa , sum(a.sal) sumsal 

           from ( select to_char(hiredate, 'yyyy') aaa , sal 

                    from emp

                 ) a

           group by aaa ) b

 ;

--- 그러나 

select to_char(hiredate, 'yyyy') aaa, sal 

 from emp 

group by to_char(hiredate, 'yyyy') 

;

이게 만약 가능하다면 

select b.aaa, b.saumsal, sum(sumsal) over (order by aaa ) cumulative 

  from ( select  to_char(hiredate, 'yyyy') aaa , sum(a.sal) sumsal 

           from emp

         group by to_char(hiredate, 'yyyy') ) b

 ;

이렇게 쿼리 작성이 가능 

( 쿼리를 좀 더 쉽게 구성 할 수 있고 in-line 뷰 사용 횟수도 줄임)