230626 월 Oracle SQL 1 [함수, NVL, DECODE, CASE, MAX, MIN]

권주희·2023년 6월 26일

CASE WHEN NVL NVL2 TO_CHAR case decode like max min null 정렬 null값 없애기 null값대신 단위콤마 명시적형변환 숫자단위콤마 최대값 최소값 형변환 형태변환

지난 주 복습

기본 SELECT 문 ->
select empco, ename, sal ... => 컬럼명
from emp => 테이블명
where deptco = 20 => 검색조건
order by sal desc; => 정렬할 컬럼
오라클 실행순서 from - where - select - order by
함수 : 1) 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반 (일반부터 이번주~)
2) 복수행 함수 : max, min, sum, avg, count

문제 121. (복습문제) 부서번호가 10, 20번인 사원들의 이름과 월급과 부서번호를 출력하는데 월급이 높은 사원부터 출력하시오.
select ename, sal, deptno
from emp
where deptno in (10,20)
order by sal desc; 
where - in 사용, order by 로 정렬

문제 122. (복습문제) 이름의 첫글자가 s로 시작하는 사원들의 이름과 월급과 입사일을 출력하는데 월급을 출력할때 다음과 같이 천단위를 부여해서 출력하시오 ex) 3,000
*책 p94
select ename, to_char(sal, '999,999'), hiredate
 from emp
 where ename like 'S%';
like - '%' 사용

문제123. (복습문제) 이름, 보너스 (sal*2300) 을 출력하는데,
한글로 이름과 보너스라고 컬럼명이 나오게 하고,
보너스를 출력할 때 천단위, 백만단위의 콤마가 출력되게 하시오
select ename as "이름", 
       to_char(sal*2300, '$999,999,999') as "보너스" 
 from emp;
숫자 자리에 뭐가 와도 관계없음
숫자->문자로 변환하는 to_char 함수를 사용

(복습문제) 지난주 마지막문제 -> 나중에 풀어보기

33. NULL 값 대신 다른 데이터 출력하기 (NVL, NVL2)

테이블의 결측치 데이터를 오라클에서는 null값이라 한다.
null 값? -> 1. 데이터가 없는 상태
2. 알수없는 값 (unknown)

문제124. 이름과 월급, 커미션, 월급 + 커미션을 출력하시오 !
select ename, sal, comm, sal+comm
 from emp;
커미션(comm)이 null 이면 sal+comm 도 null로 출력됨
왜냐면 null 이 알 수 없는 값이기 때문
-> 그래서 계산이 되게 하려면 null 값을 다른 값으로 바꿔줘야함 -> NVL
nvl(컬럼명, o)
뒤에거는 null 값대신 나올 값을 적어주면됨

문제125.
이름과 월급과 커미션, 월급 + 커미션을 출력하는데
월급 + 커미션에서 커미션이 null 이면 자기 월급이라도 출력되게하시오 !
select ename, sal, comm, sal + nvl(comm,0)
 from emp;
-> nvl(comm,0) 을 쓰면 실제로 데이터가 null에 0으로 변경되는 것은 아니고 출력되는 sql 에서만 변경이 되는것

문제 126.
NVL2 함수를 사용해서 이름, 월급, 커미션, 월급+커미션을 출력하시오
(OCP 시험용)
select ename, sal, comm, nvl2(comm, sal+comm, sal)
 from emp;
nvl2 (comm, comm 이 null 아닐때, comm이 null일때)
comm
comm이 null이 아닐때 -> sal+comm
comm이 null일때 -> sal 으로 적은거임!

nvl2 굳이 잘 안씀 -> decode로 ?

문제 127. (복습문제)
커미션이 null인 사원들의 이름과 월급과 커미션을 출력하는데,
커미션이 null인 사원들은 0으로 출력하시오
select ename, sal, nvl(comm,0)
 from emp
 where comm is null;
comm = null 이라고 쓰면 안됨. is 로 써야함
is null , is not null

