4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용

 

■지난주 배운것

1. 1. SQL이란 무엇인가
   2. SQL을 왜 배워야 하는거ㅏ?
   3. 기본 select 문 6가지 절

5  select

  1 from

  2 where

  3 group by

  4 having

  6 order by

 

4. 4. 함수
      • 단일행  함수 : 문자, 숫자, 날짜 ,변환, 일반
      • 복수행 함수 :  max, min, avg, sum, count

 

문제 154. 부서번호, 부서번호별 인원수를 출력하시오 !

(세로)

 

SELECT deptno, COUNT(*)

  FROM EMP

GROUP BY deptno;               

 

(가로)

문제155. 부서번호 , 부서번호별 토탈 월급을 가로로출력하시오

 

-sum 함수와 decode 함수를 사용한 방법:

 

 

select sum ( decode, 10, sal , 0 ) ) as "10",

sum ( decode, 20, sal , 0 ) ) as "20",

sum ( decode, 30, sal , 0 ) ) as "30"

 from emp;

 

• • pivot 문을 이용하는 방법

 

select *

from ( select deptno, sal from emp)

pivot ( sum (sal) for deptno in (10,20,30) );

 

문제156.직업, 직업별 토탈 월급을 출력하시오

세로

 

SELECT job, SUM(sal)

FROM EMP

GROUP BY job;

 

가로

SELECT *

FROM ( SELECT job,sal FROM emp)

    pivot ( SUM(sal) FOR job IN ('SALESMAN', 'CLERK','PRESIDENT','MANAGER','ANALYST'));

싱글쿼테이션 없애기

SELECT *

FROM ( SELECT job,sal FROM emp)

    pivot ( SUM(sal) FOR job IN ('SALESMAN' AS "SALESMAN",

                                 'CLERK' AS "CLERK",

                                 'PRESIDENT' AS "PRESIDENT",

                                 'MANAGER' AS "MANAGER",

                                 'ANALYST'  AS "ANALYST"));

 

 

문제157. 통신사 , 통신사별 인원수를 가로로 출력하시오 (PIVOT으로 구현하시오)

 

SELECT *

  FROM (SELECT TELECOM FROM EMP2)

  PIVOT ( COUNT(*) FOR TELECOM IN (  'sk' AS "SK", 'lg' AS "LG",'kt' AS "KT"));

 

 

문제158. 입사한 년도 (4자리) , 입사한 년도별 토탈월급을 출력하시오

 

• • 세로

 

  SELECT TO_CHAR(hiredate, 'YYYY') , SUM(sal)

    FROM EMP

  GROUP BY TO_CHAR(hiredate, 'YYYY');

 

 - 가로

SELECT *

    FROM ( SELECT TO_CHAR(hiredate, 'YYYY') a, sal FROM emp)

    pivot( SUM(sal) FOR a IN ('1980' AS "1980",

                     '1981' AS "1981",

                             '1982' AS "1982",

                             '1983' AS "1983"));

설명: pivot문안에TO_CHAR(hiredate, 'YYYY')하면에러간난다.

-> 컬럼 별칭을 이용하도록 하자

 

문제159. 입사한 년도(4자리), 입사한 년도별 인원수를 가로로 출력하시오.

SELECT *

  FROM ( SELECT TO_CHAR(hiredate, 'YYYY') A FROM EMP)

  PIVOT ( COUNT(*) FOR A IN ('1980' AS "1980",

'1981' AS "1981",

                             '1982' AS "1982",

                             '1983' AS "1983"));

 

문제160. 부서번호, 부서번호별 토탈월급을 가로로출력하시오!

 

• • SUM 함수와 DECODE 함수 사용

 

  SELECT  SUM(DECODE(DEPTNO,10,SAL,0)) AS "10",

         SUM(DECODE(DEPTNO,20,SAL,0)) AS "20",

        SUM(DECODE(DEPTNO,30,SAL,0)) AS "30"

               FROM EMP ;

 

 

 

  - PIVOT 문 이용하기

 

 

      SELECT *

        FROM ( SELECT DEPTNO, SAL FROM EMP)

        PIVOT ( SUM(SAL) FOR DEPTNO IN (10,20,30));

 

 

