SQLP 시험문제 6일차

1. 

 select deptno, sum(sal) 

 from emp 

group by rollup (deptno) 

order by deptno 

;

2. 

select /*+ gather_plan_statistics */ empno, ename, sal, sum(sal) over (order by empno ) 

 from emp  

order by empno 

;

or 

select /*+ gather_plan_statistics */ empno, ename, sal, sum(sal) over (order by empno rows between unbounded preceding and current row ) 

 from emp  

order by empno 

;

(기본적으로 " rows between unbounded preceding and current row " 이 부분은 생략 가능해서) 

3. 

select deptno, empno, sum(sal) salsum, round(avg(sal))salavg

 from emp a 

group b grouping set (deptno,(deptno,empno))

 order by 1,2 

; 

4. 

select deptno, empno, ename, sal

       ,lead(sal,1) over (order by deptno, empno) , lag(sal,1) over (order by deptno, empno)

       ,lead(rownum,1) over (order by deptno, empno) no1, lag(rownum,1) over (order by deptno, empno) no2   

 from emp 

;