17p ~
실습 group _ ad 1
1)
SELECT deptno , sum(sal)
FROM emp
group by deptno
union all
SELECT null, sum(sal)
FROM emp
2)
SELECT deptno , sum(sal)
FROM emp
group by rollup (deptno)
25P ~
실습 GROUP _AD2
1) NVL2
SELECT NVL2(job , job, '총계') job , deptno, SUM(sal)
FROM emp
GROUP BY ROLLUP (job, deptno );
2) NVL
SELECT NVL(job , '총계') job , deptno, SUM(sal)
FROM emp
GROUP BY ROLLUP (job, deptno );
3) DECODE 함수
SELECT DECODE(job , job , job ,null, '총계') job , deptno, SUM(sal)
FROM emp
GROUP BY ROLLUP (job, deptno );
실습 GROUP_AD2-1
1)
SELECT DECODE ( job , null, '총' , job) ,
CASE
WHEN grouping(job ) = 1 THEN '계'
WHEN grouping(deptno) = 1 THEN '소계'
ELSE TO_CHAR(deptno)
END deptno ,
SUM(SAL)
FROM emp
GROUP BY rollup (job, deptno)
2)
SELECT DECODE ( job , null, '총' , job) ,
CASE
WHEN grouping(job) = 1 AND grouping(deptno) = 1 THEN '계'
WHEN grouping(deptno) = 1 THEN '소계' 자리를 바꾸면 안됨.
ELSE TO_CHAR(deptno)
END deptno ,
SUM(SAL)
FROM emp
GROUP BY rollup (job, deptno)
3)
SELECT DECODE ( job , null, '총' , job) job ,
DECODE ( grouping(job) + grouping(deptno) , 2 , '계' , 1 , '소계' , TO_CHAR(deptno) ) deptno
,
SUM(SAL)
FROM emp
GROUP BY rollup (job, deptno)
27p 실습 GROUP _AD3
SELECT deptno , job , SUM(SAL)
FROM emp
GROUP BY ROLLUP ( deptno, job )
실습 GROUP _AD4
SELECT dname, job, SUM(sal)
FROM emp , dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP (dname , job )
실습 GROUP _AD5
SELECT DECODE(dname , NULL, '총합' , dname) dname , job , SUM(sal)
FROM emp , dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP ( dname, job )
서브쿼리 ADVANCED ( correlated subquery update - 실습 sub_a1 ) **외워야함
1.원래있던 애로 테이블 생성하기
CREATE TABLE dept_test AS
SELECT *
FROM DEPT
2.컬럼 생성하기
ALTER TABLE dept_test ADD ( empcnt NUMBER);
3. subquery 를 이용하여 dept_test 테이블의 empcnt 컬럼에 해당 부서원 수를 update 하는 쿼리를 작성하시오
UPDATE dept_test SET empcnt =
(SELECT count(*)
FROM emp
WHERE deptno = dept_test.deptno )
서브쿼리 ADVANCED ( correlated subquery update - 실습 sub_a2 )
1)
delete dept_test
WHERE deptno NOT IN ( SELECT deptno
FROM emp
WHERE dept_test.deptno = emp.deptno )
2)
DELETE dept_test
WHERE deptno NOT IN ( SELECT deptno
FROM emp )
3)
DELETE dept_test
WHERE NOT EXISTS ( SELECT deptno
FROM emp
WHERE dept_test.deptno = emp.deptno )
서브쿼리 ADVANCED ( correlated subquery update - 실습 sub_a3 )
UPDATE emp_test SET sal = sal + 200
WHERE sal < (SELECT AVG(sal)
FROM emp
WHERE emp.deptno = emp_test.deptno )
계층 쿼리 ( 실습 h_1)
SELECT level, deptcd, Lpad(' ' , (level - 1) *3 )||deptnm deptnm , p_deptcd
FROM dept_n
START WITH deptcd = 'dept0'
CONNECT BY PRIOR deptcd = p_deptcd;
계층 쿼리 ( 실습 h_2)
SELECT level, deptcd, LPAD( ' ' , (level-1) * 3 )||deptnm deptnm , p_deptcd
FROM dept_n
START WITH deptcd = 'dept0_02'
CONNECT BY PRIOR deptcd = p_deptcd
계층 쿼리 ( 실습 h_3)
SELECT level lv , deptcd , LPAD(' ' , (level-1)*3 )||deptnm deptnm, p_deptcd
FROM dept_n
START WITH p_deptcd = 'dept0_00'
CONNECT BY PRIOR p_deptcd = deptcd
계층 쿼리 ( 실습 h_4) h_sum
SELECT LPAD(' ' , (level-1)*3 ) || s_id s_id, value
FROM h_sum
START WITH s_id = 0
CONNECT BY PRIOR s_id = ps_id;
계층쿼리 (게시글 계층형쿼리 샘플자료.sql실습 h6 )
SELECT SEQ, LPAD(' ' , (level-1) * 3 )||TITLE
FROM board_test
START WITH seq IN ( 1, 2, 4 )
CONNECT BY PRIOR seq = parent_seq
실습 h7
SELECT SEQ, LPAD(' ' , (level-1) * 3 )||TITLE
FROM board_test
START WITH seq IN ( 1, 2, 4 )
CONNECT BY PRIOR seq = parent_seq
ORDER BY SEQ DESC
실습 h8
SELECT seq , LPAD ( ' ' , (level-1) * 3 )||title
FROM board_test
START WITH SEQ IN ( 1 , 2, 4 )
CONNECT BY PRIOR SEQ = PARENT_SEQ
ORDER SIBLINGS BY seq desc
실습 h9
분석함수/ window 함수 99p (도전해보기 실습 ana 0 )
SELECT ename, sal, deptno,
RANK () OVER (PARTITION BY deptno ORDER BY sal desc) sal_rank
FROM emp
분석함수/ window 함수 (실습 ana1)
SELECT empno, ename, sal , deptno ,
rank () over ( order by sal desc , empno ) sal_rank
,dense_rank () over ( order by sal desc , empno ) sal_dense_rank
,row_number () over ( order by sal desc , empno ) sal_rank_number
FROM emp ;
분석함수/ window 함수 (실습 no_ana2)
SELECT empno, ename, deptno , COUNT (*) over ( partition by deptno ) cnt
FROM emp ;
111p
분석함수/ window 함수 (실습 ana2)
SELECT empno, ename, sal, deptno , ROUND(AVG (sal) OVER ( PARTITION BY deptno ) , 2) avg_sal
FROM emp
분석함수/ window 함수 (실습 ana3)
SELECT empno, ename, sal, deptno , MAX(sal) OVER ( PARTITION BY deptno ) max_sal
FROM emp
분석함수/ window 함수 (실습 ana4)
SELECT empno, ename, sal, deptno , MIN(sal) OVER ( PARTITION BY deptno ) min_sal
FROM emp
LAG LEAD
분석함수/ window 함수 (실습 ana5)
SELECT empno, ename, hiredate, sal,
LAG(sal) over ( ORDER BY sal desc , hiredate asc ) lag_sal
FROM emp
분석함수/ window 함수 (그룹 내 행 순서 실습 ana5_1)
분석함수/ window 함수 (그룹 내 행 순서 실습 ana6)
SELECT empno, ename, hiredate, job , sal ,
LAG(sal) OVER ( PARTITION BY job ORDER BY sal desc , hiredate ASC ) lag_sal
FROM emp
분석함수/ window 함수 (그룹 내 행 순서 - 생각해보기, 실습 no_ana3)
분석함수/ window 함수 (그룹 내 행 순서 실습 ana7)
SELECT empno, ename, deptno , sal ,
SUM(sal) OVER ( PARTITION BY deptno ORDER BY SAL rows unbounded preceding ) c_sum
FROM emp
댓글
댓글 쓰기