sql 기본 / 변환함수, 일반함수
[복습]
1. select 문 : select - 검색할 컬럼명
from - 테이블명
where - 검색조건
order by 정렬할 컬럼명
2. 함수
- 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
- 복수행 함수 : max, min, avg, sum, count
문제99. 부서번호가 20번이 아닌 사원들의 이름과 월급과 부서번호를 출력하는데 월급이 낮은 사원부터 출력하시오
select ename, sal, deptno
from emp
where deptno != 20
order by sal asc;
030 문자형으로 데이터 유형 변환하기(TO_CHAR)
to_char(입력값, 포맷)
> 숫자를 문자로 변환하기
--이름이 SCOTT인 사원의 입사한 요일과 월급을 출력하는데 월급을 출력할 때 천단위를 표시해서 출력하세요
select ename, to_char(hiredate, 'day'), to_char(sal, '999,999')
from emp
where ename = 'SCOTT';
--9의 의미는 자릿수인데 9자리에 0~9까지문제100. 이름과 보너스를 출력하는데 보너스가 월급*1000000000 으로하고 천단위와 백만단위 등을 표시되게 하시오
select ename, to_char(sal*1000000000, '999,999,999,999,999,999,999') as 보너스
from emp;문제101. 문제100번의 결과를 다시 출력하는데 하원이가 물어본데로 뒤의 0 3개를 안나오게 하시오
select substr(to_char(sal*1000000000, '999,999,999,999,999,999'),1,16)
from emp;
--trunc를 사용할 경우 trunc는 숫자함수이기 때문에 문자함수 안에 둘러줘야함
031 날짜형으로 데이터 유형 변환하기(TO_DATE)
--81년 11월 17일에 입사한 사원의 이름과 입사일을 출력하세요
select ename, hiredate
from emp
where hiredate = '81/11/17';
/* 이렇게 입력해도 가능은 하지만 글로벌한 코드로 사용할 수 없음(ex.미국, 영국은 날짜표기형식 다름)
영국식으로 조회할 경우,
where hiredate = '11/NOV/81' 로 입력해야함 */
--어떠한 환경에 있든 상관없이 데이터 오류없이 검색하기 위해
select ename, hiredate
from emp
where hiredate = to_date('81/11/17', 'RR/MM/DD');문제102. 81년도에 입사한 사원들의 이름과 입사일을 출력하는데 내가 어느 나라에 있든 상관없이 조회되게 하시오.
select ename, hiredate
from emp
where hiredate between to_date('1981/01/01','RRRR/MM/DD') and to_date('1981/12/31','RRRR/MM/DD')+1;* 연도포맷에서 RR과 YY의 차이점
81년도를 입력했을 때 RR은 1981을 출력하고, YY는 2081을 출력함.
RR은 현재 연도에서 가장 가까운 연도를 인식하고 YY는 현재세기를 인식함.
032 암시적 형 변환 이해하기
--아래의 쿼리문은 실행이 될까요?
select ename, sal
from emp
where sal = '3000';
/* 실행됨
오라클이 알아서 문자('3000')를 숫자(3000)으로 형 변환함
대용량의 데이터를 다룰 경우 오라클의 검색속도가 느려짐*/
--예를 들어,
select ename, sal
from emp
where sal like '30%';
/* 숫자 문자
위 쿼리의 실행계획을 확인한 결과 > 1 - filter(TO_CHAR("SAL") LIKE '30%')
암시적 형변환 일어남
에러가 나진 않지만 대용량 데이터베이스 환경에서는 성능이 느려지기 때문에 좋은 코드라고 말할 수 없음
like를 자주 사용하는 sql의 컬럼은 처음 테이블 생성할 때부터 문자형으로 생성해줘야함*/
--따라서, 다음과 같이 쿼리를 변경해야함
select ename, sal
from emp
where sal = 3000;문제103. emp500 테이블에서 월급이 3000인 사원의 이름과 월급을 출력하시오.
select ename, sal
from emp500
where sal = '3000';
/* where sal = 3000 으로 입력할 경우
문자 숫자
문자와 숫자가 충돌할 경우 숫자가 우선순위가 높음
암시적 형변환 발생 > to_number(sal)
오라클 실행계획 확인결과 > 1 - filter(TO_NUMBER("SAL")=3000)*/문제104. (점심시간 문제) 직업이 SALESMAN, ANALYST인 사원들의 이름과 월급과 직업을 출력하는데 월급을 출력할 때 천단위가 부여되게 하고 월급이 높은 사원부터 출력되게 하시오
select ename, to_char(sal, '999,999'), job
from emp
where job in ('SALESMAN','ANALYST')
order by sal desc;
ㅇ일반함수
1. nvl 함수(Null VaLues) : 결측치를 다른 값으로 치환하는 함수
2. decode : 프로그래밍 코드(if문)을 사용하지 않고 함수로 if문을 구현하려는 함수
3. case : decode와 같은 기능을 가짐
033 NULL 값 대신 다른 데이터 출력하기(NVL, NVL2)
문제105. 이름과 월급과 커미션을 출력하시오
select ename, sal, comm
from emp;문제106. 커미션이 null인 사원들의 이름과 월급과 커미션을 출력하시오
select ename, sal, comm
from emp
where comm is null;null = 결측치, 0인건지 값을 안 넣은건지 구분 할 수 없음 > 다른 값으로 치환해줘야함
nvl(컬럼명, null을 대체할 값)
--이름과 커미션을 출력하는데 커미션이 null인 사원들은 0으로 출력되게 하시오
select ename, nvl(comm,0)
from emp;
--null값이 0으로 출력될 뿐, 테이블에 입력된 값이 바뀌진 않음문제107. 이름, 월급, 커미션, 월급+커미션을 출력하세요
select ename, sal, nvl(comm,0), sal+nvl(comm,0)
from emp;
--sal+comm으로 입력하면 출력되지 않음
--null값이 데이터가 없는 상태 또는 알 수 없는 값이기 때문에 사칙연산이 되지 않음문제108. 이름, 커미션을 출력하는데 커미션이 null인 사원들은 no comm이라고 출력되게 하시오
select ename, nvl(to_char(comm), 'no comm')
from emp;
--comm은 숫자형인데 'no comm'은 문자이기 때문에 comm을 문자형으로 변환시켜야함.(데이터 타입 일치)문제109. 이름, 관리자 번호를 출력하는데 관리자번호가 null인 사원들은 no manager라고 출력되게 하시오
select ename, nvl(to_char(mgr), 'no manager')
from emp;문제110. (복습문제) 부서번호가 20번, 30번인 사원들의 이름과 커미션을 출력하는데 커미션이 높은 사원부터 출력하시오
select ename, comm
from emp
where deptno = 20 or deptno = 30
order by 2 desc;
--null이 가장 먼저 출력됨.
select ename, comm
from emp
where deptno = 20 or deptno = 30
order by 2 desc nulls last;
--null이 뒤로 빠짐.
--order by 컬럼명 desc(asc) nulls last;
-- nulls first;문제111. (복습문제)월급이 1000에서 3000 사이인 사원들의 이름과 월급과 입사일을 출력하는데 최근에 입사한 사원부터 출력하시오
select ename, sal, hiredate
from emp
where sal between 1000 and 3000
order by hiredate desc;문제112. (복습문제)이름, 입사년도(4자리), 월급을 출력하는데 입사한 년도를 asc하게 출력하시오
select ename, to_char(hiredate, 'RRRR'), sal
from emp
order by 2 asc;문제113. 위의 결과를 다시 출력하는데 입사한 년도는 asc, 이를 기준으로 월급을 desc하게 출력하시오
select ename, to_char(hiredate, 'RRRR'), sal
from emp
order by 2 asc, 3 desc;
034 IF문을 SQL로 구현하기 1(DECODE)
decode(컬럼명, 조건1, 출력값1, 조건2, 출력값2, 나머지 조건의 값)
출력값은 숫자 or 문자로 통일되어야 함. 출력값에 문자와 숫자 섞어 쓸 수 없음.
/* 이름, 부서번호, 보너스를 출력하는데 보너스가 부서번호가 10번이면 300,
부서번호가 20번이면 400, 나머지 부서번호는 0을 출력하세요*/
select ename, deptno, decode( deptno, 10, 300, 20, 400, 0) as 보너스
from emp;문제114. 우리반 테이블에 이름, 통신사, 보너스를 출력하는데 보너스는 통신사가 lg면 500, kt면 100, 나머지 통신사는 0을 출력하세요
select ename, telecom, decode(lower(telecom),'lg', 500, 'kt', 100, 0) as 보너스
from emp14;문제115. 이름, 직업, 보너스를 출력하는데 보너스가 직업이 SALESMAN이면 5000 그렇지 않으면 0을 출력하시오
select ename, job, decode(job, 'SALESMAN', 5000, 0) as 보너스
from emp;문제116. 이름, 입사한 연도 4자리, 보너스를 출력하는데 입사년도가 1980년이면 9000, 1981년이면 8000, 나머지는 1000으로 출력하시오
select ename, to_char(hiredate,'RRRR'), decode(to_char(hiredate,'RRRR'),'1980',9000,
'1981', 8000, 1000) as 보너스
from emp;문제117. 이름, 월급, 보너스를 출력하는데 월급이 3000이상이면 보너스 9000, 월급이 3000보다 작으면 0 출력하시오
select ename, sal, decode(sal, sal >= 3000, 9000, sal < 3000, 0)
from emp;
/* 에러
decode 값에는 수식을 넣을 수 없음
이 문제는 decode로 해결 할 수 없음. case문을 이용해야함*/
035 IF문을 SQL로 구현하기 2(CASE)
-- 문제117
select ename, sal, case when sal >= 3000 then 9000
else 0 and 보너스
from emp;case when 조건1 then 출력값1
when 조건2 then 출력값2
else 기본값 end
case문은 반드시 end로 닫아줘야 함
문제118. 직업이 SALESMAN,ANALYST인 사원들의 이름, 직업, 월급, 보너스를 출력하는데 월급이 3000이상이면 보너스 500, 월급 2000이상이면 300, 월급 1000 이상이면 200, 나머지는 0으로 출력하세요
select ename, job, sal, case when sal >= 3000 then 500
when sal >= 2000 then 300
when sal >= 1000 then 200
else 0 end 보너스
from emp
where job in ('SALESMAN','ANALYST');문제119. 이름, 나이, 통신사를 출력하는데 통신사가 sk인 학생들을 출력하세요.
select ename, age, telecom
from emp14
where lower(telecom) like 'sk%';문제120. 이름, 나이, 통신사, 보너스를 출력하는데 통신사가 sk면 보너스가 3000 나머지 통신사는 2000 출력하시오.
select ename, age, telecom, case when lower(telecom) like 'sk%' then 3000
else 2000 end 보너스
from emp14;문제121. 이름, 나이, 통신사, 보너스를 출력하는데 통신사가 sk면 보너스 300, lg면 보너스 3500, 나머지는 0을 출력하시오
select ename, age, telecom, case when lower(telecom) in ('sk','skt') then 3000
when lower(telecom) = 'lg' then 2000
else 0 end 보너스
from emp14;
ㅁ decode문의 버그(bug) 현업사례
문제122. 이름, 월급, 직업, 보너스를 출력하는데 직업이 PRESIDENT면 null을 출력하고 나머지 사원들은 자기 월급이 출력되게 하시오
select ename, sal, job, decode(job, 'PRESIDENT', null, sal) as 보너스
from emp;문제123. 위의 결과를 다시 출력하는데 보너스가 높은 사원부터 출력하시오
select ename, sal, job, decode(job, 'PRESIDENT', null, sal) as 보너스
from emp
order by 4 asc; -- 제대로 출력 안됨
/* decode의 버그
decode( job, 'PRESIDENT', null, sal) 마지막의 sal이 문자형으로 바뀌어버림.
null이 문자형이기 때문에 뒤에오는 sal이 문자형으로 암시적 형변환 발생*/
select ename, sal, job, decode(job, 'PRESIDENT', to_number(null), sal) as 보너스
from emp
order by 보너스 desc;
문제124. (오늘의 마지막 문제) 직업이 SALESMAN, ANALYST가 아닌 사원들의 이름, 월급, 부서번호와 보너스를 출력하는데 부서번호가 10번이면 5000 아니면 0이 나오게 하시오. 그리고 월급을 출력할 때 천 단위를 부여, 컬럼명을 출력할 때 한글로 출력되게 하시오
select ename as 이름, to_char(sal, '999,999') as 월급, deptno as 부서번호, decode(deptno, 10, 5000, 0) as 보너스
from emp
where job not in ('ANALYST','SALESMAN');