문제 128. (ocp 시험 문제)
이름, 커미션을 출력하는데 커미션이 null인 사원들은 no comm 이라는 글씨로 출력하시오.
select ename, nvl(comm,'no comm')
 from emp;
라고 했는데 오답임
(comm, 'no comm') 에서
앞쪽은 숫자형, 뒷쪽은 문자형이라서 작동안함

NVL에서 꼭 숫자형이여야 하는건 아니고 숫자형, 숫자형 혹은 문자형, 문자형 으로 결과값이 통일 되어야 함.

명시적 형 변환 사용해야함 (책 92p) 참고
to_char

명시적 형변환 함수 3가지

to_char : 문자형으로 형변환
to_number : 숫자형으로 형변환

to_date : 날짜형으로 형변환

select ename, nvl(to_char(comm) , 'no comm')
from emp;

테이블 수정! 잠깐하고 감 (A%%B 얘네 지울려고)
사원번호 (empno)가 1234,2222인 사원의 행을 지우시오
delete from emp
where empno in (1234,2222);
commit;
commit은 '저장' 같은 효과

문제 129. (ocp 시험문제)
이름, 관리자번호(mgr)를 출력하는데, 관리자번호가 null인 사원들은 no manager 라는 글씨로 출력되게 하시오
select ename, nvl( to_char(mgr), 'no manager')
 from emp;

공공데이터포털 : 일반인에게 제공하는 다양한 데이터들 볼 수 있음 (공공기관 데이터)
서울시데이터 가져와서 임포트

CREATE TABLE  market_2022
( "상가업소번호" VARCHAR2(200 BYTE), 
"상호명" VARCHAR2(200 BYTE), 
"지점명" VARCHAR2(200 BYTE), 
"상권업종대분류코드" VARCHAR2(200 BYTE), 
"상권업종대분류명" VARCHAR2(200 BYTE), 
"상권업종중분류코드" VARCHAR2(200 BYTE), 
"상권업종중분류명" VARCHAR2(200 BYTE), 
"상권업종소분류코드" VARCHAR2(200 BYTE), 
"상권업종소분류명" VARCHAR2(200 BYTE), 
"표준산업분류코드" VARCHAR2(200 BYTE), 
"표준산업분류명" VARCHAR2(200 BYTE), 
"시도코드" VARCHAR2(200 BYTE), 
"시도명" VARCHAR2(200 BYTE), 
"시군구코드" VARCHAR2(200 BYTE), 
"시군구명" VARCHAR2(200 BYTE), 
"행정동코드" VARCHAR2(200 BYTE), 
"행정동명" VARCHAR2(200 BYTE), 
"법정동코드" VARCHAR2(200 BYTE), 
"법정동명" VARCHAR2(200 BYTE), 
"지번코드" VARCHAR2(200 BYTE), 
"대지구분코드" VARCHAR2(200 BYTE), 
"대지구분명" VARCHAR2(200 BYTE), 
"지번본번지" VARCHAR2(200 BYTE), 
"지번부번지" VARCHAR2(200 BYTE), 
"지번주소" VARCHAR2(200 BYTE), 
"도로명코드" VARCHAR2(200 BYTE), 
"도로명" VARCHAR2(200 BYTE), 
"건물본번지" VARCHAR2(200 BYTE), 
"건물부번지" VARCHAR2(200 BYTE), 
"건물관리번호" VARCHAR2(200 BYTE), 
"건물명" VARCHAR2(200 BYTE), 
"도로명주소" VARCHAR2(200 BYTE), 
"구우편번호" VARCHAR2(200 BYTE), 
"신우편번호" VARCHAR2(200 BYTE), 
"동정보" VARCHAR2(200 BYTE), 
"층정보" VARCHAR2(200 BYTE), 
"호정보" VARCHAR2(200 BYTE), 
"경도" VARCHAR2(200 BYTE), 
"위도" VARCHAR2(200 BYTE)
  ) ;

CVS 다운받은다음 오라클에서 테이블->해당 테이블 선택하고 데이터 임포트에서 불러오기

새로운 테이블 만든다음에 마우스 오른쪽 누르고 새로고침 한번 해줘야 뜸

