[SQL] ORACLE SQL 공부 -3-

암시적 형 변환

SELECT ename, sal
    FROM emp
    WHERE sal = '3000';

ENAME             SAL
---------- ----------
FORD             3000
SCOTT            3000

• sal은 숫자형 컬럼인데 ‘3000’을 문자형으로 비교하고 있다.
• 오라클은 알아서 ‘숫자형’=’숫자형’으로 암시적 변환 해주기 때문에 에러가 발생하지 않고 검색 가능

CREATE TABLE EMP32
( ENAME  VARCHAR2(10),
 SAL      VARCHAR2(10) );

INSERT INTO EMP32 VALUES('SCOTT','3000');
INSERT INTO EMP32 VALUES('SMITH','1200');
COMMIT;

• EMP32 테이블의 SAL 데이터를 문자형으로 만들고 저장

SELECT ename, sal
    FROM emp32
    WHERE sal = 3000;

ENAME      SAL       
---------- ----------
SCOTT      3000

• WHERE 절에서 sal은 문자형 컬럼임에도 불구하고 3000이라는 숫자형으로 비교
• 오라클은 내부적으로 숫자형=숫자형으로 비교해서 데이터를 검색해주기 때문이다.
• 오라클은 내부적으로 숫자형으로 변환할 때에는 TO_NUMBER 함수를 이용한다.

NULL 값 대신 다른 데이터 출력하기

SELECT ename, comm, NVL(comm, 0)
    FROM emp;

ENAME            COMM NVL(COMM,0)
---------- ---------- -----------
KING                            0
BLAKE                           0
CLARK                           0
JONES                           0
MARTIN           1400        1400
ALLEN             300         300
TURNER              0           0
JAMES                           0
WARD              500         500
FORD                            0
SMITH                           0

ENAME            COMM NVL(COMM,0)
---------- ---------- -----------
SCOTT                           0
ADAMS                           0
MILLER                          0

• 커미션이 NULL이 아닌 사원들은 그대로 출력되고, NULL인 사원들은 NULL 대신 0이 출력

SELECT ename, sal, comm, sal+comm
    FROM emp
    WHERE job IN  ('SALESMAN', 'ANALYST');

ENAME             SAL       COMM   SAL+COMM
---------- ---------- ---------- ----------
MARTIN           1250       1400       2650
ALLEN            1600        300       1900
TURNER           1500          0       1500
WARD             1250        500       1750
FORD             3000                      
SCOTT            3000

• NULL값 처리 안 된 쿼리

SELECT ename, sal, comm, NVL(comm, 0), sal+NVL(comm,0)
    FROM emp
    WHERE job IN  ('SALESMAN', 'ANALYST');

ENAME             SAL       COMM NVL(COMM,0) SAL+NVL(COMM,0)
---------- ---------- ---------- ----------- ---------------
MARTIN           1250       1400        1400            2650
ALLEN            1600        300         300            1900
TURNER           1500          0           0            1500
WARD             1250        500         500            1750
FORD             3000                      0            3000
SCOTT            3000                      0            3000

• NULL값 처리된 쿼리

SELECT ename, sal, comm, NVL2(comm, sal+comm, sal)
    FROM emp
    WHERE job IN ('SALESMAN', 'ANALYST');

ENAME             SAL       COMM NVL2(COMM,SAL+COMM,SAL)
---------- ---------- ---------- -----------------------
MARTIN           1250       1400                    2650
ALLEN            1600        300                    1900
TURNER           1500          0                    1500
WARD             1250        500                    1750
FORD             3000                               3000
SCOTT            3000                               3000

• NVL2함수를 이용해서 NULL이 아닌 사람들은 sal+comm을 출력하게 하고, NULL인 사원들은 sal을 출력한다.

IF문을 SQL로 구현하기 (DECODE)

SELECt ename, deptno, DECODE(deptno, 10, 300, 20, 400, 0) as 보너스 
    FROM emp;

