[SQL]-(035) IF문을 SQL로 구현하기2

---

[Reference] 초보자를 위한 SQL 200제

---

[SQL 문법]

IF문을 SQL로 구현할 수 있다.

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')

[예시]

# 오라클 연동 및 접속
import pandas as pd
import cx_Oracle
dsn=cx_Oracle.makedsn('localhost',1521,'orcl')
db=cx_Oracle.connect('scott','tiger')
cursor=db.cursor()

# SQL 문법
cursor.execute("""
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')
""")

row=cursor.fetchall()
colname=cursor.description
col=[]

for i in colname:
    col.append(i[0])

# pandas를 사용한 데이터 프레임 형식으로 변환
emp=pd.DataFrame(row,columns=col)
print(emp)

[결과]

    ENAME       JOB     SAL  BONUS
0  MARTIN  SALESMAN  1250.0    200
1   ALLEN  SALESMAN  1600.0    200
2  TURNER  SALESMAN  1500.0    200
3    WARD  SALESMAN  1250.0    200
4    FORD   ANALYST  3000.0    500
5   SCOTT   ANALYST  3000.0    500

[복습] 2023-02-03

DECODE와의 차이점은 디코드는 등호(=) 비교만 가능하지만 CASE는 부등호도 가능하다.
직업이 SALESMAN, ANALYST일 때 각기 500, 400으로 입력해보자

SELECT ename, job, CASE WHEN job in ('SALESMAN') THEN 500
                        WHEN job in ('ANALYST', 'CLERK') THEN 400
                    ELSE 0 END as Bonus
  FROM emp

[결과]

     ENAME        JOB  BONUS
0     KING  PRESIDENT      0
1    BLAKE    MANAGER      0
2    CLARK    MANAGER      0
3    JONES    MANAGER      0
4   MARTIN   SALESMAN    500
5    ALLEN   SALESMAN    500
6   TURNER   SALESMAN    500
7    JAMES      CLERK    400
8     WARD   SALESMAN    500
9     FORD    ANALYST    400
10   SMITH      CLERK    400
11   ADAMS      CLERK    400
12  MILLER      CLERK    400
13    JACK    ANALYST    400