drop table market_2019;

지우는거

select count(*) from market_2017;
select count (*) from market_2022;

문제 130. (복습문제) market_2022 에 시군구명 컬럼의 데이터를 출력하는데,
중복을 제거해서 출력하시오
select distinct 시군구명
 from market_2022;
시군구명 문자인데 ''안써도 되나봄 (컬럼명에는 상관없는듯)

문제 131. (복습문제) market_2022 에서 상호명이 스타벅스를 포함하고 있는 모든 행과 모든 컬럼을 출력하시오
select * 
 from market_2022
 where 상호명 like '%스타벅스';
상호명 = '스타벅스' 아니고 like % 써야댐

문제 132. (복습문제) 위의 결과를 다시 출력하는데 시군구명이 강남구인 것만 출력하시오
select *
 from market_2022
 where 상호명 like '%스타벅스' and 시군구명='강남구';
논리연산자 and 써서 두가지 지정

함수

단일행함수 : 문자, 숫자, 날짜, 변환, 일반 (single row function)
-> 하나의 행 (row) 가 들어와서 결과값을 내는것
복수행함수 : max, min, avg, sum, count (multiple row function)
-> 여러개의 행 (row) 가 들어와서 결과값을 내는것

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

문법 : 이름, 부서번호, 보너스를 출력하는데
보너스가 부서번호가 10번이면 5000을 출력하고,
부서번호가 20번이면 3000을 출력하고,
나머지 부서번호는 0을 출력하시오 (보너스 라는 컬럼 만들기)

select ename, deptno, decode(deptno, 10, 5000,
                                    20, 3000, 0) as 보너스
  from emp;

decode

decode : 코드없이 if 문을 구현하겠다. 없는 컬럼을 출력하기 위한 장치라고 생각?하면 될듯
0 은 그렇지 않으면~ 0을 출력해라~
decode( 컬럼명, 찾을값1, 출력할값1, 찾을값2, 출력할값2, 기본값)

문제133.
이름, 직업, 보너스를 출력하는데
보너스가 직업이 SALESMAN은 9000을 출력하고
직업이 ANALYST 면 7000을 출력하고
직업이 MANAGER 면 6000을 출력하고
나머지 직업은 0을 출력하시오
select ename, job, decode(job, 'SALESMAN', 9000, 'ANALYST', 7000, 'MANAGER', 6000, 0) as 보너스
 from emp;
괄호안에 직업 이름에 '' 둘러줘야함

문제134.
이름, 월급, 입사일, 입사한 년도 (4자리)를 출력하시오
select ename, sal, hiredate, TO_CHAR(hiredate, 'RRRR')
 from emp;
to_char( 날짜 컬럼, 날짜포맷) RRRR, MM, DD, DAY ...

문제135.
이름, 월급, 입사한 년도 4자리, 보너스를 출력하는데
입사한 년도가 1981년도면 보너스를 9000으로 출력하고,
나머지 년도는 그냥 0으로 출력하시오
select ename, sal, to_char(hiredate, 'RRRR'), decode(hiredate, 1981, 9000, 0) as 보너스
from emp;
라고 썼는데 오답
select ename, sal, to_char(hiredate,'RRRR') as 년도,
decode( to_char(hiredate,'RRRR'), '1981', 9000, 0) as 보너스
from emp;
~~* 줄바꿔서 셀렉트 컬럼쓸때 쉼표 까먹지말기..!!
~~

문제 136. (복습문제)
사원 테이블에서 이름, 이름의 첫번째 철자만 출력하시오 **-> substr 사용 **
select ename, substr( ename,1,1)
from emp;

문제 137. (복습)
이름, 보너스를 출력하는데 보너스가 이름의 첫번째 철자가 s이면 보너스를 5000을 출력하고,
A 이면 3000을 출력하고,
나머지 사원들은 0을 출력하시오
select ename, decode( substr(ename,1,1), 'S', 5000,
                                         'A', 3000, 0) as 보너스
 from emp;