ENAME          DEPTNO        보너스
---------- ---------- ----------
KING               10        300
BLAKE              30          0
CLARK              10        300
JONES              20        400
MARTIN             30          0
ALLEN              30          0
TURNER             30          0
JAMES              30          0
WARD               30          0
FORD               20        400
SMITH              20        400

ENAME          DEPTNO        보너스
---------- ---------- ----------
SCOTT              20        400
ADAMS              20        400
MILLER             10        300

• 부서 번호가 10이면 300을, 부서 번호가 20이면 400을, 나머지 부서 번호들은 0을 출력하게 한다.
• 맨 끝에 0은 default 값으로 앞의 값에 만족하지 않은 데이터, 즉 10번과 20번이 아니면 출력되는 값이다.

SELECT empno, mod(empno, 2), DECODE(mod(empno,2), 0, '짝수', 1, '홀수') as 보너스 
    FROM emp;

EMPNO MOD(EMPNO,2) 보너
---------- ------------ --
      7839            1 홀수
      7698            0 짝수
      7782            0 짝수
      7566            0 짝수
      7654            0 짝수
      7499            1 홀수
      7844            0 짝수
      7900            0 짝수
      7521            1 홀수
      7902            0 짝수
      7369            1 홀수

     EMPNO MOD(EMPNO,2) 보너
---------- ------------ --
      7788            0 짝수
      7876            0 짝수
      7934            0 짝수

• 사원 번호와 사원번호가 짝수인지 홀수 인지를 출력하는 쿼리
• default 값은 생략 가능

SELECT ename, job, DECODE(job, 'SALESMAN', 5000, 2000) as 보너스
    FROM emp;

ENAME      JOB              보너스
---------- --------- ----------
KING       PRESIDENT       2000
BLAKE      MANAGER         2000
CLARK      MANAGER         2000
JONES      MANAGER         2000
MARTIN     SALESMAN        5000
ALLEN      SALESMAN        5000
TURNER     SALESMAN        5000
JAMES      CLERK           2000
WARD       SALESMAN        5000
FORD       ANALYST         2000
SMITH      CLERK           2000

ENAME      JOB              보너스
---------- --------- ----------
SCOTT      ANALYST         2000
ADAMS      CLERK           2000
MILLER     CLERK           2000

• job이 salesman이라면 5000을 출력
• 그렇지 않으면 2000을 출력

IF문을 SQL로 구현하기 (CASE)

SELECT ename, job, sal, CASE WHEN sal >= 3000 THEN 500
                            WHEN sal >= 2000 THEN 300
                            WHEN sal >= 1000 THEN 200
                            ELSE 0 END AS BONUS
    FROM emp
    WHERE job IN ('SALESMAN', 'ANALYST');

ENAME      JOB              SAL      BONUS
---------- --------- ---------- ----------
MARTIN     SALESMAN        1250        200
ALLEN      SALESMAN        1600        200
TURNER     SALESMAN        1500        200
WARD       SALESMAN        1250        200
FORD       ANALYST         3000        500
SCOTT      ANALYST         3000        500

• 3000이상이면 500, 2000이면 300, 1000이면 200, 그 외에는 0을 출력해주는 BONUS 칼럼

SELECT ename, job, comm, CASE WHEN comm is null THEN 500
    ELSE 0 END BONUS
    FROM emp
    WHERE job IN ('SALESMAN', 'ANALYST');

ENAME      JOB             COMM      BONUS
---------- --------- ---------- ----------
MARTIN     SALESMAN        1400          0
ALLEN      SALESMAN         300          0
TURNER     SALESMAN           0          0
WARD       SALESMAN         500          0
FORD       ANALYST                     500
SCOTT      ANALYST                     500

• comm이 null이면 500을 출력, 아니라면 0을 출력해주는 BONUS 칼럼

