select ename, replace(nvl(comm,'-1'),-1,'no comm')
from emp
orderby comm desc nulls last;
-> nvl(__(A)__, __(B)__)
-> A 컬럼이 문자일 경우 B에 숫자 기입은 안된다. (반대도 마찬가지)
[TIL 5] 240516
32. 암시적 형변환
예제 32. 암시적 형 변환 이해하기
[변환함수] 데이터 유형 변환 1) 암시적 데이터 유형변화 2)명시적 데이터 유형변화
Q. 다음의 SQL이 에러가 안나고 실행이 되겠는가?
select ename,sal
from emp
where sal ='3000'
-> 숫자와 문자는 서로 비교할 수 없다. 다른 데이터 베이스 소프트 웨어는 에러가 발생한다. (단, 오라클은 에러가 안남) -> 위의 경우 숫자와 문자를 비교하면 숫자가 우선순위가 더 높아서 문자를 암시적 형 변환한다. -> 결론적으로는 에러가 발생 되는게 바람직하므로, 문법에 맞춰서 정석대로 작성하는게 바람직하다.
** 오라클 실행 설명 보기 **
explain plan forselect ename,sal
from emp
where sal ='3000';
select*fromtable(dbms_xplan.display);
테이블이 full scan 이 실행 계획으로 출력되고 있으므로, emp 테이블을 처음부터 끝까지 scan 했다는 뜻임 -> SQL 성능이 느려짐 (목차(INDEX) 를 만들어 SQL성능을 좋게 해야함)
* 월급에 INDEX를 만드는 SQL
create index emp_sal
on emp(sal);
문제1. 아래의 SQL의 실행계획을 확인해서 인덱스 스캔을 했는지 확인하시오.
explain plan forselect ename,sal
from emp
where sal =3000;
select*fromtable(dbms_xplan.display);
= FULL SCAN 보다 INDEX SCAN 을 하는 것이 좋은 SQL
문제2. 아래 SQL의 실행 계획을 확인하시오. (FULL TABLE SCAN 을 하는지 INDEX RANGE SCAN을 하는지)
select ename,sal
from emp
where sal ='3000';
explain plan forselect ename,sal
from emp
where sal ='3000';
select*fromtable(dbms_xplan.display);
= 인덱스 스캔을 하면서 빠르게 데이터 검색이 되고 있다.
문제3. 아래의 SQL은 실행이 되는지 확인하시오.
select ename,sal
from emp
where sal like'30%';
= 잘 출력됨
문제4. 위의 SQL의 실행 계획을 확인하시오.
explain plan forselect ename,sal
from emp
where sal like'30%';
select*fromtable(dbms_xplan.display);
= 인덱스를 활용하지 못하고 FULL TABLE SCAN 을 함 -> %를 숫자로 변경 못하고 SAL을 문자형으로 변환함. ->TO_CHAR(SAL) LIKE '30%'이 되면서 성능이 느려짐 -> 암시적 형변환 - SAL은 숫자인데 TO_CHAR(SAL)을 써서 문자형으로 형 변환을 함
** 좋은 성능을 위한 SQL
select ename,sal
from emp
where sal between3000and3099;
※ 다시 정리하면 튜닝전 SQL과 튜닝후 SQL은 다음과 같다. 1)튜닝전
select ename, sal
fromwhere sal like'30%';
2) 튜닝후
select ename, sal
fromwhere sal between3000and3099;
문제5. 위 튜닝 후 SQL 실행 계획을 확인하시오.
explain plan forselect ename,sal
from emp
where sal between3000and3099;
select*fromtable(dbms_xplan.display);
★ ★ WHERE 절 작성시 반드시 숫자는 숫자와 비교되고 날짜는 날짜와 비교되게끔 작성해야함. → 암시적 형 변환이 생기지 않도록!!
문제 6. 입사일에 인데스를 생성하시오.
create index emp_hiredate
on emp(hiredate);
문제 7. 아래의 SQL을 튜닝하시오. A) 튜닝전
select ename, hiredate
from emp
where hiredate like'80%';
Q) 튜닝후
select ename, hiredate
from emp
where hiredate between to_date('1980/01/01', 'RRRR/MM/DD')
and to_date('1980/12/31','RRRR/MM/DD');
문제8. 우리반 테이블에서 1996년에 태어난 학생들의 이름과 생일을 출력하시오.
select ename, birth
from emp19
where birth between to_date('1996/01/01', 'RRRR/MM/DD')
and to_date('1996/12/31','RRRR/MM/DD');
※ SQL 작성시 주의사항! (현업 TIP)
암시적 형변환이 일어나지 않게 작성하라!
33. NVL, NVL2
예제 33번. NULL값 대신 다른 데이터 출력하기 (NVL, NVL2) - NVL함수 (NULL VALUE의 약자)는 NULL 값 대신에 다른 값을 출력하게 해주는 함수
1, 이름과 커미션을 출력하시오
select ename,comm
from emp;
2. 커미션이 null 인 사원들은 0으로 출력하시오
select ename,nvl(comm, 0)
from emp;
3. 이름과 직업을 출력하는데 직업이 null인 사원들은 no job이라는 글씨로 출력되게 하시오.
select ename, substr(ename,1,1), casewhen substr(ename,1,1) in ('김', '이', '최') then'A'when substr(ename,1,1) in ('박', '유', '윤', '신')then'B'when substr(ename,1,1) in ('문', '서', '성', '경','심')then'C'when substr(ename,1,1) in ('조', '한', '황', '안')then'ㅇ'else'E'ENDAS 등급
from emp19;
문제 7. 이름, 직업, 보너스를 출력하는데 보너스가 직업이 PRESIDENT 면 null 을 출력하고 나머지 사원들은 자기의 월급으로 출력되게하시오 !
select ename, job, casewhen job ='PRESIDENT'thennullelse sal endas 보너스
from emp;
문제 8. 위의 결과를 다시 출력하는데 보너스가 높은 사원부터 출력하시오.
select ename, job, casewhen job ='PRESIDENT'thennullelse sal endas 보너스
from emp
orderby 보너스 desc;
문제 9. DECODE사용 - 이름, 월급, 직업과 보너스 출력하는데 보너스가 직업이 PRESIDENT 는 NULL값을 출력하고 나머지는 자신의 월급이 출력되게하시오.
select ename, sal, job, decode(job, 'PRESIDENT', null, sal) as 보너스
from emp;
문제 10. 위의 결과를 보너스가 높은 사원부터 출력하시오. (모 금융사에서 발생한 DECODE 관련 SQL 사고 사례)
select ename, sal, job, decode(job, 'PRESIDENT', NULL, SAL) as 보너스
from emp
orderby 보너스 desc ;
틀린 sql - 보너스 950 이 제일 위에 있음 why? ※ decode는 암시적 형변환이 있음
decode
(job,
'PRESIDENT',
NULL,
SAL)
첫번째
두번째
세번째
네번째
1) decode의 세번째 인자값에 따라서 네번째 인자값의 데이터 유형이 결정됨 2) 세번째 인자값으로 null 값이 입력이 되면 null 이 문자형이어서 네번째 값의 데이터 유형이 문자형 (암시적 형변환) = 그래서 문자형이어서 950이 맨위로 올라갔습니다.
문제 11. 위의 결과를 해결하시오.(DECODE CASE 사용)
# case 사용
select ename, sal, job, casewhen job ='PRESIDENT'thennullelse sal endas 보너스
from emp
orderby 보너스 desc nulls last ;
# decode 사용
select ename, sal, job, decode(job, 'PRESIDENT', to_number(NULL), sal) as 보너스
from emp
orderby 보너스 desc nulls last;
문제12. (빅데이터 활용 문제) 살인이 가장 많이 일어나는 장소 1위부터 - 3위까지 출력하시오.
select*from crime_loc
where c_type ='살인'orderby c_cnt desc nulls lastfetchfirst3rowsonly;
문제 13. (빅데이터 활용 문제) 범죄유형을 출력하는데 중복을 제거하여 출력하시오.
selectdistinct c_type
from crime_loc;
문제 14. (빅데이터 활용 문제) 절도가 많이 일어나는 장소를 1위부터 5위까지 출력하시오.
select*from crime_loc
where c_type ='절도'orderby c_cnt desc nulls lastfetchfirst5rowsonly;
문제 15. (빅데이터 활용 문제) 살인이 많이 일어나는 시간대가 언제인지 출력하기위한 테이블을 생성하시오.