decode 는 like 를 쓸수 없음

문제 138. (decode 관련 이슈 관련 문제)
이름과 직업과 월급을 출력하는데, 월급이 높은 사원부터 출력하시오
select ename, job, sal
 from emp
 order by sal desc;

문제 139. 이름과 보너스를 출력하는데, 보너스가 직업이 PRESIDENT 면 null 로 출력하고, 나머지 사원들은 자기 자신의 월급이 보너스로 출력되게 하시오.
select ename, decode( job, 'PRESIDENT', null, sal) as 보너스
 from emp;

문제 140.
위의 결과를 다시 출력하는데 보너스가 높은 사원부터 출력하시오
select ename, decode( job, 'PRESIDENT', null, sal) as 보너스
 from emp
 order by 보너스 desc;
라고 했는데 오답

decode null 오류

decode( job, 'PRESIDENT', null, sal)

null 자리의 데이터 유형으로 sal 자리의 데이터 유형으로 결정됨
(3번째 인자값의 데이터 유형에 의해서 네번째 인자값의 데이터 유형이 결정된다.)
'null'은 문자형이기 때문에 sal이 문자형으로 출력이 된 것이다.
문자형에서 9가 큰 수라서 문자형으로 인식되어서 제일 위에 나온 것.

문제 141. (SQLP 시험 출제문제)
위의 결과가 제대로 나오게 sql을 튜닝하시오
select ename, 
       decode( job, 'PRESIDENT', to_number(null), sal ) as 보너스
  from emp
  order by 보너스 desc ;
decode 의 유명한 암시적 형 변환 사례, 오라클 버그임 (해결x)
decode 는 오라클의 특이한 함수임

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

decode 는 이퀄 (=) 비교만 가능하다. 그런데 case는 이퀄(=)비교 뿐만 아니라 non equal 비교도 가능 (등호, 부등호 기호 사용 가능)

case

문제 142.
이름, 월급, 보너스를 출력하는데
월급이 3000 이상이면 보너스를 9000을 출력하고
월급이 1000 이상이고 3000보다 작으면 보너스를 2000을 출력하고
나머지 사원들은 0 을 출력하시오
select ename, sal, case when sal >= 3000 then 9000
                        when sal >= 1000 then 2000
                   else 0 end as 보너스
  from emp;
첫째줄에서 3000 이상인 사람들은 걸러졌기 때문에 밑에는 1000이상만 적어주면 됨
WHEN sal >= 1000 AND sal < 3000 THEN 2000 로 쭉 써도 됨!!

문제 143.
이름, 부서번호, 보너스를 출력하는데
보너스가 부서번호가 10번이면 9000을 출력하고
부서번호가 20번이면 6000을 출력하고
나머지 부서번호면 0을 출력하시오.
select ename, deptno, case when deptno = 10 then 9000
                           when deptno = 20 then 6000
                      else 0 end as 보너스
 from emp;
select ename, deptno, case deptno when 10 then 9000
                                  when 20 then 6000
                      else 0 end as 보너스
 from emp;
두가지 다 사용가능!

문제 144.
이름과 보너스를 출력하는데
커미션이 null이면 보너스를 9000을 출력하고,
커미션이 null이 아니면 보너스를 5000을 출력하시오
select ename, case when comm = null then 9000
              else 5000 and as 보너스
 from emp;
라고 썼는데 오류 발생
select ename, comm, case when comm is null then 9000
              else 5000 end as 보너스
 from emp;
comm is null 로 써야함..?ㅠ = 안되나여
and 아니고 end 임!

order by 절에서 null 값을 다루는 옵션

-> nulls last / nulls first

예제. 이름과 커미션을 출력하는데,
커미션이 높은 사원부터 낮은 사원순으로 출력하시오.
select ename, comm
 from emp
 order by comm desc;
이렇게 출력하면 null 이 맨 위에 나오면서 보기 불편
null 을 아래로 보내려면
select ename, comm
 from emp
 order by comm desc nulls last;