SELECT ename, job, CASE WHEN job in ('SALESMAN', 'ANALYST') THEN 500
                        WHEN job in ('CLERK', 'MANAGER') THEN 400
                        ELSE 0 END AS 보너스 
                        FROM emp;

ENAME      JOB              보너스
---------- --------- ----------
KING       PRESIDENT          0
BLAKE      MANAGER          400
CLARK      MANAGER          400
JONES      MANAGER          400
MARTIN     SALESMAN         500
ALLEN      SALESMAN         500
TURNER     SALESMAN         500
JAMES      CLERK            400
WARD       SALESMAN         500
FORD       ANALYST          500
SMITH      CLERK            400

ENAME      JOB              보너스
---------- --------- ----------
SCOTT      ANALYST          500
ADAMS      CLERK            400
MILLER     CLERK            400

최대값 출력하기(MAX)

SELECT MAX(sal)
    FROM emp
    WHERE job='SALESMAN';

MAX(SAL)
----------
      1600

• MAX 함수를 통해 직업이 SALESMAN인 사원들의 최대 월급값을 출력할 수 있다.

SELECT job, MAX(sal)
    FROM emp
    WHERE job='SALESMAN';

오류 보고 -
SQL 오류: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

• job 칼럼은 여러 개의 행을 출력하려 하고, MAX 함수는 단일행을 출력하기 때문에 에러가 발생
• 그렇기 때문에 GROUP BY 절이 필요

SELECT job, MAX(sal)
    FROM emp
    WHERE job = 'SALESMAN'
    GROUP BY job;

JOB         MAX(SAL)
--------- ----------
SALESMAN        1600

• GROUP BY 로 표현한 쿼리

SELECT deptno, MAX(sal)
    FROM emp
    GROUP BY deptno;

DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000

최소값 출력하기 (MIN)

SELECT MIN(sal)
    FROM emp
    WHERE job='SALESMAN';

MIN(SAL)
----------
      1250

SELECT job, MIN(sal) 최소값
    FROM emp
    GROUP BY job
    ORDER BY 최소값 DESC;

JOB              최소값
--------- ----------
PRESIDENT       5000
ANALYST         3000
MANAGER         2450
SALESMAN        1250
CLERK            800

• ORDER BY 절은 항상 맨 마지막에 작성하고, 실행 또한 맨 마지막에 실행된다.

SELECT job, MIN(sal)
    FROM emp
    WHERE job != 'SALESMAN'
    GROUP BY job
    ORDER BY MIN(sal) DESC;

JOB         MIN(SAL)
--------- ----------
PRESIDENT       5000
ANALYST         3000
MANAGER         2450
CLERK            800

평균값 출력하기

SELECT AVG(comm)
    FROM emp;

AVG(COMM)
----------
       550

• 그룹 함수는 NULL 값을 무시하기 때문에 아래와 결과값이 다르다.

SELECT ROUND(AVG(NVL(comm, 0)))
    FROM emp;

ROUND(AVG(NVL(COMM,0)))
-----------------------
                    157

토탈값 출력하기(SUM)

SELECT deptno, SUM(sal)
    FROM emp
    GROUP BY deptno;

DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

SELECT job, SUM(sal)
    FROM emp
    GROUP BY job
    ORDER BY sum(sal) DESC;

JOB         SUM(SAL)
--------- ----------
MANAGER         8275
ANALYST         6000
SALESMAN        5600
PRESIDENT       5000
CLERK           4150

---

• 그룹함수로 조건을 줄 때에는 WHERE 절 대신 HAVING 절을 이용해야 한다.

SELECT job, SUM(sal)
    FROM emp
    GROUP BY job
    HAVING SUM(sal) >= 4000
    ORDER BY SUM(sal) DESC;

JOB         SUM(SAL)
--------- ----------
MANAGER         8275
ANALYST         6000
SALESMAN        5600
PRESIDENT       5000
CLERK           4150