문제160. 부서번호, 부서번호별 토탈월급을 가로로출력하시오! (job또한)

 

 

• • SUM 함수와 DECODE 함수 사용

 

SELECT JOB,

       SUM(DECODE(DEPTNO, 10, SAL, 0)) AS "10",

       SUM(DECODE(DEPTNO, 20, SAL, 0)) AS "20",

       SUM(DECODE(DEPTNO, 30, SAL, 0)) AS "30"

  FROM EMP

 GROUP BY JOB ;

 

 

 

  - PIVOT 문 이용하기

 

      SELECT *

        FROM ( SELECT DEPTNO, SAL,,JOB FROM EMP)

        PIVOT ( SUM(SAL) FOR DEPTNO IN (10,20,30));

 

 

문제161. 전공별, 통신사별 인원수를 출력하시오

                   (세로)   (가로)

 

-SUM 함수와 DECODE함수를 활용하는방법

SELECT MAJOR ,SUM(DECODE(TELECOM,'sk',1,null)) AS "sk",

        SUM(DECODE(TELECOM,'lg',1,null)) AS "lg",

        SUM(DECODE(TELECOM,'kt',1,null)) AS "kt"

        FROM EMP2

        GROUP BY major;

 

 

 

-PIVOT문을 활용하는 방법

   SELECT *

          FROM (SELECT  major, telecom FROM EMP2)

         

        pivot ( COUNT(*) for telecom IN ( 'sk', 'lg', 'kt'));

       

 

 

 

문제162. (점심시간 문제)

전공별, 나이별 인원수를 출력하시오

(세로)    (가로)

 

select *

  from (select major, agefrom emp2)

pivot (count(*) for age in (24, 25, 26, 27, 28, 29, 30, 31, 33, 35 ));

 

 

 

 

■ 데이터 분석함수

 

• • listagg 함수
  • rank 함수

문제163. 이름 , 월급, 월급에 대한 순위를 출력하시오

 

select ename, sal, rank() over ( order by sal desc) 순위

from emp;

 

3등을 뽑고 싶으면

문제164. 이름,나이, 순위를 출력하는데 순위가 생년월일 순으로 순위를 부여하시오

 

SELECT telecom, ename, age, 

dense_rank() over ( PARTITION BY telecom

order by birth asc) 순위

from EMP2 ;

 

 

문제166. 부서번호, 이름, 월급 ,순위를 출력하는데 부서번호별로 각각 월급이 높은 순서대로 순위를 부여하시오

select deptno , ename, sal , dense_rank() over ( partition by deptno

 order by sal desc)  순위

from emp;

 

 

문제167. FORD는 월급을 3000을 받으면 emp 테이블에서 월급의 순위가

어떻게 되겠는가?

 

select rank(3000)  within GROUP (order by sal desc) 순위 from emp;

 

 

문제168. 1991년 8월 15일 은 우리반에서 나이 순위가 어떻게 되는가

 

SELECT RANK(TO_DATE('1991/08/15','RRRR/MM/DD')) within GROUP (ORDER BY birth asc) 순위

FROM EMP2;

 

ALTER SESSION SET nls_date_format='yyyy-mm-dd';

select rank('1991/8/15') within group ( order by birth acs) 순위 from emp2;

 

 

문제169. 아래의 데이터를 입력하고 이름과 월급을 출력하는데

월급이 높은 것 부터 출력하시오!

insert into emp ( empno , ename, sal , deptno)

values (1924,'jack', null, 30);

commit;

 

 

 

select ename, sal FROM EMP ORDER by sal DESC;

 

null 이 맨 위로 올라감

 

select ename, sal

FROM EMP

ORDER by sal DESC nulls last;

 

null 이 마지막으로 빠짐

 

문제 170. 이름, 입사일, 순위를 출력하는데 가장 최근에 입사한 사원부터 순위를 부여하시오 (가장 최근에 입사한 사원이 1등이 되게 )

 

 

SELECT ename, hiredate, RANK() OVER (ORDER BY hiredate DESC nulls last)

 FROM EMP ;