OCP 시험문제임 (NULL 을 제일 마지막에 나오게 하는 문제)
nulls last 또는 nulls first 를 쓸 수 있다.
null 의 디폴트 값은 제일 앞에 나오는것

문제 145.
아래의 결과에서 보너스 null 값을 맨 아래에 출력되게 하시오
select ename, 
       decode( job, 'PRESIDENT', to_number(null), sal ) as 보너스
  from emp
  order by 보너스 desc ;
여기서 nulls last 추가
select ename, 
      decode( job, 'PRESIDENT', to_number(null), sal ) as 보너스
 from emp
 order by 보너스 desc nulls last;

036. 최대값 출력하기 (MAX)

예제. 사원테이블에서 최대 월급을 출력하시오
select max(sal)
 from emp;

문제 146.
직업이 SALESMAN 인 사원들 중에서의 최대 월급을 출력하시오
select max(sal)
 from emp
 where job='SALESMAN';
문제 147.
우리반 테이블 에서 서울에서 사는 학생중에 최대 나이를 출력하시오
select max(age)
 from emp17
 where address like '서울%';
이름까지 뽑는건 아직 안됨 서브 쿼리 배워야 된데
-> 해봄!!
select ename, max(age) as 최대나이
from emp17
group by ename
having max(age) = ( select max(age)
                     from emp17
                     where address like '서울%' ) ;
                     ```

문제 148. (복습문제)
직업이 SALESMAN, ANALYST 인 사원들 중에 최대 월급을 출력하시오
select max(sal)
 from emp
 where job in ('SALESMAN', 'ANALYST');

문제 149.
통신사가 KT 인 학생들중에서의 최대 나이를 출력하시오
select max(age)
 from emp17
 where lower(telecom) = 'kt';

문제 150. 직업이 SALESMAN 인 사원들중에서의 최대 월급을 출력하시오
select max(sal)
 from emp
 where job='SALESMAN';
추가로, SALESMAN 1600 이렇게 직업이름도 출력되게 하고 싶으면,
select job, max(sal)
 from emp
 where job='SALESMAN';
이렇게 쓰면 단일그룹함수가 아닙니다 라고 에러가 난다.
이유 : 실행순서가 안맞음 (프롬절 -> 웨어절-> 셀렉절 인데)
max 셀은 한개가 나오게 하는건데, job 은 여러개가 출력되려고 하는 거라 에러 남.
select job, max(sal)
 from emp
 where job='SALESMAN'
 group by job;
코딩 순서 : select -> from -> where -> group
실행 순서 : from -> where -> group -> select

그룹핑을 해줘야 함!

group by 절의 역할 : 데이터를 grouping 해주는 역할을 한다.
where 절 빼고도 사용 가능
select job, max(sal)
 from emp
 group by job;
where 절 빼고 사용하게 되면, job 별로 그룹핑 한다음에 그중에서 max 결과가 나옴

A,B,C,D 순 정렬 안함

문제 151. 부서번호, 부서번호별 최대 월급을 출력하시오
select deptno, max(sal)
 from emp
 group by deptno;

문제 152. 부서번호, 부서번호별 최대 월급을 출력하는데,
부서번호가 10,20,30번 순으로 정렬 되어서 출력되게 하시오.
select deptno, max(sal)
 from emp
 group by deptno
 order by deptno asc;
order by 절은 항상 가장 마지막에

실행순서 : from -> group -> select -> order

order by 절은 코딩도, 실행도 가장 마지막에 쓰임

※ 함수는 어찌되었든 결과를 리턴해준다. (sqlp 시험용)

select max(sal)
 from emp
 where job='SALESMA';

등으로 오타나 잘못 입력했을경우에도 결과는 NULL 나오긴 한다.
(where 절의 조건이 false 여도 결과는 리턴이 됩니다.)

문제 153.
아래의 출력되는 결과가 null 값이 맞는지 확인해보시오
select max(sal)
 from emp
 where job='SALESMA'; 
select nvl( max(sal), 0)
 from emp
 where job='SALESMA'; 
선택된 레코드가 없다고 나오지 않는다. null 값이라도 출력한다.

037. 최소값 출력하기 (MIN)

문제 154.
사원테이블에서 최소 월급을 출력하시오
select min(sal)
 from emp;

문제 155.
직업이 SALESMAN 인 사원들 중에서의 최소월급을 출력하시오
select min(sal)
 from emp
 where job ='SALESMAN';

문제 156.
직업과 직업별 최소월급을 출력하시오
select job, min(sal)
 from emp
 group by job;
where 절 쓰지 않아도 됨! 그룹핑만! (from->group->select 순서로 실행)

문제 157.
위의 결과를 다시 출력하는데, 직업이 SALESMAN 은 제외하고 출력하시오
select job, min(sal)
 from emp
 where job !='SALESMAN'
 group by job;
!= '같지 않다' 의미의 비교 연산자

문제 158.
위의 결과를 다시 출력하는데, 직업별 최소 월급이 높은 것부터 출력하시오
select job, min(sal)
 from emp
 where job !='SALESMAN'
 group by job
 order by 2 desc;

select 보고 싶은 컬럼명
from 테이블명
where 검색조건
group by 그룹핑할 컬럼
order by 정렬할 컬럼
실행 순서 : from->where->group->select->order

문제 159.
우리반 테이블에서 성별, 성별별 최소나이를 출력하시오
select gender, min(age)
 from emp17
 group by gender;
이름까지 보이게 하는건 아직 안됨!

문제 160. (복습문제)
부서번호, 부서번호별 최소 월급을 출력하는데
부서번호가 10번과 20번만 출력되게 하시오
select deptno, min(sal)
 from emp17
 where deptno in (10, 20);
라고 썼는데 오류 발생
select deptno, min(sal)
 from emp
 where deptno in (10, 20)
 group by deptno;

문제 161. (복습문제)
우리반 테이블에서 이름의 성씨만 출력하시오.
select substr( ename, 1, 1)
 from emp17;

문제 162.
우리반 테이블에서 성씨, 성씨별 최소나이를 출력하시오
select substr( ename, 1, 1), min(age)
 from emp17
 group by substr( ename, 1, 1);
셀렉트에 나올 주어를 그룹에도 그래도 써야함

문제 163.
emp 테이블에서 입사한 년도 (4자리), 입사한 년도별 최소 월급을 출력하시오
select to_char(hiredate, 'RRRR'), min(sal)
 from emp
 group by to_char(hiredate, 'RRRR');

문제 164.
우리반 테이블에서 통신사, 통신사별 최소나이를 출력하시오
select telecom, min(age)
 from emp17
 group by telecom;
라고 하면 소문자, 대문자 별로 통신사가 다 나뉘어짐
제대로 통신사가 3개만 나오게 해줘야 할 때는,

문제 165.
위의 164번을 다시 수행하는데, 통신사를 소문자로 출력하시오!
select lower(telecom), min(age)
 from emp17
 group by lower(telecom);
이렇게 하면 일단 소문자로 바꿔서 그룹핑 되었음
이 상태에서 3개로 더 결과를 줄이려면,

문제 166.
이름, 통신사를 출력하는데, 통신사를 소문자로 출력하고,
통신사 skt를 sk로 출력하시오 -> decode 함수 사용
select ename, decode( lower(telecom), 'skt', 'sk',lower(telecom) )
 from emp17;

문제 167. (마지막문제)
아래와 같이 통신사와 통신사별 최소 나이가 출력되게 하시오
통신사 | 최소나이
kt | 25
lg | 25
sk | ?
select decode( lower(telecom), 'skt' , 'sk',
               'lgu+' , 'lg' ,
               'lgt' , 'lg' , lower(telecom)) as 통신사 , min(age)
 from emp17
 where lower(telecom) in ('kt' , 'lg' , 'sk')
 group by lower(telecom)
 order by 통신사 asc;
하니까 답 나오긴 했음 * where 절 사용
그런데

이렇게 하는게 더 나을듯? * group by 사용

권주희

열씨미하자

다음 포스트