오늘의 TIL
- NULL값 대신 다른 데이터 출력하기 (NVL, NVL2)
- 공공 데이터 포탈에서 데이터 다운, 큰 데이터 있는 테이블 만들기
- IF문을 SQL로 구현하기 1(DECODE)
- IF문을 SQL로 구현하기 2(CASE)
- ORDER BY 절에서 NULL값을 다루는 옵션 (NULLS LAST, NULLS FIRST)
- GROUP BY 절
- 최대값 출력하기 (MAX) -> 복수행 함수 시작!
- 최소값 출력하기(MIN)
문제 121. 부서번호가 10, 20번인 사원들의 이름, 월급, 부서번호를 출력하는데 월급이 높은 사원부터 출력
select ename, sal, deptno from emp order by deptno desc;
문제 122. 이름의 첫글자가 S로 시작하는 사원들의 이름, 월급, 입사일을 출력하는데 월급을 출력할 때 다음과 같이 천단위를 부여해서 출력하시오. ex) 3,000 -> 책 p.94
select ename, to_char(sal, '999,999'), hiredate from emp where ename like 'S%';
문제 123. 이름, 보너스(sal * 2300) 을 출력하는데 한글로 이름과 보너스라고 컬럼명이 나오게 하고, 보너스를 출력할때 천단위, 백만단위의 콤마가 출력되게 하시오.
NULL값 대신 다른 데이터 출력하기 (NVL, NVL2)
문제 124. 이름, 월급, 커미션, 월급+커미션 출력
- 커미션이 null이면 sal + comm 이 null로 출력돈다. null이 알 수 없는 값이기 때문!
계산이 되게 하려면, null 값을 다른 값으로 치환해야 한다.
치환되게 하는 함수가nvl
<NVL의 문법!>
nvl(컬럼명, null대신에 나올 값)
select ename, sal, nvl(comm, 0)
from emp;문제 125. 이름, 월급, 커미션, 월급 + 커미션을 출력하는데, 월급 + 커미션에서 커미션이 null이면 자기 월급이라도 출력되게 하시오
내 답
select ename, sal, comm ,nvl(sal+comm, sal) from emp;수업
select ename, sal, comm , sal + nvl(comm, 0) from emp;
문제 126. nvl2 함수를 사용해서 이름, 월급, 커미션, 월급 + 커미션 출력
select ename, sal, comm , nvl2(comm, sal+comm, sal) from emp;
<nvl2 함수의 문법>
nvl2(comm, comm이 null이 아닐 때, comm이 null일 때)
select ename, sal, comm , nvl2(comm, sal+comm, sal)
from emp; 문제 127. comm이 null인 사원들의 이름, 월급, 커미션을 출력하는데 커미션이 null인 사원들은 0으로 출력하세요
select ename, sal, nvl(comm,0) from emp where comm is null;
컬럼 삭제했음!
delete from emp where empno in (1234, 2222); // 사원번호가 1234, 2222인 사람들 지워줘. commit; // 지금 삭제한거를 데이터베이스에 영구 저장하겠음 !세미콜론을 commit 옆에 쓰지않고 , from emp 옆에 썼다면 모든게 지워지니까 조심하기!
문제 128. (ocp 시험 문제) 이름, 커미션을 출력하는데 커미션이 null인 사원들은 no comm이라는 글씨로 출력하시오.
내 오답
select ename, nvl(comm , 'no comm') from emp;정답
select ename, nvl( to_char(comm) , 'no comm') from emp;
- 오답이 틀린 이유는, comm은 숫자이고 'no comm'은 문자형이라서! 'no comm'을 숫자로 바꿀수는 없고다. comm은 암시적 형변환이 안되어서 직접 문자형으로 바꿔줘야한다.
no_char사용!
? 복습 !
명시적 형변환 함수 3가지
1. to_char: 문자형으로 형변환
2. to_number: 숫자형으로 형변환
3. to_date: 날짜형으로 형변환
문제 129. (ocp 시험 문제) 이름, 관리자번호(mgr)을 출력하는데 , 관리자번호가 null인 사원들은 no manager라는 글씨로 출력되게 하시오.
select ename, nvl( to_char(mgr), 'no manager') from emp;
- my sql은 !!
SELECT ename, IFNULL(CAST(mgr AS CHAR), 'no manager') AS manager FROM emp;NVL 함수 대신 IFNULL 함수
큰 데이터가 있는 테이블 만들기!
공공데이터 포탈에서 다운받은 서울시 소상공인 관련한 데이터를 가져와서 넣었다.
1. 컬럼명 만들기
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)
) ;2. table 리스트 안에 market_2022 오른쪽 마우스 누르고 데이터 임포트 클릭
3. 찾아보기 해서 아까 다운받은 파일 열기.
4. 계속 다음 다음 다음 누르고 완료 했는데, 원래 실무에서는 여기에서 에러메세지가 날 수 있다. 지금은 테이블이 예뻐서 에러가 안나는 것!
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 '%스타벅스%';정답 -> 상호명이 스타벅스인 모든 행, 컬럼을 출력해야 하니까
*을 써야함select * from market_2022 where 상호명 like '%스타벅스%';
문제 132. (복습) 위 결과를 다시 출력하는데, 시군구명이 강남구인 것만 출력 (and) !!!
select * from market_2022 where 상호명 like '%스타벅스%' and 시군구명 = '강남구';
IF문을 SQL로 구현하기 1(DECODE)
문법(예제). 이름, 부서번호, 보너스를 출력하는데, 보너스가 부서번호가 10번이면 5000을 출력하고, 부서번호가 20번이면 3000을 출력하고, 나머지 부서번호는 0을 출력하시오.
SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 5000, // 부서번호가 10이면 5000 출력 20, 3000, 0) AS 보너스 // 20 이면 3000출력 아니면 0을 출력해 FROM EMP;문법은,
DECODE( 컬럼명, 찾을값1, 출력할 값1, 찾을값2, 출력할값2, 기본값)
- DE(decaffeine의 DE!) , DECODE는 코드없이 IF문을 구현하겠다. 라는 함수!
함수의 종류 복습 !
- 단일행 함수 :
문자, 숫자, 날짜, 변환,일반 (nvl 까지 배웠음) - 복수행 함수 : max, min, avg, sum, count
문제 133. 이름, 직업, 보너스를 출력하는데 보너스가 직업이 SALESMAN 은 9000을 출력하고, 직업이 ALANYST면 7000을 출력하고, 매니저면 6000을 출력하고, 나머지 직업은 0을 출력
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN', 9000, 'ANALYST', 7000, 'MANAGER', 6000, 0) AS 보너스 FROM EMP;
- job 옆에 'salesman'이라는 문자열은 9000과 형이 상관없다. job이랑 'salesman' 이 문자형인지 숫자형인지만 형이 잘 맞으면 됨!
문제 134. 이름, 월급, 입사일, 입사한 년도(4자리)를 출력하시오!
내 답(오답일듯)
select ename, sal, hiredate, '19' || substr(hiredate, 1, 2) from emp;정답
select ename, sal, hiredate, to_char(hiredate, 'RRRR') from emp;
문제 135. 이름, 월급, 입사한 년도 4자리, 보너스를 출력하는데 입사한 년도가 1981년도면 보너스를 9000으로 출력하고 나머리 년도는 그냥 0으로 출력하시오.
select ename, sal, hiredate, to_char(hiredate, 'RRRR') as "년도", decode(to_char(hiredate, 'RRRR'),'1981',9000, 0) as "보너스" // decode 옆에 바로 hiredate오면 안됨
문제 136. 이름, 이름의 첫번째 철자만 출력. 사원테이블
select ename, substr(ename, 1, 1) from emp;
문제 137. 이름, 보너스를 출력하는데, 이름의 첫번째 철자가 S이면 보너스를 5000을 출력하고, A면 3000을 출력하고 나머지는 0을 출력
select ename, substr(ename, 1, 1) as "첫번째 철자", decode(substr(ename, 1, 1),'S', 5000, 'A', 3000, 0) as "보너스" from emp;
문제 138. 이름, 직업, 월급을 출력하는데 월급이 높은 사원부터 출력
select ename, job, sal from emp order by sal desc;
문제 139. 이름, 보너스를 출력하는데 직업이 프레지던트면 보너스를 null로 출력하고, 나머지 사원들은 자기 자신의 월급이 보너스로 출력
select ename, job, decode(job, 'PRESIDENT', null, sal) as "보너스" from emp;
오라클의 버그..!!!
-> decode의 유명한 암시적 형변환 사례! (아직도 해결이 안되었다.)
문제 140. 위 결과를 다시 출력하는데, 보너스가 높은 사원부터 출력
- decode( job, 'PRESIDENT', null, sal) -> 3번째 인자값의 데이터 유형에 의해서 4번째 인자값의 데이터 유형이 결정된다. 위 코드에서 null은 문자형이라서 sal이 문자형으로 출력이 된 것이다...!!
숫자형으로 봤을 때는 5000이 제일 큰 것이지만, 문자형으로 봤을 때는9가 크니까 위 데이터 출력이 저렇게 나온 것!
문제 141. (SQLP) 위의 결과가 제대로 나오게 sql튜닝 하기
select ename, job, decode(job, 'PRESIDENT', to_number(null), sal) as "보너스" from emp order by 3 desc;
- to_number(null) 를 그냥 0 으로 써줘도 상관없다 !
IF문을 SQL로 구현하기 2(CASE)
DECODE는 이퀄(=) 비교만 가능하다. 그런데 CASE는 이퀄(=)비교 뿐만아니라 NON EQUAL 비교도 가능하다.
문제 142. 이름, 월급, 보너스를 출력하는데 월급이 3000 이상이면 보너스를 9000을 출력하고, 월급이 1000이상이고 3000보다 작으면, 보너스를 2000을 출력하고 나머지 사원들은 0
-> 이문제는 decode로 불가!
SELECT ename, job, case when sal >= 3000 then 9000 when sal >= 1000 then 2000 else 0 end as 보너스 FROM emp;
문제 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 ORDER BY 3 DESC;SELECT ENAME, DEPTNO, CASE DEPTNO WHEN 10 THEN 9000 WHEN 20 THEN 6000 ELSE 0 END AS 보너스 FROM EMP ORDER BY 3 DESC;
- 방법이 위처럼 두개! CASE뒤에 DEPTNO, WHEN 자리 바뀌어도 됨
문제 144. 이름, 보너스를 출력하는데 커미션이 NULL이면 보너스를 9000 출력, 커미션이 NULL이 아니면 보너스를 5000 출력하기
SELECT ENAME, COMM ,CASE WHEN COMM IS NULL THEN 9000 ELSE 5000 END AS 보너스 FROM EMP;
ORDER BY 절에서 NULL값을 다루는 옵션 (NULLS LAST, NULLS FIRST)
예제. 이름, 커미션을 출력하는데, 커미션이 높은 사원부터 낮은 사원 순으로 출력
이렇게만 출력하면 NULL이 맨 위에 나오면서 보기가 불편하다. NULL을 맨 아래로 빼고싶다면, ORDER BY 절 DESC/ASC 끝에 NULLS LAST 붙여주자! (NULLS FIRST도 쓸 수 있다. 이게 디폴트!)
SELECT ENAME, COMM
FROM EMP
ORDER BY COMM DESC NULLS LAST;
문제 145. 아래의 결과에서 보너스 NULL 값을 맨 아래에 출력하기
ex)
select ename, job, decode(job, 'PRESIDENT', to_number(null), sal) as "보너스" from emp order by 3 desc ;정답
select ename, job, decode(job, 'PRESIDENT', to_number(null), sal) as "보너스" from emp order by 3 desc NULLS LAST; // 이렇게 nulls last 붙여주기 !!
여기까지 단일행 함수- single row function (문자, 숫자, 날짜, 변환, 일반) 을 공부했다!
이제 복수행 함수를- multiple row function (max, min, avg, sum, count)
단일행 함수는 하나의 행(row) 가 함수에 들어오면 하나의 결과값으로 출력되는 것이고, 복수행 함수는 여러개의 행(row)가 입력이 되서 하나의 값으로 출력!
복수행 함수 시작!!
최대값 출력하기 (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 '서울%';
문제 148. (복습) 직업이 SALESMAN, ANALYST인 사원들중 최대월급
SELECT MAX(SAL) FROM EMP WHERE JOB IN ('SALESMAN','ANALYST');
문제 149. 통신사가 KT인 학생들중에서의 최대 나이를 출력
SELECT MAX(AGE) FROM EMP17 WHERE LOWER(TELECOM) = 'kt';
GROUP BY 절
데이터를 하나로 GROUPING 해주는 역할을 한다!
정렬은 따로 하지 않는다. 정렬은 ORDER BY!
문제 150. 직업이 SALESMAN인 사원들중에서의 최대월급을 출력하는데 직업도 같이 나오게 하세요.
- 에러가 나는이유! 실행순서가 FROM -> WHERE -> SELECT 인데 MAX(SAL)은 하나가 출력되고 JOB은 여러개가 출력되려고 한다.
이렇게 바꾸면 실행순서가 FROM -> WHERE -> GROUP BY -> SELECT가 되서
WHERE절 실행하면서 SALESMAN , GROUP BY 에서 SALESMAN으로 그루핑을 해서 하나로 묶어줬다.
SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;
여기서 WHERE절을 빼면 각 직업마다의 최고월급이 출력된다.
- 오라클에 옵티마이저가 있는데 평상시에 SQL을 잘 짰다면 옵티마이저도 똑똑해진다...! 정렬된 순서같은것들이 바뀔 수 있다. 그래서 평소에 SQL 잘 짜자 !
문제 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;
실행순서: FROM -> GROUP BY -> SELECT -> ORDER BY
ORDER BY 절은 코딩도 맨 마지막, 실행도 맨 마지막!!
최소값 출력하기(MIN)
- 함수는 어찌되었든 결과를 리턴한다.
SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN'; 여기를 모르고 WHERE JOB = 'SALESMA'; 라고 해도 나올까? -> 나온다. NULL나온다.
- 함수는 WHERE절의 조건이 FALSE여도 결과는 리턴이 된다!!
문제 153. 아래의 출력되는 결과가 NULL이 맞는지 확인해보자
SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMA';이렇게 NVL을 사용해서 NULL이 맞는건지 확인
SELECT NVL(MAX(SAL), 0) FROM EMP WHERE JOB = 'SALESMA';
이렇게 함수가 아닌 SAL만 사용하면 선택된 레코드가 없다고 나온다. 그렇지만 함수를 사용하면 뭐라도 나온다!
문제 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;
문제 157. 위 결과를 다시 출력하는데 직업이 SALESMAN은 제외하고 출력
SELECT JOB, MIN(SAL) // 4. 이거 두개를 출력 FROM EMP // 1. 여기서 WHERE JOB != 'SALESMAN' // 2. 직업 SALESMAN 제끼고 GROUP BY JOB; // 3. JOB을 하나로 그루핑하고
문제 158. 위 결과를 다시 출력하는데, 직업별 최소 월급이 높은 것 부터 정렬해서 출력
SELECT JOB, MIN(SAL) // SELECT + 보고싶은 컬럼명 FROM EMP // FROM + 테이블명 WHERE JOB != 'SALESMAN' // WHERE + 검색조건 GROUP BY JOB // GROUP BY + 그룹핑할 컬럼 ORDER BY 2 DESC; // ORDER BY + 정렬할 컬럼
- 실행순서
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY
문제 159. 우리반 테이블에서 성별, 성별별 최소나이 출력
SELECT GENDER, MIN(AGE) FROM EMP17 GROUP BY GENDER;
문제 160. (복습) 부서번호, 부서번호별 최소 월급을 출력하는데, 부서번호가 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개인데 지금 총 9개가 나왔다.
문제 165. (위문제 해결하기 전에 이거먼저 풀어보기) 위문제를 다시 푸는데 통신사를 소문자로 출력하시오!
SELECT LOWER(TELECOM) , MIN(AGE) FROM EMP17 GROUP BY LOWER(TELECOM);
문제 166. 이름, 통신사를 소문자로 출력하고, 통신사 SKT를 SK로 변경해서 출력하세요!
SELECT ENAME,DECODE(LOWER(TELECOM), 'skt', 'sk', LOWER(TELECOM)) FROM EMP17;
문제 167. 아래와 같이 통신사와 통신사별 최소 나이를 출력하시오.
ex)
통신사 최소나이
kt 25
lg 30
sk ?case 사용
SELECT CASE WHEN LOWER(TELECOM) = 'skt' THEN 'sk' WHEN LOWER(TELECOM) = 'lgu+' THEN 'lg' WHEN LOWER(TELECOM) = 'lgt' THEN 'lg' ELSE LOWER(TELECOM) END AS 통신사, MIN(AGE) FROM EMP17 WHERE LOWER(TELECOM) IS NOT NULL GROUP BY CASE WHEN LOWER(TELECOM) = 'skt' THEN 'sk' WHEN LOWER(TELECOM) = 'lgu+' THEN 'lg' WHEN LOWER(TELECOM) = 'lgt' THEN 'lg' ELSE LOWER(TELECOM) END ORDER BY 통신사 ASC;decode 사용
SELECT DECODE(LOWER(TELECOM), 'skt', 'sk', 'lgt', 'lg', 'lgu+', 'lg', LOWER(TELECOM)) as 통신사 , min(age) as 나이 from emp17 where LOWER(TELECOM) is not null group by DECODE(LOWER(TELECOM), 'skt', 'sk', 'lgt', 'lg', 'lgu+', 'lg', LOWER(TELECOM)) order by 1 asc;인상깊은 다른분들 코드 -> 1,2글자를 짤라부리셨다.
select lower(substr(telecom,1,2)) as 통신사, min(age) as 최소나이 from emp17 where lower(substr(telecom,1,2)) is not null group by lower(substr(telecom,1,2)) order by 2 asc;
궁금한 것
SELECT TELECOM FROM EMP17 WHERE LOWER(TELECOM) = 'kt'; // WHERE UPPER(TELECOM) = 'KT' OR LOWER(TELECOM) = 'kt';
