[SQL-2] SQL 초급

Mar 31, 2024

Contents

PART 2「초급」 SQL 기초 다지기 중요 내용 정리하기

PART 2「초급」 SQL 기초 다지기

016 대소문자 변환 함수 배우기(UPPER, LOWER, INITCAP)

📖

대소문자 변환 함수

UPPER : 대문자로 변환하는 함수

LOWER : 소문자로 변환하는 함수

INITCAP : 문자의 첫번째 철자는 대문자, 나머지는 소문자로 변환하는 함수


select  upper([컬럼명]), lower([컬럼명]), initcap([컬럼명])
   from [테이블명] ;

예제

emp 테이블에서 이름이 SCOTT 인 사원의 데이터를 검색하는데, 이름의 데이터가 대문자로 저장되어있든 소문자로 저장되어있든 상관없이 데이터가 검색되게 하시오


A1. select  ename
		  from  emp
		  where  lower(ename) = 'scott'; 

A2. select  ename
		  from emp
		  where  upper(ename) ='SCOTT';

문제풀며 이해하기

Q1.

통신사가 kt 인 학생들의 이름과 통신사를 출력하시오.

(or 를 사용하지 말고 출력하세요 )


A1. select ename, telecom
	    from emp18
	    where lower(telecom) like '%sk%';

A2. select ename, telecom
	    from emp18
	    where upper(telecom) like '%sk%';

A3. select ename, telecom
	    from emp18
	    where initcap(telecom) like '%sk%';

Q2.

통신사가 sk 인 학생들의 이름과 통신사를 출력하시오 ! ( upper 나 lower 를 활용하세요 ~)


A. select ename, telecom
    from emp18
    where lower(telecom) = 'kt'
        or lower(telecom) = '알뜰kt';

017 문자에서 특정 철자 추출하기(SUBSTR)

📖

SUBSTR 문자열에서 특정 철자만 잘라낼 수 있다


substr([문자열 컬럼], [잘라낼 철자의 자리번호], [개수])

1. 철자 자리번호는 1부터 시작
	ex. SMITH 에서 S = 1, M =2, I = 3, ~~

2. 철자 자리번호 뒤부터 시작하기는 -1부터 시작
	ex. SMITH 에서 H = -1, T = -2, ~~

예제


select ename, substr(ename, 1, 2)
	from emp;

REGEXP_SUBSTR()


regexp_substr([컬럼명],[조건],[조건에 맞는 값의 번호 중 스캔을 시작할 번호],[그 중 조건에 맞는 몇 번째의 번호에 위치한 값을 출력할 것인지])

문제풀며 익히기

Q1.

사원테이블에서 이름을 출력하고 이름의 두번째 철자만 출력하시오.


A. select ename, substr(ename, 2, 1)
    from emp;

Q2.

우리반 테이블에서 이름, 이름의 성씨만 출력하시오.


A. select ename, substr(ename, 1, 1)
    from emp18;

Q3.

우리반에서 성씨가 김, 이, 박 인 학생들의 이름과 생일을 출력하시오.


A. select ename, birth
    from emp18
    where substr(ename,1,1) in ('김','이','박');

Q4.

이름을 출력하는데, 맨 뒷 철자만 출력하시오.


A. select substr(ename, -1, 1)
    from emp;

018 문자열의 길이를 출력하기(LENGTH)

📖

LENGTH 함수 length 는 문자열의 길이를 출력하는 함수 입니다. 예제) 이름, 이름의 철자의 개수를 출력하시오.


select ename, length(ename)
	from emp;

📖

코딩순서 복습


select [보고싶은 컬럼명]
	from [컬럼을 가져올 테이블명]
	where [검색조건]
	order by [정렬할 컬럼명];

문제풀며 익히기

Q1.

우리반 테이블에서 이름, 이메일, 이메일의 철자의 개수를 출력하는데 이메일의 철자의 개수가 높은것부터 출력하시오.


A1. select ename, email, length(email)
	    from emp18
	    order by length(email) desc;

A2. select ename, email, length(email)
	    from emp18
	    order by 3 desc;

Q2.

위의 결과를 다시 출력하는데, 이메일의 철자 길이가 20개 이상인 학생들만 출력하시오.


A1. select ename, email, length(email)
	    from emp18
	    where length(email) >= 20
	    order by length(email) desc;

A2.  select ename, email, length(email)
	    from emp18
	    where length(email) >= 20
	    order by 3 desc;

019 문자에서 특정 철자의 위치 출력하기(INSTR)

📖

INSTR 함수 문자열에서 특정 철자의 자리번호를 출력하는 함수

찾는 문자가 없는 행이 출력될 때는 0으로 표시됨

[Oracle] 오라클 INSTR 함수 사용법 완벽 정리 (CHARINDEX, IndexOf)

오라클에서는 문자열에서 원하는 문자를 찾을 때 INSTR 함수를 사용한다. INSTR 함수는 대소문자를 구별하여 문자를 검색하며, 여러 개의 문자를 찾을 때는 OR 연산자를 사용하면 된다. 해당 문자를 한번 찾으면 더 이상 검색을 하지 않기 때문에 필요에 따라서 뒤(마지막)에서 부터 검색을 할 수도 있다. INSTR ( [문자열], [찾을 문자 값], [찾기를 시작할 위치(1,-1)], [찾은 결과의 순번(1...n)] ) 기본 사용법 SELECT INSTR('Oracle Database', 'Database') AS result1 , INSTR('Oracle Database', 'Server') AS result2 FROM dual INSTR 함수는 문자열에서 문자를 찾으면 문자의 시작 위치를 반환한다..

https://gent.tistory.com/22


INSTR ( [문자열], [찾을 문자 값], [찾기를 시작할 위치(1,-1)], [찾은 결과의 순번(1...n)] )

INSTR 함수는 문자열에서 문자를 찾으면 문자의 시작 위치를 반환한다. 문자를 찾지 못하면 "0"을 반환한다. 예제) 우리반 테이블에서 이메일을 출력하고 이메일에서 @ 의 자리번호를 출력하시오.


select email, instr(email, '@')
	from emp18;

문제풀며 익히기

Q1.

우리반 테이블에서 이름을 출력하고, 그 옆에서 이름의 ‘일’자가 몇번째 자리에 있는지 출력하시오.


A. select ename, instr(ename, '일')
     from emp18;

#0은 없다는 의미

Q2.

우리반 테이블에서 이름에 ‘일’자가 포함된 학생의 이름을 출력하시오.


A. select ename, instr(ename, '일')
     from emp18
     where instr(ename, '일') > 0;

Q3.

우리반 테이블에서 이메일과 이메일에서 @앞에 철자만 출력하시오.


A. select email, substr(email,1,instr(email, '@')-1)
    from emp18;

020 특정 철자를 다른 철자로 변경하기(REPLACE)

📖

REPLACE 함수 문자열에서 특정 철자를 다른 철자로 변경하는 함수


replace([컬렴명], [변경 전 데이터], [변경 후 데이터])

예제)


select ename, replace(sal, 0, '*')
	from emp;

regexp_replace 함수

replace 함수의 더 업그레이드 된 버전의 함수

regular(정규) expression(표현식)함수


regexp_replace([컬럼명], ['[변경 원하는 데이터 범위]'], [변경 후 데이터])

예제)

이름과 월급을 출력하는데, 월급이 숫자 0~3까지는 *로 출력하시오


select ename, regexp_replace(sal, '[0-3]', '*')
	from emp;

#0~3까지 *로 바꾸겠다는 것이고
## ~대신 -를 사용해야함
### 범위 지정하는 두번째 인자에 [] 대괄호 반드시 사용

문제풀며 익히기

Q1.

우리반 테이블에서 이름을 출력하는데 아산 병원 전광판 처럼 다음과 같이 출력되게하시오 ! ( substr 과 연결연산자를 이용하세요)

유연❤️


A1. select substr(ename, 1, 2) || '♥'
    from emp18;

A2. select replace(ename, substr(ename, -1, 1), '♥')
			from emp18;

Q3(개인정보 보호법 관련 데이터 암호화).

우리반 테이블에서 이름과 생일과 오늘 날짜를 출력하시오.


A. select ename, birth, sysdate
		from emp18;

#sysdate 오늘 날짜 출력 함수

Q4.

우리반 테이블에서 이름과 나이를 출력하시오.


A. select ename, trunc((sysdate - birth)/365)
    from emp18;

#trunc함수 : 소수점 아래는 반올림 없이 절삭하는 함수

Q5. (개인정보 보호법 SQL 문제)

우리반 테이블의 데이터를 아래와 같이 출력하시오.

홍길동 20대

박지성 30대


A. select ename, substr(trunc((sysdate - birth)/365),1,1) || '0대'
    from emp18;

A2. select ename, trunc(((sysdate - birth)/365),-1) || '대'
	   from emp18;

	#trunc 함수에 -1을 사용해 1의 자리에서 절사 (1의자리가 0이됨)
	## trunc([컬럼명],[옵션])

021 특정 철자를 N개 만큼 채우기(LPAD, RPAD)

📖

출력되는 숫자외에 다른 숫자를 집어넣지 못하도록 숫자를 출력할 때 / *을 채워넣을 때 유용한 함수

LPAD 함수

sal을 출력하는데, 전체 10자리를 잡고 나머지 빈자리에는 *을 채워넣은 것. 나머지 빈자리는 left(왼쪽)부터 채워넣음


lpad([컬럼명], [출력하고 싶은 만큼의 수], [채워넣을 값])

LPAD 함수는 문자열을 지정된 길이만큼 왼쪽으로 채우는 함수입니다. 주로 숫자나 문자열을 일정한 길이로 맞추거나 패딩하는 용도로 사용됩니다.

기본 구문은 다음과 같습니다:


LPAD(string, length, [pad_string])

string: 패딩을 적용할 대상 문자열입니다.

length: 최종 문자열의 길이를 지정합니다. 만약 string이 이미 이 길이보다 길다면 변화 없이 그대로 반환됩니다.

pad_string (옵션): 패딩에 사용할 문자열입니다. 이 파라미터를 생략하면 기본값으로 공백이 사용됩니다.

예를 들어, LPAD('hello', 10)은 ' hello'를 반환합니다. 여기서 'hello'는 5글자이므로 왼쪽에 공백 5글자가 추가되어 최종 길이가 10이 됩니다.

pad_string 파라미터를 사용하여 특정 문자로 패딩할 수도 있습니다. 예를 들어, LPAD('hello', 10, '-')은 '-----hello'를 반환합니다.

이와 유사하게 RPAD 함수는 문자열을 지정된 길이만큼 오른쪽으로 채우는 함수입니다.

예제) 이름과 월급을 출력하는데, 월급을 출력할 때 월급의 나머지 자리에 *을 채워넣으시오


select ename, lpad(sal, 10, '*')
	from emp;

022 특정 철자 잘라내기(TRIM, RTRIM, LTRIM)

📖

문자열에서 특정 철자를 잘라낼때 사용하는 함수 주로 공백문자를 잘라낼 때 사용함

TRIM

양쪽 공백을 잘라내는 함수

예제)


insert into emp(empno, ename, sal )
	values( 1234, 'JACK    ', 3400 );

select ename, sal
	from emp
	where ename = 'JACK'

#INSERT 할때 이름에 공백값도 함께 입력되어 select 되지 않음

select ename, sal 
	from emp
	where trim(ename)='JACK'

#TRIM(ENAME) 이라고 하면 ENAME 에서 양쪽 공백을 잘라냄

RTRIM 함수

원하는 값의 right(오른쪽)을 잘라내는 함수


rtrim([컬럼명], [잘라낼 값])

예제)


ex 1.
우리반 테이블에서 이메일을 출력하는데 이메일 끝에 
.com 을 잘라내서 출력하시오 !

	 A. select  email, rtrim( email, '.com')
		   from  emp18;

ex 2.
위의 결과를 다시 출력하는 .net 도 잘라내시오

	A. select  email, rtrim( rtrim(email, '.com'), '.net')
       from  emp18;

	error. 
			kimjunhwan11@nate.com → kimjunhwan11@na
			.net 중에 어느 하나의 철자가 있으면 잘라내기 때문에 발생하는 오류

LTRIM 함수

문제풀며 익히기

Q1.

우리반 테이블에서 인천 논현동에 거주하는 학생의 이름과 주소를 출력하시오.(단, like는 사용하지 않고 출력)


A. select ename, address
    from emp18
    where trim(address) = '인천 논현동';

023 반올림해서 출력하기(ROUND)

📖

숫자함수 ROUND : 반올림하는 함수

문법


round( [숫자]or[컬럼명], [옵션] )

옵션


1. 숫자 '0' : 소수점 바로 아래 값 반올림
		ex) 787.67 -> 788		

2. 숫자 -1 : 소수점 바로 앞의 값을 반올림
						(1의 자리 수 반올림 된 후 0으로 대체)
		ex) 787.67 -> 790

3. 숫자 1 : 소수점 첫번째 자리까지 출력
						(소수점 두번째 자리 반올림)
		ex) 787.67 -> 787.7

DUAL? : select 절의 함수의 결과를 보기 위한 가상의 테이블 예제)


select round(787.67,1)
	from dual;

문제풀며 익히기

Q1.

emp 테이블에서 이름과 월급, 월급의 십의 자리에서 반올림한 숫자를 출력하시오


A. select ename, sal, round(sal,-2)
    from emp;

024 숫자를 버리고 출력하기(TRUNC)

📖

숫자함수 TRUNC

반올림 없이 숫자 버림


   7  8  7 . 6  7  8
# -3 -2 -1 0 1  2  3

default 값 = 0 
따라서 두번째 인자에 아무것도 쓰지 않으면,
소수점 이하는 버림

예제)


select trunc(787.678, -1)
	from dual;

TRUNC(787.678,-1)|
-----------------+
              780|

문제풀며 익히기

Q1.

사원 테이블에서 이름과 월급을 출력하는데, 다음과 같이 십자리를 포해서 이후를 다 지우시오.


A. select ename, trunc(sal, -2)
    from emp;

025 나눈 나머지 값 출력하기(MOD)

📖

숫자함수 MOD

나눈 값의 나머지 출력

예제)


select mod(10, 3)
	from dual;

#10을 3으로 나눈 값인 1이 출력됨

문제풀며 익히기

Q1.

우리반 테이블에서 이름, 나이를 출력하시오.


A. select ename, trunc((sysdate-birth)/365)
    from emp18;

위의 결과를 다시 출력하는데, 나이가 짝수인 학생들만 출력하시오.


A. select ename, trunc((sysdate-birth)/365)
    from emp18
    where mod(trunc((sysdate-birth)/365),2)=0;

Q2. (복습문제)

우리 반에서 나이가 짝수인 학생들의 이름과 나이를 출력하는데, 나이가 많은 학생부터 출력하시오.


A. select ename, trunc((sysdate-birth)/365) as 나이
    from emp18
    where mod(trunc((sysdate-birth)/365),2)=0
    order by 나이 desc;

026 날짜 간 개월 수 출력하기(MONTHS_BETWEEN)

📖

MONTHS_BETWEEN 날짜 1 ~ 날짜 2 사이의 개월 수를 출력하는 함수


months_between([최신 날짜1],[옛날 날짜2])

💡

오라클의 날짜 함수

오라클의 날짜 함수 4가지


1. MONTHS_BETWEEN : 날짜와 날짜 사이의 개월수를 출력하는 함수

2. ADD_MONTHS : 날짜에서 개월수를 더한 날짜를 출력하는 함수

3. NEXT_DAY : 특정 날짜 뒤에 돌아오는 특정 요일의 날짜를 출력하는 함수

4. LAST_DAY : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

문제풀며 익히기

Q1.

사원테이블에서 오늘날짜 - 입사일을 출력하시오.

(단, 소수점 이후는 버리고 출력하시오)


A. select trunc(sysdate - hiredate)
    from emp;

Q2.

우리반 테이블에서 이름을 출력하고, 태어난 날짜부터 오늘까지 총 몇 주 살았는지 출력하시오.


A. select ename, trunc((sysdate - birth)/7)
    from emp18;

Q2.

우리반 테이블에서 이름을 출력하고, 태어난 날짜부터 오늘까지 총 몇 개월 살았는지 출력하시오.


A1. select ename, trunc(months_between(sysdate,birth))
	    from emp18;

A2. select ename, trunc((sysdate-birth)/365)
			from emp18;

!!단, 윤년이 있는 경우 차이 발생!!

* 윤년의 위키피디아의 정의 *
	양력에서 평년은 365일로 구성되는데, 
	4년에 한번씨 366일의 윤년을 사용
	ex) 1988년, 1992년, 1996년은 윤년

	따라서 months_between을 사용하는게 정확함.

Q3.

우리반 테이블에서 그 동안 살아온 달이 300달 이상인 학생들의 이름과 살아온 개월 수를 출력하시오


A. select ename, trunc(months_between(sysdate,birth))
    from emp18
    where trunc(months_between(sysdate,birth)) >= 300;

027 개월 수 더한 날짜 출력하기(ADD_MONTHS)

📖

개월 수 더한 날짜 출력하기(ADD_MONTHS)

💡

오라클의 날짜 함수

오라클의 날짜 함수 4가지


1. MONTHS_BETWEEN : 날짜와 날짜 사이의 개월수를 출력하는 함수

2. ADD_MONTHS : 날짜에서 개월수를 더한 날짜를 출력하는 함수

3. NEXT_DAY : 특정 날짜 뒤에 돌아오는 특정 요일의 날짜를 출력하는 함수

4. LAST_DAY : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

ADD_MONTHS

특정 날짜에 개월 수를 더해주는 함수


ADD_MONTHS( [특정날짜], [더할 개월수] )

예제) 오늘 날짜에서 3일을 더한 날짜를 출력하시오 오늘 날짜에서 3달 뒤의 날짜를 출력하시오.


select add_months(sysdate, 3)
	from dual;

문제풀며 익히기

Q1.

오늘 날짜에서 3년 뒤의 날짜를 출력하시오.


A. select sysdate + (interval '3' year)
	from dual;

Q2.

우리반 테이블에서 이름, 생일, 생일에서 50년 더한 날짜를 출력하시오.


A. select ename, birth, birth + (interval '50' year)
    from emp18;

년도 명확하게 보기


alter session set nls_date_format = 'RRRR/MM/DD';
#지금 접속한 세션(창)에서 날짜형식을 RRRR/MM/DD로 설정하겠다는 뜻

select ename, birth, birth + (interval '50' year)
    from emp18;

028 특정 날짜 뒤에 오는 요일 날짜 출력하기(NEXT_DAY)

📖

NEXT_DAY 함수

💡

오라클의 날짜 함수

오라클의 날짜 함수 4가지


1. MONTHS_BETWEEN : 날짜와 날짜 사이의 개월수를 출력하는 함수

2. ADD_MONTHS : 날짜에서 개월수를 더한 날짜를 출력하는 함수

3. NEXT_DAY : 특정 날짜 뒤에 돌아오는 특정 요일의 날짜를 출력하는 함수

4. LAST_DAY : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

NEXT_DAY 함수

특정 날짜 뒤에 오는 요일 날짜 출력하기


next_day( [특정날짜] , '[특정요일]' )

예제) 오늘 날짜에서 바로 돌아오는 월요일의 날짜를 출력하시오


select next_day(sysdate, '월요일')
	from dual;

문제풀며 익히기

Q1. (오라클 본교재 문제)

오늘부터 100달 뒤에 돌아오는 월요일의 날짜를 출력하시오.


A1. select next_day(sysdate + (interval '100' month), '월요일')
    from dual;

					NEXT_DAY(SYSDATE+(INTERVAL'100'MONTH),'월요일')|
				-------------------------------------------------+
							                    2032-05-10 17:58:32.000|

A2. select next_day(add_months(sysdate,100), '월요일')
    from dual;

Q2.

오늘부터 100년 뒤에 돌아오는 월요일의 날짜를 출력하시오


A1. select next_day(add_months(sysdate,100*12), '월요일')
    from dual;

##interval함수 이용시 99년까지만 가능

A2. select next_day(sysdate + (interval '100' year), '월요일')
    from dual;

	!!ERROR!!
	select next_day(sysdate + (interval '100' year), '월요일')
																			*
	1행에 오류:
	ORA-01873: 간격의 선행 정밀도가 너무 작습니다

029 특정 날짜가 있는 달의 마지막 날짜 출력하기(LAST_DAY)

📖

💡

오라클의 날짜 함수

오라클의 날짜 함수 4가지


1. MONTHS_BETWEEN : 날짜와 날짜 사이의 개월수를 출력하는 함수

2. ADD_MONTHS : 날짜에서 개월수를 더한 날짜를 출력하는 함수

3. NEXT_DAY : 특정 날짜 뒤에 돌아오는 특정 요일의 날짜를 출력하는 함수

4. LAST_DAY : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

LAST_DAY 함수

특정 날짜가 있는 달의 마지막 날짜 출력하기


last_day( [특정날짜] )

예제) 오늘 날짜의 달의 마지막 날짜를 출력하시오


select sysdate, last_day(sysdate)
	from dual;

문제풀며 익히기

Q1.

오늘 날짜에서 하루를 더한 날짜를 출력하고, 그 날짜의 달의 마지막 날짜를 출력하시오.


A. select sysdate+1, last_day(sysdate+1)
    from dual;

Q2. (복습문제)

사원 테이블에서 이름에 S 자를 포함하고 있는 사원들의 이름과 입사일을 출력하는데 입사일이 최근에 입사한 사원부터 출력하시오


A. select ename, hiredate
    from emp
    where ename like '%S%'
    order by hiredate desc;

030 문자형으로 데이터 유형 변환하기(TO_CHAR)

📖

함수의 종류 1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반 2. 복수행 함수 : max, min, avg, sum, count

오라클의 데이터 유형이 크게 3가지 ?

문자형 : 사원 테이블의 이름, 직업과 같은 컬럼

숫자형 : 사원 테이블의 사원번호, 월급, 커미션, 부서번호 같은 컬럼

날짜형 : 사원 테이블의 입사일 컬럼

변환함수 : 데이터 유형을 변환하는 함수

to_char : 문자형으로 변환하는 함수

숫자 단위 표시하기(예제2번 참조)

숫자→ 문자로 변환해야 숫자에 ,(콤마) 넣을 수 있음
단위 표시에서 숫자 9의 의미

숫자 9는 이 자리에 0~9까지 어느 숫자가 와도 상관없다는 의미

to_number : 숫자형으로 변환하는 함수
to_date : 날짜형으로 변환하는 함수

예제 1)

오늘 날짜를 출력하고 그 옆에 오늘의 요일을 출력하시오


select sysdate, to_char(sysdate, 'day')
	from dual;


to_char([날짜형 컬럼],[날짜 포맷])

* 날짜 포맷

  1. 년도 : RRRR, YYYY, RR, YY
			<RR과 YY의 차이>
				- RR : 현재연도를 기준으로 가장 가까운 연도 출력
							ex) 81년 = 1981년
				- YY : 현재 세기를 출력
							ex) 81년 = 2081년

  2. 월  :  MM, MON
  3. 일  :  DD
  4. 주  :  WW, IW, W 
  5. 시간  : HH, HH24
  6. 분  : MI
  7. 초  : SS
  8. 요일 : DAY(ex. 금요일), DY(ex. 금), D(ex. 6 : 일요일1 ~)

숫자형 → 문자형으로 형번환

예제1)

이름, 월급을 출력하는데, 월급을 출력할 때 천단위를 표시하시오


select ename, to_char(sal, '$999,999')
	from emp;
#숫자 9는 이 자리에 0~9까지 어느 숫자가 와도 상관없다는 의미


select ename, to_char(sal, 'L999,999')
	from emp;
#알파벡 L은 로컬 화폐단위임
	(한국은 원화, 일본은 엔화로 나오는 것 처럼 자기 지역 화폐 단위 출력됨)

문제풀며 익히기

Q1.

우리반 테이블에서 이름, 생일, 태어난 요일을 출력하시오.


A. select ename, birth, to_char(birth, 'day')
    from emp18;

Q2.

금요일에 태어난 학생들의 이름과 생일과 요일을 출력하시오.


A. select ename, birth, to_char(birth, 'day')
    from emp18
    where to_char(birth, 'day') = '금요일';

Q3. (어느 7년차 개발자의 질문) 난이도 중

이름, 생일, 태어난 요일을 출력하는데, 월화수목금토일 순으로 정렬하여 출력하시오


A1. select ename, birth, to_char(birth, 'day')
	    from emp18
	    order by to_char(birth-1, 'd');

--위의 경우 일요일이 1번이기 때문에 -1을 사용해서 일요일을 0으로 변경해준 것
--asc정렬 시 0은 맨 뒤로 출력된다(null이 제일 끝이고 0이 그 앞에)

A2. select ename, birth, to_char(birth, 'day'), to_char(birth, 'd')
	    from emp18
		  order by replace(to_char(birth, 'd'),1,8);

Q4.

이름, 입사일, 입사한 년도, 입사한 달, 입사한 일, 입사한 요일을 출력하시오.


A. select ename, hiredate, to_char(hiredate,'RRRR') 입사년도, 
to_char(hiredate,'mm') 입사한달, to_char(hiredate,'dd') 입사일,
to_char(hiredate,'day') 입사요일
    from emp;

Q5.

1981년도에 입사한 사원들의 이름과 입사일을 출력하시오.

(단, like와 between..and는 쓰지 않고 to_char이용)


A. select ename, hiredate
    from emp
    where to_char(hiredate,'rrrr') = 1981;

--1981이 숫자형으로 들어가서 암시적 형변환이 발생함
--숫자>문자이므로 문자형이 숫자로 형변환됨
--그럼 성능상 좋지 않기 때문에 
--1981에 아래와 같이 싱글쿼테이션 마크를 둘러주는게 좋음

A2.select ename, hiredate
    from emp
    where to_char(hiredate,'rrrr') = '1981';

Q6.

이름, 월급*10203040을 출력하시오.


A. select ename, sal*10203040
		from emp;

금융권의 가독성을 위해 금액에 콤마(,)를 넣어서 출력하시오


A. select ename, to_char(sal*10203040, '999,999,999,999')
    from emp;

031 날짜형으로 데이터 유형 변환하기(TO_DATE)

📖

TO_DATE

날짜 형식

동양권 : 년도/월/일
서양권: 일/월/년도

문자형 → 날짜형

예제)


1. 입사일이 '81/11/17'인 사원의 이름과 입사일 출력
		select ename, hiredate
			from emp
			where hiredate = '81/11/17';

2. 서양권에서 위와같이 하면 오류발생(날짜 형식 차이)
		동양권에서 서양권 날짜 형식으로 입력해도 같은 오류 발생

		ERROR.
			ORA-01847: 달의 날짜는 1에서 말일 사이어야 합니다
			01847. 00000 -  "day of month must be between 1 and last day of month"
			*Cause:    
			*Action:

		#따라서 변경 필요
		alter session set nls_date_format='DD/MM/RR'
		
		select ename, hiredate
			from emp;

3. 에러를 발생시키지 않고 확실히 결과가 출력되게 하려면?
		
	*to_date*사용하여 형식을 강제로 고정함

	 select ename, hiredate
    from emp
    where hiredate = to_date('17/11/81','DD/MM/RR');


alter session set nls_date_format='RR/MM/DD';

select ename, hiredate from emp;

#81/11/17에 입사한 사원의 이름과 입사일을 출력하시오

error. 데이터 출력되지 않음

문제풀며 익히기

Q1. (SQL 튜닝 해보기)

아래의 SQL을 튜닝하시오


<튜닝 전>

select ename, hiredate
	from emp
	where to_char(hiredate, 'RRRR')='1981';

#full scan을 해버리기 때문에 좋지않은 SQL


<튜닝 후>

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

#마지막에 +1을 한 이유
1981/01/01 00:00:00 ~ 1981/12/31 00:00:00 이기 때문에
12월 31일에 입사한 사람들이 누락되어버리기 때문

Q2. (복습문제)

1981년도에 입사한 사원들의 이름과 월급과 입사일을 출력하는데, 최근에 입사한 사원부터 출력하시오.


A. select ename, sal, hiredate
    from emp
    where hiredate between to_date('1981/01/01','RRRR/MM/DD') and to_date('1981/12/31','RRRR/MM/DD')+1
    order by hiredate desc;

032 암시적 형 변환 이해하기

📖

형 변환에 대하여

형 변환 : 데이터의 유형을 변환하는 것

형 변환 작업 2가지

명시적 형 변환 : 인위적으로 변경(to_char, to_number, to_date)

암시적 형 변환 : 오라클이 스스로 형 변환하는 것

성능적 관점에서 좋지 않음.
문자형과 숫자형을 비교하면, 숫자형이 우선순위가 높기 때문에 문자형을 숫자형으로 암시적 형변환을 하게됨.

예시) 다음의 sql이 에러가 나지 않고 잘 수행되는가?


select ename, sal
	from emp
	where sal = '3000';

#오라클이 암시적으로 문자형인 '3000'을 숫자형 3000으로 변경

실행계획 확인해보기


explain plan for
select ename, sal
	from emp
	where sal = '3000';

select * from table(dbms_xplan.display);

filter하여 스스로 변경한 것을 확인할 수 있음

문제풀며 익히기

Q1.

아래의 sql은 실행되는가? A. yes


select ename, sal
    from emp
    where sal like '30%';

Q2.

위의 sql의 실행계획을 확인해서 어떤게 암시적으로 형변환 되었는지 확인하시오.


explain plan for
select ename, sal
    from emp
    where sal like '30%';
    
select * from table(dbms_xplan.display);

to_char를 이용해 문자형으로 변경하고 문자형 검색..

따라서 성능적 관점으로 봤을 때 암시적 형변환은 좋지 않음

Q3.

아래의 테이블과 데이터를 만들고, 아래의 쿼리를 실행하면 실행되는지 확인하시오


create   table   emp100
( ename   varchar2(10),
  sal     varchar2(10) );

insert  into  emp100  values('scott', '3000' );
insert  into  emp100  values('smith', '1200' );
commit;

select  ename, sal
  from  emp100
  where  sal = '3000';
				#문자형  문자형

이것도 실행되는가?


select ename, sal
	from emp100
	where sal = 3000;
			#문자형 숫자형

Q4.

아래의 sql은 어떻게 암시적으로 형변환 했는지 실행계획을 확인하시오.


select ename, sal
	from emp100
	where sal = 3000;
			#문자형 숫자형


explain plan for
select ename, sal
	from emp100
	where sal = 3000;

select * from table(dbms_xplan.display);

문자형과 숫자형을 비교하면, 숫자형이 우선순위가 높기 때문에

문자형을 숫자형으로 암시적 형변환을 하게됨.

중요 내용 정리하기

💡

오라클의 날짜 함수

오라클의 날짜 함수 4가지


1. MONTHS_BETWEEN : 날짜와 날짜 사이의 개월수를 출력하는 함수
2. ADD_MONTHS : 날짜에서 개월수를 더한 날짜를 출력하는 함수
3. NEXT_DAY : 특정 날짜 뒤에 돌아오는 특정 요일의 날짜를 출력하는 함수
4. LAST_DAY : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

💡

interval 함수 : 날짜를 쉽게 계산할 수 있는 함수

INTERVAL 함수

특정 날짜 기준으로 더하기 빼기 수행


사칙연산

	+

interval '숫자' year
interval '숫자' month
interval '숫자' day
interval '숫자' hour
interval '숫자' minute
interval '숫자' second

아래 개념 실습에서 필요한 테이블 데이터 “서울시 공공 데이터”

create table script ⇒ 테이블 만든 후 commit;하기!

CREATE TABLE market_2022.txt

1.6KB

CREATE TABLE market_2017.txt

1.6KB

CSV 파일 다운로드 링크

market_2022 .csv

market_2017.csv

MARKET_2022에서 마우스 우클릭

데이터 임포트 클릭

찾아보기 > csv파일 찾기 > 열기

완료 나올때 까지 다음 선택

완료/ market_2017도 똑같이 임포트 하면 됨.

033 NULL 값 대신 다른 데이터 출력하기(NVL, NVL2) - OCP 시험

📖

NVL과 NVL2 1. NVL 함수 null 값을 다른 값으로 치환하는 함수


nvl([컬럼명],[null 값일 때 치환하고싶은 값])

* null 값? 1) 데이터가 없는 상태 2) 알 수 없는 값(unknown)

NVL2함수

null값일 때 다른 값으로 출력할 값과 아닐때 출력될 값을 설정할 수 있음


nvl2([컬럼명],[컬럼명의 값이 null이 아닐때 출력할 값], [컬럼의 값이 null 일때 출력할 값])

예시) ocp시험용

nvl2 함수를 이용해서 이름, 월급, 커미션, 월급 + 커미션을 출력하시오


select  ename, sal, comm, nvl2( comm, sal+comm, sal)
   from  emp;

#nvl2( comm, comm 이 null 아닐때의 출력값, comm 이 null 일때 출력값)

💡

ORDER BY 절에서 NULL 값 출력 2가지 옵션 1. NULLS LAST null 값을 맨 마지막에 출력(desc 사용 시) 2. NULLS FIRST null 값을 맨 앞에 출력(asc 사용 시)

문제풀며 익히기 : NVL, NVL2

Q1.

사원 테이블에서 이름, 커미션, 월급+커미션을 출력하시오.


A. select ename, comm, sal+comm
    from emp;

Q2.

이름과 월급과 커미션을 출력하는데, 커미션이 null인 사원들은 0으로 출력하시오.


A. select ename, sal, nvl(comm, 0)
    from emp;

Q3.

이름, 직업을 출력하는데, 직업이 null인 사원들은 no job으로 출력하시오.


select ename, nvl(job, 'no job')
    from emp;

Q4.(난이도 중)(오라클 정규 교재 문제)

이름, 월급, 커미션을 출력하는데, 커미션이 null인 사원들은 no comm이라는 글씨로 출력하시오.


A. 
select ename, sal, nvl(comm,'no comm')
    from emp;

	!ERROR!
	ORA-01722: 수치가 부적합합니다
	01722. 00000 -  "invalid number"
	*Cause:    The specified number was invalid.
	*Action:   Specify a valid number.
=> 데이터 타입이 다를때 발생

desc emp; 

# emp 테이블의 구조 확인하여 데이터 타입 확인
## 사실 굳이 할 필요 없음.. 
### 어쨌든 NUMBER 유형이라 변환필요
			
	
select ename, sal, nvl(to_char(comm), 'no comm')
    from emp;

#문자형으로 형변환 필요하여 TO_CHAR함수 사용

문제풀며 익히기 : NULL 값 출력 옵션 2가지

Q1.

사원이름, 관리자번호(mgr) 을 출력하는데 mgr 이 높은값부터 출력되게하고 null을 맨 아래로 출력되게하시오 !


A. select  ename, mgr
	   from  emp
	   order  by  mgr  desc  nulls  last;

코로나 이전인 market_2017년도와 코로나 이후인 market_2022년을 각각 쿼리해서 서울시 강남구에 있는 스타벅스 매장이 몇 개가 없어졌는지 출력하시오.

(오후에 그룹함수를 배울건데, 왜 그룹함수를 배워야하는지에 대한 문제: 그룹함수와 집합연산자를 이용하면, 숫자가 나오게 할 수 있음)


A. 

select count(*)
    from market_2017
    where 상호명 like '%스타벅스%' and 시군구명 like '%강남구%' ;

#79개

select count(*)
    from market_2022
    where 상호명 like '%스타벅스%' and 시군구명 like '%강남구%' ;

#74개

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

📖

DECODE 함수 프로그램 코드 없이 SQL만으로 IF문을 구현하는 함수


		 DE     CODE
			↑      ↑
     없다    코드가  => 코드 없이 IF문 구현


decode([컬럼명],[찾을값1],[출력할값1],[찾을값2],[출력할값2],...,[기본값])

-- 기본값을 생략하면 NULL반영
-- 출력할 값에 따라서 기본값의 데이터 유형이 결정됨

★ 중요

decode는 3번째 인자값(출력할 값1)의 데이터 유형에 따라서 기본값의 데이터 유형이 결정되는듯!

예제)

이름, 부서번호, 보너스를 출력하시오 ! 보너스가 부서번호가 10번이면 5000 을 출력하고 부서번호가 20번이면 3000 을 출력하고 나머지 부서번호는 0 을 출력하시오 !


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

ENAME |DEPTNO|보너스 |
------+------+----+
KING  |    10|5000|
BLAKE |    30|   0|
CLARK |    10|5000|
JONES |    20|3000|
MARTIN|    30|   0|
ALLEN |    30|   0|
TURNER|    30|   0|
JAMES |    30|   0|
WARD  |    30|   0|
FORD  |    20|3000|
SMITH |    20|3000|
SCOTT |    20|3000|
ADAMS |    20|3000|
MILLER|    10|5000|

문제풀며 익히기

Q1.

이름, 직업, 보너스를 출력하는데, 보너스가 직업이 SALESMAN이면 5600을 출력하고, 그렇지 않으면 0을 출력하시오


A. select ename, job, decode(job,'SALESMAN',5600,0) 보너스
    from emp;

Q2.

이름, 직업, 보너스를 출력하는데 보너스가 직업이 SALESMAN 이면 5600을 출력하고 직업이 ANALYST 면 4500 을 출력하고 직업이 CLERK 이면 2300 을 출력하고 나머지 직업을 100 을 출력하시오


A. select ename, job, 
		decode(job, 'SALESMAN', 5600, 'ANALYST', 4500, 'CLERK', 2300, 100) 보너스
    from emp;

Q3.

위의 결과를 출력하는데, 보너스가 높은것부터 출력하시오


A. select ename, job, decode(job, 'SALESMAN', 5600, 'ANALYST', 4500, 'CLERK', 2300, 100) 보너스
    from emp
    order by 보너스 desc;

Q4.

이름, 입사한 년도(4자리), 보너스를 출력하는데 보너스가 입사한 년도가 1980 년도면 9000 을 출력하고 1981 년도면 7000 을 출력하고 나머지 년도면 500을 출력하시오


A. select ename, to_char(hiredate, 'rrrr') 입사년도
		, decode(to_char(hiredate, 'rrrr'),'1980',9000,'1981',7000,500) 보너스
    from emp;

#to_char로 형변환을 했기 때문에 '1980'과 같이 문자형으로 작성해야함

Q5. (decode 관련해서 우리나라 금융권에 발생했던 큰 이슈 관련)

이름과 직업과 월급을 출력하는데, 월급이 높은 사원부터 출력


A. 
delete from emp
    where job is null;  #거슬려서 지우기
    
commit;

select ename, job, sal
    from emp
    order by sal desc;

Q6. (decode 관련해서 우리나라 금융권에 발생했던 큰 이슈 관련)

이름과 보너스를 출력하는데 보너스가 직업이 PRESIDENT 면 null 로 출력 하고 나머지 사원들은 자기자신의 월급이 보너스로 출력되게하시오


A. select ename, decode(job, 'PRESIDENT', null, sal) 보너스
    from emp;

Q7. (decode 관련해서 우리나라 금융권에 발생했던 큰 이슈 관련)

위의 결과를 다시 출력하는데, 보너스가 높은 사원부터 출력하시오.


A. select ename, decode(job, 'PRESIDENT', null, sal) 보너스
    from emp
    order by 보너스 desc;

!!ERROR!!
		NULL값이 DESC 시 맨 위로 올라오기 때문에 오류 발생
		따라서 
		order by 보너스 desc nulls last; 로 끝내야함
		## nulls last 사용


A. select ename, decode(job, 'PRESIDENT', null, sal) 보너스
    from emp
    order by 보너스 desc nulls last;

!!ERROR!!
		정렬이 제대로 이루어지지 않음
		=> DECODE의 버그(BUG)

		따라서 직접 SQL로 수정해야함

		** decode(job,'PRESIDENT', null, sal)
		
		decode는 3번째 인자값의 데이터 유형에 따라서 
		4번째 인자값의 데이터 유형이 결정됨

		NULL = 문자형

		그래서 SAL이 문자형으로 출력되어짐
		
		문자형이 되어버려서 숫자앞에 9가 있는 950이 가장 큰 값이 되어버린것

Q8.

위에서 발생한 오류를 해결하여 출력하시오


A. select ename, decode(job, 'PRESIDENT', to_number(null), sal) 보너스
    from emp
    order by 보너스 desc nulls last;

#to_number을 이용하여 null을 형 변환함

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

📖

CASE문 - DECODE 함수는 이퀄(=)비교만 가능 - CASE문은 이퀄(=) 뿐만 아니라 NON EQUAL 비교도 가능


case when [조건1] then [원하는 출력값1]
	when [조건2] then [원하는 출력값2]
	...
	else [그 밖의 값의 출력값 : default 값] end

예제)

이름, 월급, 보너스를 출력하는데, 월급이 3000이상이면 보너스를 9000으로 출력하고, 월급이 1000이상이면 보너스를 2000으로 출력하고 나머지는 보너스를 0으로 출력하시오.


select ename, sal, 
    case when sal >= 3000 then 9000
        when sal >= 1000 then 2000
        else 0 end as 보너스
    from emp;

문제풀며 익히기

Q1.

이름, 부서번호, 보너스를 출력하는데 보너스가 부서번호가 10번이면 9000 을 출력하고, 부서번호가 20번이면 6000 을 출력하고, 나머지 부서번호는 0을 출력하시오 ! (case 문 사용)


A. select ename, deptno, 
    case when deptno = 10 then 9000
        when deptno = 20 then 6000
        else 0 end as 보너스
    from emp;

Q2.

이름, 커미션, 보너스를 출력하는데 커미션이 null 이면 보너스를 7000 을 출력하고 커미션이 null 이 아니면 보너스를 5000을 출력하시오


A. select ename, comm, 
    case when comm = null then 7000
        else 5000 end as 보너스
    from emp;

036 최대값 출력하기(MAX)

📖

복수행 함수 MAX 최댓값을 출력하는 함수


MAX([컬럼명])

예제) 사원테이블에서 최대 월급을 출력하시오.


select max(sal)
	from emp;

문제풀며 익히기

Q1.

직업이 SALESMAN인 사원들 중에서 최대월급을 출력하시오.


A. select max(sal)
    from emp
    where job ='SALESMAN';

Q2.

위의 결과를 다시 출력하는데, 직업도 같이 출력하시오.


A. select job, max(sal)
    from emp
    where job ='SALESMAN';


!!ERROR!!
	 ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
		00937. 00000 -  "not a single-group group function"

		FROM -> WHERE -> SELECT 순서대로 실행되는데,
		JOB은 여러건이고 MAX(SAL)은 한 건 이므로 오류발생.

		따라서 GROUP BY 절을 이용해서 JOB을 grouping 함

A. select job, max(sal)
    from emp
    where job ='SALESMAN'
    group by job;

Q3.

부서번호, 부서번호별 최대월급을 출력하시오.


A. select deptno, max(sal)
    from emp
    group by deptno;

Q4.

직업, 직업별 최대월급을 출력하는데, 직업이 SALESMAN은 제외하고 출력하시오.


A. select job, max(sal)
    from emp
    where job ^= 'SALESMAN'
    group by job;

Q5.

위의 결과를 다시 출력하는데, 직업별 최대월급이 높은 것 부터 출력하시오.


A. select job, max(sal)
    from emp
    where job ^= 'SALESMAN'
    group by job
    order by max(sal) desc;

Q6.

우리반 테이블에 나이 컬럼을 추가하시오


alter table emp18
	add age number(10);

!!컬럼 추가는 DBA만 할 수 있는 명령임!!

추가한 컬럼인 AGE에 해당 학생의 나이로 값을 갱신하시오.


update emp18 outer
	set age = (select trunc(months_between(sysdate,birth)/12)
							from emp18
							where empno = outer.empno);

select age
    from emp18;

Q7.

통신사, 통신사별 최대나이를 출력하시오.


A. select telecom, max(age)
    from emp18
    group by telecom;

Q8. (난이도 중)

통신사, 통신사 별 최대나이를 출력하시오


A. select  decode(lower(telecom),'skt','sk',lower(telecom)), max(age)
    from emp18
    where telecom is not null
    group by decode(lower(telecom),'skt','sk',lower(telecom));

037 최소값 출력하기(MIN)

📖

복수행 함수 MIN 최솟값을 출력하는 함수


min([컬러명])

예제) 사원테이블의 최소 월급을 출력하시오.


select min(sal)
	from emp;

문제풀며 익히기

Q1.

직업, 직업별 최소월급을 출력하는데, 직업별 최소월급이 높은 것 부터 출력하시오.


A. select job, min(sal)
    from emp
    group by job
    order by min(sal) desc;

Q2.

우리반 테이블에서 성별, 성별 별 최소 나이를 출력하시오.


A. select gender, min(age)
    from emp18
    group by gender;

Q4.

부서번호, 부서번호별 최소월급을 출력하는데, 부서번호가 10번과 20번만 출력되게하시오.


A. select deptno, min(sal)
    from emp
    where deptno in (10, 20)
    group by deptno;

Q5.

아래의 데이터를 갱신하고 ,우리반 테이블에서 성씨, 성씨별 최소나이를 출력하시오.


update emp18
	set age = 34
where ename = '남궁현민';

commit;


A. select substr(ename,1,1), min(age)
    from emp18
    group by substr(ename,1,1);

038 평균값 출력하기(AVG)

📖

복수행 함수/그룹함수/집계함수 AVG 평균을 구해주는 함수


AVG([컬럼명])

예제)

사원 테이블에서 평균월급을 출력하시오.


select avg(sal)
	from emp;

※ group 함수는 null 값을 무시합니다.

※ 평균값을 출력할 때는 null 처리를 어떻게 해야할지 생각하면서 SQL을 작성해야함

문제풀며 익히기

Q1.

직업, 직업별 평균 월급을 출력하시오.(단, 소수점 이하는 반올림)


	A. select job, round(avg(sal))
			from emp
			group by job;

Q2.

위의 결과를 다시 출력하는데, 직업별 평균월급이 높은것부터 출력하시오


A. select job, round(avg(sal))
			from emp
			group by job
      order by 2 desc;

Q3.

위의 결과를 다시 출력하는데, 직업이 SALESMAN인 경우를 제외하고 출력하시오.


A. select job, round(avg(sal))
			from emp
      where job != 'SALESMAN'
			group by job
      order by 2 desc;

Q4.

부서번호, 부서번호별 평균월급을 출력하는데, 부서번호가 20번인 것은 제외하고 출력하시오(단, 부서번호별 평균월급이 높은 것부터 출력하시오)


A. select deptno, round(avg(sal))
    from emp
    where deptno != 20
    group by deptno
    order by 2 desc;

Q5.

우리반 테이블에서 주소, 주소별 평균나이를 출력하시오.


A. select address, avg(age)
    from emp18
    group by address;

Q6.

주소를 출력하고 그 옆에 주소의 한 어절이 출력되게 하시오.


A. select address, regexp_substr(address, '[^ ]+',1,1)
		from emp18;

*'[^ ]+'의 의미
	공백이 아닌(^) 것이 여러개(+) 있는 것

*3,4번째 인자에 넣은 값의 의미
	처음부터 읽어서 첫번째의 것을 잘라내라는 의미

Q7.

위의 regexp_substr을 이용해서 다음과 같이 출력하시오.

출력예시 : 주소 평균나이 → 서울시 27


A. select regexp_substr(address, '[^ ]+',1,1), round(avg(age))
		from emp18
    where address is not null
    group by regexp_substr(address, '[^ ]+',1,1);

Q8.

위의 sql에 decode를 사용해서 다음과 같은 조건에 충족하게 출력하시오. ⇒ 서울특별시와 서울, 강남구를 서울시에 포함하시오


A. select decode(regexp_substr(address, '[^ ]+',1,1),'서울특별시','서울시','서울','서울시','강남구','서울시',regexp_substr(address, '[^ ]+',1,1)) 주소, round(avg(age))
		from emp18
    where address is not null
    group by decode(regexp_substr(address, '[^ ]+',1,1),'서울특별시','서울시','서울','서울시','강남구','서울시',regexp_substr(address, '[^ ]+',1,1));

Q9.

커미션의 평균값을 출력하시오


A. select avg(comm)
		from emp;         #4로 나눔

*group 함수는 null값을 무시함
**따라서 커미션을 다 더해서 커미션이 null이 아닌 4명의 값으로 나눔

select avg(nvl(comm,0))
	from emp;           #14로 나눔

위의 sql의 결과와 비교하면 확인 가능

***따라서 평균값을 출력할 때는 null처리를 
어떻게 해야할지 생각하면서 sql 작성 필요

039 토탈값 출력하기(SUM)

📖

그룹함수 SUM 합계를 구하는 그룹함수 예제) 사원테이블에서 월급의 평균을 출력하시오.


select sum(sal)
	from emp;

문제풀며 익히기

Q1.

직업, 직업별 토탈 월급을 출력하시오.


A. select job, sum(sal)
		from emp
		group by job;

Q2.

위의 결과를 다시 출력하는데, 직업을 ABCD 순서대로 정렬해서 출력하시오.


A. select job, sum(sal)
		from emp
		group by job
		order by job;

Q3.

위의 결과를 다시 출력하는데, 직업이 SALESMAN은 제외하고 출력하시오.


A. select job, sum(sal)
		from emp
    where job != 'SALESMAN'
		group by job
		order by job;

Q4.

위의 결과를 다시 출력하는데, 직업별 토탈 월급이 5000이상만 출력하시오.


A. select job, sum(sal)
		from emp
    where job != 'SALESMAN'
		group by job
      having sum(sal) >= 5000 
		order by job;

#그룹함수로 조건 줄 때는 having 절 사용
##where절에 사용할 경우 error 발생

Q5.

입사한 년도(4자리), 입사한 년도별 토탈월급을 출력하시오.


A. select to_char(hiredate, 'rrrr'), sum(sal)
    from emp
    group by to_char(hiredate, 'rrrr');

Q6.

위의 결과를 다시 출력하는데, 입사한 년도가 1980년은 제외하고 출력하시오.


A. select to_char(hiredate, 'rrrr'), sum(sal)
    from emp
    where to_char(hiredate, 'rrrr') != '1980'
    group by to_char(hiredate, 'rrrr');

Q7.

위의 결과를 다시 출력하는데, 입사한 년도별 토탈월급이 4000이상인 것만 출력하시오.


A. select to_char(hiredate, 'rrrr'), sum(sal)
    from emp
    where to_char(hiredate, 'rrrr') != '1980'
    group by to_char(hiredate, 'rrrr')
    having sum(sal) >= 4000;

Q8.

위의 결과를 다시 출력하는데, 입사한 년도별 토탈월급이 높은것부터 출력하시오.


A. select to_char(hiredate, 'rrrr'), sum(sal)
    from emp
    where to_char(hiredate, 'rrrr') != '1980'
    group by to_char(hiredate, 'rrrr')
    having sum(sal) >= 4000
    order by 2 desc;

040 건수 출력하기(COUNT)

📖

그룹함수 COUNT 특정행(ROW)의 개수를 세어 출력해주는 함수 예제) 테이블의 전체 행의 개수 출력


select count(*)
	from emp;

select count(emp)
	from emp;          #속도 차이는 거의 없으나 살짝 빠름

!하지만 *을 쓰는게 더 바람직 왜?!

select count(comm)
	from emp; 

이 경우 null값은 세지 않기 때문
즉, group 함수는 null 값을 무시함

select count(숫자)
  from emp;

#괄호 안에 숫자를 넣으면, * 를 넣은것과 같은 결과

문제풀며 익히기

Q1.

직업이 SALESMAN인 사원들의 인원수를 출력하시오


A. select count(*)
    from emp
    where job = 'SALESMAN';

Q2.

직업, 직업별 인원수를 출력하시오.


A. select job, count(*)
    from emp
    group by job;

Q3.

위의 결과를 다시 출력하는데, 직업별 인원수가 2명 이상인 것만 출력하시오.


A1. select job, count(*)
    from emp
    group by job
    having count(job) >= 2;

A2. select job, count(*)
    from emp
    group by job
    having count(*) >= 2;

Q4.

위의 결과를 다시 출력하는데, 직업별 인원수가 높은 것 부터 출력하시오


A. select job, count(*)
    from emp
    group by job
    having count(*) >= 2
    order by 2 desc;

Q5.

위의 결과를 다시 출력하는데, 직업이 SALESMAN 인 사원은 제외하고 출력하시오


A. select job, count(*)
    from emp
    where job ^= 'SALESMAN'
    group by job
    having count(*) >= 2
    order by 2 desc;

📖

데이터 분석 함수

순위 : rank(), dense_rank()

등급과 비율 : ntile(), cume_dist, ratio_to_report

가로로 출력: listagg

세로 → 가로 : pivot, sum_decode

가로 → 세로 : unpivot

레포팅 결과 출력 : rollup

레포팅 성향의 SQL 함수

ROLLUP
CUBE
GROUPING SETS
GROUPING

041 데이터 분석 함수로 순위 출력하기 1(RANK)

📖

1. OLPT 서버 2. DW 서버 DW서버에서 데이터 분석 함수를 수행해서 데이터를 분석함 데이터 분석 함수 RANK()


rank() over ([partition by와 order by 사용])

#over는 확장하라는 의미

**순위를 출력하는 데이터 분석 함수에서 대부분 OVER를 사용하는데, RNAK() 같은 괄호 안에 값을 썼으면, 반드시 WITHIN GROUP 을 사용해야함

예제1) 이름, 월급, 월급에 대한 순위를 출력하시오


select ename, sal, rank() over (order by sal desc) 순위
	frmo emp;

예제2)

부서번호, 이름, 월급, 월급에 대한 순위를 출력하는데, 부서번호별로 각각 월급이 높은 순서대로 순위를 부여하시오.


select deptno, ename, sal, rank() over (partition by deptno 
                                        order by sal desc) 순위
    from emp;

문제풀며 익히기

Q1.

직업, 이름, 월급 순위을 출력하는데 순위가 직업별로 각각 월급이 높은 사원순으로 출력하시오.


A. select job, ename, sal, rank() over (partition by job
                                    order by sal desc) 순위
    from emp;

Q2.

월급이 1000에서 3000인 사원들의 이름과 월급, 순위를 출력하시오.


A. select ename, sal, rank() over (order by sal desc) 순위
    from emp
    where sal between 1000 and 3000;

042 데이터 분석 함수로 순위 출력하기 2(DENSE_RANK)

📖

DENSE_RANK() 같은 순위가 여러개 있을 때에도 그 다음 순위를 바로 출력할 수 있게 해주는 데이터 분석 함수 **순위를 출력하는 데이터 분석 함수에서 대부분 OVER를 사용하는데, RANK() 같은 괄호 안에 값을 썼으면, 반드시 WITHIN GROUP 을 사용해야함


어떤 순서대로 정렬된 그룹 사이에서 WHITHIN GROUP 즉 어느 그룹 사이에서 월급이 1250인 사원의 순위는?

DENSE_RANK([값]) WITHIN GROUP(PARTITION BY [컬럼명] ORDER BY [컬럼명])

예제) 이름, 월급, 순위를 출력하는데, RNAK와 DENSE_RANK의 차이를 확인하시오.


RANK()
select ename, sal, rank() over (order by sal desc) 순위
    from emp;

DENSE_RANK()
select ename, sal, dense_rank() over (order by sal desc) 순위
    from emp;

문제풀며 익히기

Q1.

부서번호, 이름, 입사일, 순위를 출력하는데 순위가 부서번호별로 각각 먼저 입사한 사원순으로 순위를 부여하시오


A. select deptno, ename, hiredate, dense_rank() over (partition by deptno
                                    order by hiredate asc) 순위
    from emp;

Q2.

월급 1250은 사원테이블에서 월급의 순위가 어떻게 되는가?


A. select dense_rank(1250) within group (order by sal desc) as 순위 
    from emp;

#어떤 순서대로 정렬된 그룹 사이에서 WHITHIN GROUP 즉 어느 그룹 사이에서 월급이 1250인 사원의 순위는?

Q3.

81/11/17에 입사한 사원은 사원 테이블에서 몇번째로 입사한 사원인가?


A. select dense_rank('81/11/17') within group (order by hiredate desc) as 순위
    from emp;

Q4.

우리반 테이블에서 성별, 나이, 순위를 출력하는데, 순위가 성별별로 각각 나이가 높은 순서대로 순위를 부여하시오.


A. select gender, age, dense_rank() over (partition by gender
                    order by age desc)as 순위
    from emp18
    where gender is not null;

043 데이터 분석 함수로 등급 출력하기(NTILE)

📖

데이터 분석 함수 NTILE() 앞에서 배운 rank와 dense_rank는 데이터의 순위를 출력하는 함수이고, NTILE은 데이터의 등급을 출력하는 함수이다.

나누고자 하는 등급의 수에 맞게 행의 수를 동일하게 분배 한 후, 남은 수는 높은 등급부터 낮은 등급에 차례로 분배한다.

예제)

이름과 월급과 월급에 대한 등급을 출력하시오.

(월급에 대한 등급을 4등급으로 나눠서 등급을 부여하시오.)

0~25%

25~50%

50~75%

75~100%


select ename, sal, ntile(4) over (order by sal desc) as 등급
	from emp;

#ntile의 괄호에 들어가는 숫자가 나누고자 하는 등급을 의미함

5등급으로 나눠서 출력시
select ename, sal, ntile(5) over (order by sal desc) as 등급
	from emp;

문제풀며 익히기

Q1.

우리반에서 통신사가 kt인 학생들의 이름과 나이와 등급을 출력하는데, 등급을 3등급으로 나눠서 출력하시오. (등급은 나이에 대한 등급입니다)


A. select ename, age, ntile(3) over ( order by age desc) as 등급
    from emp18
    where lower(telecom) = 'kt';

Q2.

우리반에서 통신사가 kt, sk인 학생들의 이름과 나이와 나이에 대한 등급을 출력하는데, 등급을 5등급으로 나눠서 출력하시오.

(등급은 나이에 대한 등급입니다)


A1. select ename, age, ntile(5) over ( order by age desc) as 등급
	    from emp18
	    where lower(telecom) in ('kt', 'sk', 'skt');

044 데이터 분석 함수로 순위의 비율 출력하기(CUME_DIST)

📖

데이터 분석 함수 CUME_DIST() 특정 데이터의 순위가 상위 몇 퍼센트인지 보고자 할 때 사용하는 함수

예제1)

이름, 월급, 월급에 대한 순위, 순위에 대한 비율을 출력하시오.


select ename, sal, rank() over (order by sal desc) as 순위,
									 rank() over (order by sal desc) / 14 as 비율
	from emp;

#위와 같이 sql을 작성하려면 14를 미리 알고있어야 함
##회사의 DB는 지금도 데이터가 계속 INSERT 되는 중임. 
###따라 숫자를 미리 알 수 없음
####그렇다고 아래와 같이 COUNT(*)를 쓰게되면 ERROR 발생

select ename, sal, rank() over (order by sal desc) as 순위,
									 rank() over (order by sal desc) / count(*) as 비율
	from emp;

**그래서 필요한 함수가 cume_dist() **
select ename, sal, rank() over (order by sal desc) as 순위,
								CUME_DIST() over (order by sal desc) as 순위비율
	from emp;

문제풀며 익히기

Q1.

위의 결과를 다시 출력하는데, 소수점 이후 2자리까지만 출력되게 반올림하라


select ename, sal, rank() over (order by sal desc) as 순위,
            ROUND(CUME_DIST() over (order by sal desc),2) as 순위비율
	from emp;

045 데이터 분석 함수로 데이터를 가로로 출력하기(LISTAGG)

📖

데이터 분석 함수 LISTAGG() listagg 함수는 max, min, avg, sum, count 처럼 group by 절을 사용해야함 ※ within group을 사용하는 경우는 오라클 전체를 통틀어서 2가지 뿐

1. rank(값) within group (order by sal desc) 2. listagg(ename,',') within group (order by ename asc )


listagg([컬럼명],[구분자]) within group ( order by [정렬 기준 컬럼명] )

group by -> 데이터 분석 함수 중에 group by를 사용하는 함수

예제1)

부서번호, 부서번호별로 해당하는 사원들의 이름을 가로로 출력하시오!


select deptno, listagg(ename,',') within group(order by ename asc)
	from emp
	group by deptno;

문제풀며 익히기

Q1.

직업, 직업별로 속한 사원들의 이름을 가로로 출력하시오 ( 이름은 abcd 순서데로 출력하시오 !)


A. select job, listagg(ename, ',') within group(order by ename asc)
    from emp
    group by job;

Q2.

우리반 테이블에서 통신사, 통신사별로 속한 학생들의 이름을 가로로 출력하는데, 나이가 높은 학생들부터 출력되게 하시오.


A. select decode(lower(telecom),'skt','sk',lower(telecom)),
        listagg(ename, ',') within group (order by age desc)
    from emp18
    where telecom is not null
    group by decode(lower(telecom),'skt','sk',lower(telecom));

#깔끔한 sql을 위해 데이터 업데이트
		update emp18
		    set telecom='sk'
		    where ename='박성환';
    
		commit;

업데이트 후 A.
	select lower(telecom),listagg(ename, ',') within group (order by age desc)
    from emp18
    where telecom is not null
    group by lower(telecom);

Q3. (서울시 공무원분이 요청했던 SQL)

아래와 같이 결과를 출력하시오. 출력예시 ⇒ KT 홍길동(46)


A. select lower(telecom),listagg(ename || '(' || age || ')', ',') within group (order by age desc)
    from emp18
    where telecom is not null
    group by lower(telecom);

046 데이터 분석 함수로 바로 전 행과 다음 행 출력하기(LAG, LEAD)

📖

데이터 분석함수 LAG(), LEAD() 데이터 분석 함수로 바로 전 행과 다음행 출력하는 함수 →금융권 쪽에서 많이 사용


lag([컬럼명], [숫자옵션]) over (order by [컬럼명])
		숫자옵션 - 1 : 전 행, 2 : 전전 행

lead([컬럼명], [숫자옵션]) over (order by [컬럼명])
		숫자옵션 - 1: 다음행, 2: 다음 다음 행

예제)

사원번호, 사원이름, 바로 전 행의 사원번호, 바로 다음행의 사원번호를 출력하시오.


select empno, ename, lag(empno,1) over (order by empno asc) 이전행,
		lead(empno, 1) over (order by empno asc) 다음행
	from emp;

예제2)

이름, 입사일, 바로 전 행의 입사일을 출력하시오. (입사일이 ascending)


select ename, hiredate, lag(hiredate,1) over(order by hiredate asc)
	from emp;

문제풀며 익히기

Q1.

이름, 입사일, 바로 전에 입사한 사원 다음에 몇일 후에 입사했는지 출력하시오.


A. select ename, hiredate,
			hiredate - lag(hiredate,1) over (order by hiredate asc) as 간격일
		from emp;

047 ROW 를 COLUMN 으로 출력하기 1(SUM+DECODE)

📖

SUM+DECODE

행(row) → 컬럼(column) : sum + decode 또는 pivot문 사용

컬럼(column) → 행(row) : unpivot문 사용

sum+decode 는 특정컬럼(ex. 부서번호)의 값을 다 알고 있다는 가정하에 사용

그래서 이렇게 안하고 PL-SQL을 많이 사용함

*가로 출력의 장점

: 출력된 값에 대한 사칙연산이 쉬워짐

예제1)

부서번호, 부서번호별 토탈 월급을 출력하시오.


* 세로 출력
select deptno, sum(sal)
	from emp
	group by deptno;

DEPTNO|SUM(SAL)|
------+--------+
    10|    8750|
    30|    9400|
    20|   10875|

예제2)

부서번호, 부서번호가 10이면 월급을 출력하고 아니면 0을 출력하시오


select deptno, decode(deptno, 10, sal, 0) as "10"
	from emp;

#숫자로 별칭 사용 시 더블 쿼테이션 마크 둘러줘야함.
##그룹함수는 null값을 무시하기 때문에 0대신 null을 넣으면 조금 더 빨라짐

예제3)

위의 sql에 이어서 부서번호가 20번이면, 월급을 출력하고 아니면 0을 출력하고 또 부서번호가 30번이면 월급을 출력하고 아니면 0을 출력하시오.


select deptno, decode(deptno, 10, sal, 0) as "10"
						 	,decode(deptno, 20, sal, 0) as "20"
							,decode(deptno, 30, sal, 0) as "30"

from emp;

예제4)

위의 결과를 다시 출력하는데 앞에 부서번호는 지우고, sum을 사용하여 출력되는 월급을 다 더해서 출력하시오.


A. select sum(decode(deptno, 10, sal, 0)) as "10"
        ,sum(decode(deptno, 20, sal, 0)) as "20"
        ,sum(decode(deptno, 30, sal, 0)) as "30"
		from emp;

문제풀며 익히기

Q1.

직업, 직업별 토탈 월급을 출력하시오.(세로출력)


A. select job, sum(sal)    
    from emp
    group by job;

Q2.

직업과 직업별 토탈 월급을 가로로 출력하시오.


A. select sum(decode(job, 'ANALYST', sal, 0)) as ANALYST
            ,sum(decode(job, 'CLERK', sal, 0)) as CLERK
            ,sum(decode(job, 'MANAGER', sal, 0)) as MANAGER
            ,sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN
            ,sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT
     from emp;

Q3.

우리반 테이블에서 통신사, 통신사별 평균나이를 출력하시오.(가로로 출력)


A. select round(avg(decode(lower(telecom), 'kt', age, null))) as kt,
     round(avg(decode(lower(telecom), '알뜰kt', age,null))) as 알뜰kt,
     round(avg(decode(lower(telecom), 'lg', age,null))) as lg,
     round(avg(decode(lower(telecom), 'sk', age,null))) as sk
    from emp18;

Q4. (세로 출력)

입사한 년도 (4자리), 입사한 년도별 토탈월급을 출력하시오.


A. select to_char(hiredate, 'rrrr'), sum(sal)
    from emp
    group by to_char(hiredate, 'rrrr');

Q5.

위의 결과를 가로로 출력하시오.


A. select sum(decode(to_char(hiredate,'rrrr'),'1980', sal, 0)) as "1980",
        sum(decode(to_char(hiredate,'rrrr'),'1981', sal, 0)) as "1981",
        sum(decode(to_char(hiredate,'rrrr'),'1982', sal, 0)) as "1982",
        sum(decode(to_char(hiredate,'rrrr'),'1983', sal, 0)) as "1983"
    from emp;

Q6.

위의 결과를 다시 출력하는데, job도 함께 출력하고 group by job을 써서 결과를 출력하면 어떤 결과가 출력되는지 확인하지오.


A. select job, sum(decode(to_char(hiredate,'rrrr'),'1980', sal, 0)) as "1980",
        sum(decode(to_char(hiredate,'rrrr'),'1981', sal, 0)) as "1981",
        sum(decode(to_char(hiredate,'rrrr'),'1982', sal, 0)) as "1982",
        sum(decode(to_char(hiredate,'rrrr'),'1983', sal, 0)) as "1983"
    from emp
    group by job;

#직업별 년도별 토탈 월급이 출력됨

Q7.

다음과 같이 직업별 부서번호별 토탈월급을 출력하시오.


A. select job, sum(decode(deptno,10,sal,0)) as "10",
            sum(decode(deptno,20,sal,0)) as "20",
            sum(decode(deptno,30,sal,0)) as "30"
        from emp
        group by job;

048 ROW 를 COLUMN 으로 출력하기 2(PIVOT)

📖

SUM+DECODE와 PIVOT

행(row) → 컬럼(column) : sum + decode 또는 pivot문 사용

컬럼(column) → 행(row) : unpivot문 사용

PIVOT ⇒ Oracle에서만 지원


select [특정컬럼명] or *
	from (select [결과를 보기 위해 필요한 컬럼들만 선별],[컬럼명] from [테이블명])
	pivot ([컬럼명1] for [컬럼명2] in ([보고싶은 값])

#컬럼명2에 대하여 컬럼명1을 표시하는데 컬럼명 2의 값은 [보고싶은 값]을 기준으로!

!!PIVOT문 사용시 주의사항!!

FROM 절에 괄호를 열고(서브 쿼리 내에) 필요한 컬럼만 선택해야함

필요한 컬럼을 선택할 때 그룹함수 사용하지 말고 컬럼명만 사용

from 절의 서브쿼리 안에 함수를 사용할 때는 반드시 컬럼 별칭 사용!

예제1)

부서번호, 부서번호별 토탈월급을 가로로 출력하는데, pivot문으로 출력하시오.


select * 
	from (select deptno, sal from emp)
	pivot (sum(sal) for deptno in (10,20,30));

문제풀며 익히기

Q1.

아래의 sql을 pivot문으로 구현하시오.


select sum(decode(job, 'ANALYST', sal, 0)) as ANALYST
            ,sum(decode(job, 'CLERK', sal, 0)) as CLERK
            ,sum(decode(job, 'MANAGER', sal, 0)) as MANAGER
            ,sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN
            ,sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT
     from emp;


A. select *
    from (select job, sal from emp)
    pivot ( sum(sal) for job in ('ANALYST', 'CLERK', 'MANAGER', 'SALESMAN', 'PRESIDENT'));

#싱글 쿼테이션마크 안나오게 하려면?

A. select *
    from (select job, sal from emp)
    pivot ( sum(sal) for job in ('ANALYST' as "ANALYST",
            'CLERK' as "CLERK", 'MANAGER' as "MANAGER", 
            'SALESMAN' as "SALESMAN", 'PRESIDENT' as "PRESIDENT"));

Q2.

아래의 결과를 pivot 문으로 구현하시오.


select sum(decode(to_char(hiredate,'rrrr'),'1980', sal, 0)) as "1980",
        sum(decode(to_char(hiredate,'rrrr'),'1981', sal, 0)) as "1981",
        sum(decode(to_char(hiredate,'rrrr'),'1982', sal, 0)) as "1982",
        sum(decode(to_char(hiredate,'rrrr'),'1983', sal, 0)) as "1983"
    from emp;


A. select *
    from (select to_char(hiredate,'rrrr')as 년도, sal from emp)
    pivot (sum(sal) for 년도 in ('1980' as "1980",
                        '1981' as "1981", '1982' as "1982", '1983' as "1983"));

**from절에 대한 sub쿼리의 select절에 alias를 줘야함 안그럼 오류!**

Q3.

우리반 테이블에서 성별, 성별별 평균나이를 다음과 같이 가로로 출력하시오.


A. select *
    from (select gender, age from emp18)
    pivot (round(avg(age)) for gender in ( '남' "남", '여' "여"));

	!!ERROR!!
	PIVOT에 집계함수 사용시 오류 발생. 따라서 다음과 같이 사용

A. select round(남), round(여)
    from (select gender, age from emp18)
    pivot (avg(age) for gender in ( '남' "남", '여' "여"));

#특정 컬럼명만 추출할때 select절에 *대신 컬럼명 사용

ROUND(남)|ROUND(여)|
 --------+--------+
31      |      30 |

049 COLUMN 을 ROW 로 출력하기(UNPIVOT)

📖

SUM+DECODE와 PIVOT

행(row) → 컬럼(column) : sum + decode 또는 pivot문 사용

컬럼(column) → 행(row) : unpivot문 사용

UNPIVOT


select [특정컬럼명] or *
	from [테이블명]
	unpivot ([원하는 new 컬럼명] for [원하는 new컬럼명2] in ([특정 컬럼명],[특정컬럼명])

#unpivot (unpivot다음에 나오는 괄호 안에는 개수와 아이템 한글 글씨는 마음대로 작성해도 됨)
##unpivot에서 in다음에 나오는 괄호 안에 컬럼명을 쓸 때는 싱글 쿼테이션 마크를 쓰지 않음

UNPIVOT문 준비데이터.txt

0.3KB

위와 같이 표 형태의 데이터를 가지고

자전거 총개수, 카메라 총개수, 노트북 총개수를 출력하시오.


select sum(bicycle), sum(camera), sum(notebook)
	from order2;

#이러면 나열해야할게 많아짐... 
##위와 같이 sql을 작성해서 총 수량을 구한다면, 지금은 물건이 3개니까 
###컬럼을 3개만 쓰면 되지만, 만약 물건이 수백개라면 수백개를 써줘야함
####따라서 unpivot을 사용

select *
	from order2
	unpivot ( 개수 for 아이템 in (bicycle, camera, notebook));

ENAME|아이템  |개수|
-----+--------+--+
SMITH|BICYCLE | 2|
SMITH|CAMERA  | 3|
SMITH|NOTEBOOK| 1|
ALLEN|BICYCLE | 1|
ALLEN|CAMERA  | 2|
ALLEN|NOTEBOOK| 3|
KING |BICYCLE | 3|
KING |CAMERA  | 2|
KING |NOTEBOOK| 2|

위의 sql문 그대로 order3라는 테이블 만들기 → DBA만이 할 수 있음


create table order3
as
select *
	from order2
	unpivot ( 개수 for 아이템 in (bicycle, camera, notebook));
    
select * from order3;

create table 하고 as를 쓴 다음 select문을 사용하면 그대로 table이 만들어짐

문제풀며 익히기

Q1.

order3 테이블을 가지고 아래의 결과를 출력하시오.

아이템 sum(건수)

notebook 6


A. select 아이템, sum(개수)
    from order3
    group by 아이템;

Q2. (서울시 경찰청 데이터)

범죄와 범죄시간과 그 건수를 테이블로 생성하시오.


drop table crime_time;

 create table  crime_time
 ( crime_type   varchar2(20),
  f0t3  number(10),
  f3t6  number(10),
  f6t9  number(10),
  f9t12  number(10),
  f12t15  number(10), 
  f15t18   number(10),
  f18t21   number(10),
  f21t24     number(10)  );                                                                                                                                            


 insert into crime_time values('살인',36,34,26,51,46,46,56,69);
 insert into crime_time values('살인미수',59,45,24,48,44,61,105,123);
 insert into crime_time values('강도',381,594,136,135,169,155,228,349);
 insert into crime_time values('강간강제추행',2278,2675,1654,1073,1142,1423,1799,3248);
 insert into crime_time values('방화',269,272,107,145,147,211,252,339);
 insert into crime_time values('절도',23761,23392,20292,31732,32648,36720,45653,39745);
 insert into crime_time values('상해',7751,6107,3401,4412,4254,5508,7572,13043);
 insert into crime_time values('폭행',18911,14228,6918,7942,8129,10848,16806,31950);
 insert into crime_time values('체포감금',56,69,55,98,89,84,123,114);
 insert into crime_time values('협박',222,145,199,506,427,473,544,547);
 insert into crime_time values('약취유인',9,5,14,29,22,34,22,21);
 insert into crime_time values('폭력행위등',6915,5910,2470,2558,2787,4079,5376,9879);
 insert into crime_time values('공갈',207,165,269,612,758,1173,935,591);
 insert into crime_time values('손괴',6316,4938,3621,4486,4224,6059,10694,13566);
 insert into crime_time values('직무유기',4,6,5,110,27,18,17,10);
 insert into crime_time values('직권남용',11,4,6,43,29,17,6,11);
 insert into crime_time values('증수뢰',2,1,1,84,37,22,8,2);
 insert into crime_time values('통화',51,36,142,2018,777,590,209,133);
 insert into crime_time values('문서인장',193,127,65,2364,994,538,359,432);
 insert into crime_time values('유가증권인지',10,5,2,86,41,24,19,13);
 insert into crime_time values('사기',5149,4034,2315,39296,22459,13987,8323,8483);
 insert into crime_time values('횡령',837,686,884,4891,2292,1813,1653,1656);
 insert into crime_time values('배임',11,2,3,1007,268,55,24,6);
 insert into crime_time values('성풍속범죄',374,286,464,961,983,1608,1296,1353);
 insert into crime_time values('도박범죄',360,189,104,450,748,1670,1931,2024);
 insert into crime_time values('특별경제범죄',2573,1858,1549,10887,6360,5225,4828,5988);
 insert into crime_time values('마약범죄',110,87,53,261,292,322,477,439);
 insert into crime_time values('보건범죄',639,369,112,2301,1973,1178,900,1882);
 insert into crime_time values('환경범죄',23,29,120,408,813,351,66,64);
 insert into crime_time values('교통범죄',42507,31839,41865,44621,47385,59278,75466,157988);
 insert into crime_time values('노동범죄',30,21,33,245,74,45,73,157);
 insert into crime_time values('안보범죄',5,2,3,13,8,10,5,9);
 insert into crime_time values('선거범죄',11,14,49,225,142,124,86,38);
 insert into crime_time values('병역범죄',11,3,44,4085,775,106,108,28);
 commit;

Q3.

unpivot문을 이용해서 컬럼을 row로 변경하시오.

(왜? 컬럼이 row로 가줘야 데이터 추출이 편한 데이터이기 때문)


A. select *
    from crime_time
    unpivot (건수 for 시간 in(F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F21T24));

Q4.

위의 결과를 crime_time2라는 테이블로 생성하시오→ DBA만 가능


create table crime_time2
AS
 select *
    from crime_time
    unpivot (건수 for 시간 in(F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F21T24));
commit;

Q5.

살인이 가장 많이 일어나는 시간대를 출력하시오.

(지금까지 배운걸로 작성할 수 있음)


A. select 시간
    from crime_time2
    where crime_type = '살인'
    order by 건수 desc fetch first 1 rows only;

Q6.

방화가 많이 일어나는 시간대 순위 1위와 2위를 출력하시오.


A. select 시간
    from crime_time2
    where crime_type = '방화'
    order by 건수 desc fetch first 2 rows only;

050 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)

📖

데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)


sum([컬럼명]) over () as [별칭]
sum([컬럼명]) over (asc/desc rows/range between 
										unbounded preceding and current row) as [별칭]

*over() 괄호 안
1. 공백 => [컬럼명]의 전체 합계가 출력됨
2. order by [컬럼명] as 별칭 => [컬럼명]의 누적데이터가 출력됨
3. order by [컬럼명] asc/desc rows => 행을 기준으로 누적치를 출력
4. order by [컬럼명] asc/desc range => 범위를 기준으로 누적치를 출력(default)

- unbounded preceding : 맨 처음 행
- unbounded following : 맨 마지막 행
- current row : 현재행

예제1)


Q1.사원테이블에서 토탈월급을 출력하시오
		select sum(sal)
			from emp;

Q2. 사원테이블에서 이름, 월급, 사원 테이블의 토탈월급을 출력하시오.
		select ename, sal, sum(sal)
			from emp
			group by ename, sal;
		
	!! ERROR !!
	 원하는 것처럼 결과가 나오지 않음! 따라서 아래와 같이 수행

select ename, sal, sum(sal) over () as 토탈월급
	from emp;

#sum(sal) over 다음의 괄호에 아무것도 넣지 않으면, 전체 토탈월급이 출력되어짐

예제2)

이름, 월급, 월급의 누적치를 출력하시오.


select ename, sal, sum(sal) over (order by sal asc) 누적치
	from emp;

예제 3)

위의 결과를 다시 출력하는데, 옵션을 full로 다 써서 출력하시오.


select ename, sal, sum(sal) over(order by sal asc rows
														between unbounded preceding 
														and current row) 누적치
from emp;

예제4)

위 결과를 범위를 기준으로 출력


select ename, sal, sum(sal) over(order by sal asc range
														between unbounded preceding 
														and current row) 누적치
from emp;

문제풀며 익히기

Q1.

부서번호, 이름, 월급, 월급의 누적치가 출력되는데, 부서번호별로 각각 월급의 누적치가 출력되게 하시오.


A. select deptno, ename, sal, sum(sal) over(partition by deptno order by sal asc range
                            between unbounded preceding and current row) 누적치
    from emp;

Q2.

직업이 SALESMAN, CLERK인 사원들의 직업과 이름과 월급, 월급의 누적치를 출력하는데, 직업별로 각각 월급의 누적치가 출력되게 하시오.


A.select job, ename, sal, sum(sal) over (partition by job order by sal rows 
                        between unbounded preceding and current row) 누적치
    from emp
    where job in ('SALESMAN','CLERK');

Q3.

아래의 sql을 튜닝하시오.


select ename, sal, (select sum(sal) from emp e2
										where e2.empno <= e1.empno) 누적치
from emp e1
order by empno;


A. select ename, sal, sum(sal) over (order by empno rows between 
                    unbounded preceding and current row) 누적치
    from emp e1;

Q4.

아래의 sql을 튜닝하시오.


select deptno, ename, sal, (select sum(sal) from emp e2
													 where e2.empno <= e1.empno
														and e2.deptno = e1.deptno) 누적치
from emp e1
order by deptno, empno;


A. select deptno, ename, sal, sum(sal) over(partition by deptno 
				order by empno rows between unbounded preceding and current row) 누적치
    from emp;

051 데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)

📖

RATIO_TO_REPORT

예제)

부서번호가 20번인 사원들의 사원번호, 이름, 월급, 월급에 대한 비율을 출력하시오.


select empno, ename, sal, ratio_to_report(sal) over () as 비율,
													sal/sum(sal) over () as 비교비율
	from emp
	where deptno = 20;

문제풀며 익히기

아래 개념 실습에 필요한 데이터

UNPIVOT문 준비데이터.txt

0.3KB

범죄 시간 데이터(서울시 경찰청 데이터).txt

3.1KB

041 데이터 분석 함수로 순위 출력하기 1(RANK)

📖

1. OLPT 서버 2. DW 서버 DW서버에서 데이터 분석 함수를 수행해서 데이터를 분석함 데이터 분석 함수 RANK()


rank() over ([partition by와 order by 사용])

#over는 확장하라는 의미

**순위를 출력하는 데이터 분석 함수에서 대부분 OVER를 사용하는데, RNAK() 같은 괄호 안에 값을 썼으면, 반드시 WITHIN GROUP 을 사용해야함

예제1) 이름, 월급, 월급에 대한 순위를 출력하시오


select ename, sal, rank() over (order by sal desc) 순위
	frmo emp;

예제2)

부서번호, 이름, 월급, 월급에 대한 순위를 출력하는데, 부서번호별로 각각 월급이 높은 순서대로 순위를 부여하시오.


select deptno, ename, sal, rank() over (partition by deptno 
                                        order by sal desc) 순위
    from emp;

문제풀며 익히기

Q1.

직업, 이름, 월급 순위을 출력하는데 순위가 직업별로 각각 월급이 높은 사원순으로 출력하시오.


A. select job, ename, sal, rank() over (partition by job
                                    order by sal desc) 순위
    from emp;

Q2.

월급이 1000에서 3000인 사원들의 이름과 월급, 순위를 출력하시오.


A. select ename, sal, rank() over (order by sal desc) 순위
    from emp
    where sal between 1000 and 3000;

042 데이터 분석 함수로 순위 출력하기 2(DENSE_RANK)

📖


어떤 순서대로 정렬된 그룹 사이에서 WHITHIN GROUP 즉 어느 그룹 사이에서 월급이 1250인 사원의 순위는?

DENSE_RANK([값]) WITHIN GROUP(PARTITION BY [컬럼명] ORDER BY [컬럼명])

예제) 이름, 월급, 순위를 출력하는데, RNAK와 DENSE_RANK의 차이를 확인하시오.


RANK()
select ename, sal, rank() over (order by sal desc) 순위
    from emp;

DENSE_RANK()
select ename, sal, dense_rank() over (order by sal desc) 순위
    from emp;

문제풀며 익히기

Q1.

부서번호, 이름, 입사일, 순위를 출력하는데 순위가 부서번호별로 각각 먼저 입사한 사원순으로 순위를 부여하시오


A. select deptno, ename, hiredate, dense_rank() over (partition by deptno
                                    order by hiredate asc) 순위
    from emp;

Q2.

월급 1250은 사원테이블에서 월급의 순위가 어떻게 되는가?


A. select dense_rank(1250) within group (order by sal desc) as 순위 
    from emp;

#어떤 순서대로 정렬된 그룹 사이에서 WHITHIN GROUP 즉 어느 그룹 사이에서 월급이 1250인 사원의 순위는?

Q3.

81/11/17에 입사한 사원은 사원 테이블에서 몇번째로 입사한 사원인가?


A. select dense_rank('81/11/17') within group (order by hiredate desc) as 순위
    from emp;

Q4.

우리반 테이블에서 성별, 나이, 순위를 출력하는데, 순위가 성별별로 각각 나이가 높은 순서대로 순위를 부여하시오.


A. select gender, age, dense_rank() over (partition by gender
                    order by age desc)as 순위
    from emp18
    where gender is not null;

043 데이터 분석 함수로 등급 출력하기(NTILE)

📖

데이터 분석 함수 NTILE() 앞에서 배운 rank와 dense_rank는 데이터의 순위를 출력하는 함수이고, NTILE은 데이터의 등급을 출력하는 함수이다.

나누고자 하는 등급의 수에 맞게 행의 수를 동일하게 분배 한 후, 남은 수는 높은 등급부터 낮은 등급에 차례로 분배한다.

예제)

이름과 월급과 월급에 대한 등급을 출력하시오.

(월급에 대한 등급을 4등급으로 나눠서 등급을 부여하시오.)

0~25%

25~50%

50~75%

75~100%


select ename, sal, ntile(4) over (order by sal desc) as 등급
	from emp;

#ntile의 괄호에 들어가는 숫자가 나누고자 하는 등급을 의미함

5등급으로 나눠서 출력시
select ename, sal, ntile(5) over (order by sal desc) as 등급
	from emp;

문제풀며 익히기

Q1.

우리반에서 통신사가 kt인 학생들의 이름과 나이와 등급을 출력하는데, 등급을 3등급으로 나눠서 출력하시오. (등급은 나이에 대한 등급입니다)


A. select ename, age, ntile(3) over ( order by age desc) as 등급
    from emp18
    where lower(telecom) = 'kt';

Q2.

우리반에서 통신사가 kt, sk인 학생들의 이름과 나이와 나이에 대한 등급을 출력하는데, 등급을 5등급으로 나눠서 출력하시오.

(등급은 나이에 대한 등급입니다)


A1. select ename, age, ntile(5) over ( order by age desc) as 등급
	    from emp18
	    where lower(telecom) in ('kt', 'sk', 'skt');

044 데이터 분석 함수로 순위의 비율 출력하기(CUME_DIST)

📖

데이터 분석 함수 CUME_DIST() 특정 데이터의 순위가 상위 몇 퍼센트인지 보고자 할 때 사용하는 함수

예제1)

이름, 월급, 월급에 대한 순위, 순위에 대한 비율을 출력하시오.


select ename, sal, rank() over (order by sal desc) as 순위,
									 rank() over (order by sal desc) / 14 as 비율
	from emp;

#위와 같이 sql을 작성하려면 14를 미리 알고있어야 함
##회사의 DB는 지금도 데이터가 계속 INSERT 되는 중임. 
###따라 숫자를 미리 알 수 없음
####그렇다고 아래와 같이 COUNT(*)를 쓰게되면 ERROR 발생

select ename, sal, rank() over (order by sal desc) as 순위,
									 rank() over (order by sal desc) / count(*) as 비율
	from emp;

**그래서 필요한 함수가 cume_dist() **
select ename, sal, rank() over (order by sal desc) as 순위,
								CUME_DIST() over (order by sal desc) as 순위비율
	from emp;

문제풀며 익히기

Q1.

위의 결과를 다시 출력하는데, 소수점 이후 2자리까지만 출력되게 반올림하라


select ename, sal, rank() over (order by sal desc) as 순위,
            ROUND(CUME_DIST() over (order by sal desc),2) as 순위비율
	from emp;

045 데이터 분석 함수로 데이터를 가로로 출력하기(LISTAGG)

📖

1. rank(값) within group (order by sal desc) 2. listagg(ename,',') within group (order by ename asc )


listagg([컬럼명],[구분자]) within group ( order by [정렬 기준 컬럼명] )

group by -> 데이터 분석 함수 중에 group by를 사용하는 함수

예제1)

부서번호, 부서번호별로 해당하는 사원들의 이름을 가로로 출력하시오!


select deptno, listagg(ename,',') within group(order by ename asc)
	from emp
	group by deptno;

문제풀며 익히기

Q1.

직업, 직업별로 속한 사원들의 이름을 가로로 출력하시오 ( 이름은 abcd 순서데로 출력하시오 !)


A. select job, listagg(ename, ',') within group(order by ename asc)
    from emp
    group by job;

Q2.

우리반 테이블에서 통신사, 통신사별로 속한 학생들의 이름을 가로로 출력하는데, 나이가 높은 학생들부터 출력되게 하시오.


A. select decode(lower(telecom),'skt','sk',lower(telecom)),
        listagg(ename, ',') within group (order by age desc)
    from emp18
    where telecom is not null
    group by decode(lower(telecom),'skt','sk',lower(telecom));

#깔끔한 sql을 위해 데이터 업데이트
		update emp18
		    set telecom='sk'
		    where ename='박성환';
    
		commit;

업데이트 후 A.
	select lower(telecom),listagg(ename, ',') within group (order by age desc)
    from emp18
    where telecom is not null
    group by lower(telecom);

Q3. (서울시 공무원분이 요청했던 SQL)

아래와 같이 결과를 출력하시오. 출력예시 ⇒ KT 홍길동(46)


A. select lower(telecom),listagg(ename || '(' || age || ')', ',') within group (order by age desc)
    from emp18
    where telecom is not null
    group by lower(telecom);

046 데이터 분석 함수로 바로 전 행과 다음 행 출력하기(LAG, LEAD)

📖

데이터 분석함수 LAG(), LEAD() 데이터 분석 함수로 바로 전 행과 다음행 출력하는 함수 →금융권 쪽에서 많이 사용


lag([컬럼명], [숫자옵션]) over (order by [컬럼명])
		숫자옵션 - 1 : 전 행, 2 : 전전 행

lead([컬럼명], [숫자옵션]) over (order by [컬럼명])
		숫자옵션 - 1: 다음행, 2: 다음 다음 행

예제)

사원번호, 사원이름, 바로 전 행의 사원번호, 바로 다음행의 사원번호를 출력하시오.


select empno, ename, lag(empno,1) over (order by empno asc) 이전행,
		lead(empno, 1) over (order by empno asc) 다음행
	from emp;

예제2)

이름, 입사일, 바로 전 행의 입사일을 출력하시오. (입사일이 ascending)


select ename, hiredate, lag(hiredate,1) over(order by hiredate asc)
	from emp;

문제풀며 익히기

Q1.

이름, 입사일, 바로 전에 입사한 사원 다음에 몇일 후에 입사했는지 출력하시오.


A. select ename, hiredate,
			hiredate - lag(hiredate,1) over (order by hiredate asc) as 간격일
		from emp;

047 ROW 를 COLUMN 으로 출력하기 1(SUM+DECODE)

📖

SUM+DECODE

행(row) → 컬럼(column) : sum + decode 또는 pivot문 사용

컬럼(column) → 행(row) : unpivot문 사용

sum+decode 는 특정컬럼(ex. 부서번호)의 값을 다 알고 있다는 가정하에 사용

그래서 이렇게 안하고 PL-SQL을 많이 사용함

*가로 출력의 장점

: 출력된 값에 대한 사칙연산이 쉬워짐

예제1)

부서번호, 부서번호별 토탈 월급을 출력하시오.


* 세로 출력
select deptno, sum(sal)
	from emp
	group by deptno;

DEPTNO|SUM(SAL)|
------+--------+
    10|    8750|
    30|    9400|
    20|   10875|

예제2)

부서번호, 부서번호가 10이면 월급을 출력하고 아니면 0을 출력하시오


select deptno, decode(deptno, 10, sal, 0) as "10"
	from emp;

#숫자로 별칭 사용 시 더블 쿼테이션 마크 둘러줘야함.
##그룹함수는 null값을 무시하기 때문에 0대신 null을 넣으면 조금 더 빨라짐

예제3)

위의 sql에 이어서 부서번호가 20번이면, 월급을 출력하고 아니면 0을 출력하고 또 부서번호가 30번이면 월급을 출력하고 아니면 0을 출력하시오.


select deptno, decode(deptno, 10, sal, 0) as "10"
						 	,decode(deptno, 20, sal, 0) as "20"
							,decode(deptno, 30, sal, 0) as "30"

from emp;

예제4)

위의 결과를 다시 출력하는데 앞에 부서번호는 지우고, sum을 사용하여 출력되는 월급을 다 더해서 출력하시오.


A. select sum(decode(deptno, 10, sal, 0)) as "10"
        ,sum(decode(deptno, 20, sal, 0)) as "20"
        ,sum(decode(deptno, 30, sal, 0)) as "30"
		from emp;

문제풀며 익히기

Q1.

직업, 직업별 토탈 월급을 출력하시오.(세로출력)


A. select job, sum(sal)    
    from emp
    group by job;

Q2.

직업과 직업별 토탈 월급을 가로로 출력하시오.


A. select sum(decode(job, 'ANALYST', sal, 0)) as ANALYST
            ,sum(decode(job, 'CLERK', sal, 0)) as CLERK
            ,sum(decode(job, 'MANAGER', sal, 0)) as MANAGER
            ,sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN
            ,sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT
     from emp;

Q3.

우리반 테이블에서 통신사, 통신사별 평균나이를 출력하시오.(가로로 출력)


A. select round(avg(decode(lower(telecom), 'kt', age, null))) as kt,
     round(avg(decode(lower(telecom), '알뜰kt', age,null))) as 알뜰kt,
     round(avg(decode(lower(telecom), 'lg', age,null))) as lg,
     round(avg(decode(lower(telecom), 'sk', age,null))) as sk
    from emp18;

Q4. (세로 출력)

입사한 년도 (4자리), 입사한 년도별 토탈월급을 출력하시오.


A. select to_char(hiredate, 'rrrr'), sum(sal)
    from emp
    group by to_char(hiredate, 'rrrr');

Q5.

위의 결과를 가로로 출력하시오.


A. select sum(decode(to_char(hiredate,'rrrr'),'1980', sal, 0)) as "1980",
        sum(decode(to_char(hiredate,'rrrr'),'1981', sal, 0)) as "1981",
        sum(decode(to_char(hiredate,'rrrr'),'1982', sal, 0)) as "1982",
        sum(decode(to_char(hiredate,'rrrr'),'1983', sal, 0)) as "1983"
    from emp;

Q6.

위의 결과를 다시 출력하는데, job도 함께 출력하고 group by job을 써서 결과를 출력하면 어떤 결과가 출력되는지 확인하지오.


A. select job, sum(decode(to_char(hiredate,'rrrr'),'1980', sal, 0)) as "1980",
        sum(decode(to_char(hiredate,'rrrr'),'1981', sal, 0)) as "1981",
        sum(decode(to_char(hiredate,'rrrr'),'1982', sal, 0)) as "1982",
        sum(decode(to_char(hiredate,'rrrr'),'1983', sal, 0)) as "1983"
    from emp
    group by job;

#직업별 년도별 토탈 월급이 출력됨

Q7.

다음과 같이 직업별 부서번호별 토탈월급을 출력하시오.


A. select job, sum(decode(deptno,10,sal,0)) as "10",
            sum(decode(deptno,20,sal,0)) as "20",
            sum(decode(deptno,30,sal,0)) as "30"
        from emp
        group by job;

048 ROW 를 COLUMN 으로 출력하기 2(PIVOT)

📖

SUM+DECODE와 PIVOT

행(row) → 컬럼(column) : sum + decode 또는 pivot문 사용

컬럼(column) → 행(row) : unpivot문 사용

PIVOT ⇒ Oracle에서만 지원


select [특정컬럼명] or *
	from (select [결과를 보기 위해 필요한 컬럼들만 선별],[컬럼명] from [테이블명])
	pivot ([컬럼명1] for [컬럼명2] in ([보고싶은 값])

#컬럼명2에 대하여 컬럼명1을 표시하는데 컬럼명 2의 값은 [보고싶은 값]을 기준으로!

!!PIVOT문 사용시 주의사항!!

FROM 절에 괄호를 열고(서브 쿼리 내에) 필요한 컬럼만 선택해야함

필요한 컬럼을 선택할 때 그룹함수 사용하지 말고 컬럼명만 사용

from 절의 서브쿼리 안에 함수를 사용할 때는 반드시 컬럼 별칭 사용!

예제1)

부서번호, 부서번호별 토탈월급을 가로로 출력하는데, pivot문으로 출력하시오.


select * 
	from (select deptno, sal from emp)
	pivot (sum(sal) for deptno in (10,20,30));

문제풀며 익히기

Q1.

아래의 sql을 pivot문으로 구현하시오.


select sum(decode(job, 'ANALYST', sal, 0)) as ANALYST
            ,sum(decode(job, 'CLERK', sal, 0)) as CLERK
            ,sum(decode(job, 'MANAGER', sal, 0)) as MANAGER
            ,sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN
            ,sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT
     from emp;


A. select *
    from (select job, sal from emp)
    pivot ( sum(sal) for job in ('ANALYST', 'CLERK', 'MANAGER', 'SALESMAN', 'PRESIDENT'));

#싱글 쿼테이션마크 안나오게 하려면?

A. select *
    from (select job, sal from emp)
    pivot ( sum(sal) for job in ('ANALYST' as "ANALYST",
            'CLERK' as "CLERK", 'MANAGER' as "MANAGER", 
            'SALESMAN' as "SALESMAN", 'PRESIDENT' as "PRESIDENT"));

Q2.

아래의 결과를 pivot 문으로 구현하시오.


select sum(decode(to_char(hiredate,'rrrr'),'1980', sal, 0)) as "1980",
        sum(decode(to_char(hiredate,'rrrr'),'1981', sal, 0)) as "1981",
        sum(decode(to_char(hiredate,'rrrr'),'1982', sal, 0)) as "1982",
        sum(decode(to_char(hiredate,'rrrr'),'1983', sal, 0)) as "1983"
    from emp;


A. select *
    from (select to_char(hiredate,'rrrr')as 년도, sal from emp)
    pivot (sum(sal) for 년도 in ('1980' as "1980",
                        '1981' as "1981", '1982' as "1982", '1983' as "1983"));

**from절에 대한 sub쿼리의 select절에 alias를 줘야함 안그럼 오류!**

Q3.

우리반 테이블에서 성별, 성별별 평균나이를 다음과 같이 가로로 출력하시오.


A. select *
    from (select gender, age from emp18)
    pivot (round(avg(age)) for gender in ( '남' "남", '여' "여"));

	!!ERROR!!
	PIVOT에 집계함수 사용시 오류 발생. 따라서 다음과 같이 사용

A. select round(남), round(여)
    from (select gender, age from emp18)
    pivot (avg(age) for gender in ( '남' "남", '여' "여"));

#특정 컬럼명만 추출할때 select절에 *대신 컬럼명 사용

ROUND(남)|ROUND(여)|
 --------+--------+
31      |      30 |

049 COLUMN 을 ROW 로 출력하기(UNPIVOT)

📖

SUM+DECODE와 PIVOT

행(row) → 컬럼(column) : sum + decode 또는 pivot문 사용

컬럼(column) → 행(row) : unpivot문 사용

UNPIVOT


select [특정컬럼명] or *
	from [테이블명]
	unpivot ([원하는 new 컬럼명] for [원하는 new컬럼명2] in ([특정 컬럼명],[특정컬럼명])

#unpivot (unpivot다음에 나오는 괄호 안에는 개수와 아이템 한글 글씨는 마음대로 작성해도 됨)
##unpivot에서 in다음에 나오는 괄호 안에 컬럼명을 쓸 때는 싱글 쿼테이션 마크를 쓰지 않음

UNPIVOT문 준비데이터.txt

0.3KB

위와 같이 표 형태의 데이터를 가지고

자전거 총개수, 카메라 총개수, 노트북 총개수를 출력하시오.


select sum(bicycle), sum(camera), sum(notebook)
	from order2;

#이러면 나열해야할게 많아짐... 
##위와 같이 sql을 작성해서 총 수량을 구한다면, 지금은 물건이 3개니까 
###컬럼을 3개만 쓰면 되지만, 만약 물건이 수백개라면 수백개를 써줘야함
####따라서 unpivot을 사용

select *
	from order2
	unpivot ( 개수 for 아이템 in (bicycle, camera, notebook));

ENAME|아이템  |개수|
-----+--------+--+
SMITH|BICYCLE | 2|
SMITH|CAMERA  | 3|
SMITH|NOTEBOOK| 1|
ALLEN|BICYCLE | 1|
ALLEN|CAMERA  | 2|
ALLEN|NOTEBOOK| 3|
KING |BICYCLE | 3|
KING |CAMERA  | 2|
KING |NOTEBOOK| 2|

위의 sql문 그대로 order3라는 테이블 만들기 → DBA만이 할 수 있음


create table order3
as
select *
	from order2
	unpivot ( 개수 for 아이템 in (bicycle, camera, notebook));
    
select * from order3;

create table 하고 as를 쓴 다음 select문을 사용하면 그대로 table이 만들어짐

문제풀며 익히기

Q1.

order3 테이블을 가지고 아래의 결과를 출력하시오.

아이템 sum(건수)

notebook 6


A. select 아이템, sum(개수)
    from order3
    group by 아이템;

Q2. (서울시 경찰청 데이터)

범죄와 범죄시간과 그 건수를 테이블로 생성하시오.


drop table crime_time;

 create table  crime_time
 ( crime_type   varchar2(20),
  f0t3  number(10),
  f3t6  number(10),
  f6t9  number(10),
  f9t12  number(10),
  f12t15  number(10), 
  f15t18   number(10),
  f18t21   number(10),
  f21t24     number(10)  );                                                                                                                                            


 insert into crime_time values('살인',36,34,26,51,46,46,56,69);
 insert into crime_time values('살인미수',59,45,24,48,44,61,105,123);
 insert into crime_time values('강도',381,594,136,135,169,155,228,349);
 insert into crime_time values('강간강제추행',2278,2675,1654,1073,1142,1423,1799,3248);
 insert into crime_time values('방화',269,272,107,145,147,211,252,339);
 insert into crime_time values('절도',23761,23392,20292,31732,32648,36720,45653,39745);
 insert into crime_time values('상해',7751,6107,3401,4412,4254,5508,7572,13043);
 insert into crime_time values('폭행',18911,14228,6918,7942,8129,10848,16806,31950);
 insert into crime_time values('체포감금',56,69,55,98,89,84,123,114);
 insert into crime_time values('협박',222,145,199,506,427,473,544,547);
 insert into crime_time values('약취유인',9,5,14,29,22,34,22,21);
 insert into crime_time values('폭력행위등',6915,5910,2470,2558,2787,4079,5376,9879);
 insert into crime_time values('공갈',207,165,269,612,758,1173,935,591);
 insert into crime_time values('손괴',6316,4938,3621,4486,4224,6059,10694,13566);
 insert into crime_time values('직무유기',4,6,5,110,27,18,17,10);
 insert into crime_time values('직권남용',11,4,6,43,29,17,6,11);
 insert into crime_time values('증수뢰',2,1,1,84,37,22,8,2);
 insert into crime_time values('통화',51,36,142,2018,777,590,209,133);
 insert into crime_time values('문서인장',193,127,65,2364,994,538,359,432);
 insert into crime_time values('유가증권인지',10,5,2,86,41,24,19,13);
 insert into crime_time values('사기',5149,4034,2315,39296,22459,13987,8323,8483);
 insert into crime_time values('횡령',837,686,884,4891,2292,1813,1653,1656);
 insert into crime_time values('배임',11,2,3,1007,268,55,24,6);
 insert into crime_time values('성풍속범죄',374,286,464,961,983,1608,1296,1353);
 insert into crime_time values('도박범죄',360,189,104,450,748,1670,1931,2024);
 insert into crime_time values('특별경제범죄',2573,1858,1549,10887,6360,5225,4828,5988);
 insert into crime_time values('마약범죄',110,87,53,261,292,322,477,439);
 insert into crime_time values('보건범죄',639,369,112,2301,1973,1178,900,1882);
 insert into crime_time values('환경범죄',23,29,120,408,813,351,66,64);
 insert into crime_time values('교통범죄',42507,31839,41865,44621,47385,59278,75466,157988);
 insert into crime_time values('노동범죄',30,21,33,245,74,45,73,157);
 insert into crime_time values('안보범죄',5,2,3,13,8,10,5,9);
 insert into crime_time values('선거범죄',11,14,49,225,142,124,86,38);
 insert into crime_time values('병역범죄',11,3,44,4085,775,106,108,28);
 commit;

Q3.

unpivot문을 이용해서 컬럼을 row로 변경하시오.

(왜? 컬럼이 row로 가줘야 데이터 추출이 편한 데이터이기 때문)


A. select *
    from crime_time
    unpivot (건수 for 시간 in(F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F21T24));

Q4.

위의 결과를 crime_time2라는 테이블로 생성하시오→ DBA만 가능


create table crime_time2
AS
 select *
    from crime_time
    unpivot (건수 for 시간 in(F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F21T24));
commit;

Q5.

살인이 가장 많이 일어나는 시간대를 출력하시오.

(지금까지 배운걸로 작성할 수 있음)


A. select 시간
    from crime_time2
    where crime_type = '살인'
    order by 건수 desc fetch first 1 rows only;

Q6.

방화가 많이 일어나는 시간대 순위 1위와 2위를 출력하시오.


A. select 시간
    from crime_time2
    where crime_type = '방화'
    order by 건수 desc fetch first 2 rows only;

050 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)

📖

데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)


sum([컬럼명]) over () as [별칭]
sum([컬럼명]) over (asc/desc rows/range between 
										unbounded preceding and current row) as [별칭]

*over() 괄호 안
1. 공백 => [컬럼명]의 전체 합계가 출력됨
2. order by [컬럼명] as 별칭 => [컬럼명]의 누적데이터가 출력됨
3. order by [컬럼명] asc/desc rows => 행을 기준으로 누적치를 출력
4. order by [컬럼명] asc/desc range => 범위를 기준으로 누적치를 출력(default)

- unbounded preceding : 맨 처음 행
- unbounded following : 맨 마지막 행
- current row : 현재행

예제1)


Q1.사원테이블에서 토탈월급을 출력하시오
		select sum(sal)
			from emp;

Q2. 사원테이블에서 이름, 월급, 사원 테이블의 토탈월급을 출력하시오.
		select ename, sal, sum(sal)
			from emp
			group by ename, sal;
		
	!! ERROR !!
	 원하는 것처럼 결과가 나오지 않음! 따라서 아래와 같이 수행

select ename, sal, sum(sal) over () as 토탈월급
	from emp;

#sum(sal) over 다음의 괄호에 아무것도 넣지 않으면, 전체 토탈월급이 출력되어짐

예제2)

이름, 월급, 월급의 누적치를 출력하시오.


select ename, sal, sum(sal) over (order by sal asc) 누적치
	from emp;

예제 3)

위의 결과를 다시 출력하는데, 옵션을 full로 다 써서 출력하시오.


select ename, sal, sum(sal) over(order by sal asc rows
														between unbounded preceding 
														and current row) 누적치
from emp;

예제4)

위 결과를 범위를 기준으로 출력


select ename, sal, sum(sal) over(order by sal asc range
														between unbounded preceding 
														and current row) 누적치
from emp;

문제풀며 익히기

Q1.

부서번호, 이름, 월급, 월급의 누적치가 출력되는데, 부서번호별로 각각 월급의 누적치가 출력되게 하시오.


A. select deptno, ename, sal, sum(sal) over(partition by deptno order by sal asc range
                            between unbounded preceding and current row) 누적치
    from emp;

Q2.

직업이 SALESMAN, CLERK인 사원들의 직업과 이름과 월급, 월급의 누적치를 출력하는데, 직업별로 각각 월급의 누적치가 출력되게 하시오.


A.select job, ename, sal, sum(sal) over (partition by job order by sal rows 
                        between unbounded preceding and current row) 누적치
    from emp
    where job in ('SALESMAN','CLERK');

Q3.

아래의 sql을 튜닝하시오.


select ename, sal, (select sum(sal) from emp e2
										where e2.empno <= e1.empno) 누적치
from emp e1
order by empno;


A. select ename, sal, sum(sal) over (order by empno rows between 
                    unbounded preceding and current row) 누적치
    from emp e1;

Q4.

아래의 sql을 튜닝하시오.


select deptno, ename, sal, (select sum(sal) from emp e2
													 where e2.empno <= e1.empno
														and e2.deptno = e1.deptno) 누적치
from emp e1
order by deptno, empno;


A. select deptno, ename, sal, sum(sal) over(partition by deptno 
				order by empno rows between unbounded preceding and current row) 누적치
    from emp;

051 데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)

📖

RATIO_TO_REPORT

예제)

부서번호가 20번인 사원들의 사원번호, 이름, 월급, 월급에 대한 비율을 출력하시오.


select empno, ename, sal, ratio_to_report(sal) over () as 비율,
													sal/sum(sal) over () as 비교비율
	from emp
	where deptno = 20;

문제풀며 익히기

052 데이터 분석 함수로 집계 결과 출력하기 1(ROLLUP)

📖

데이터 분석함수 ROLLUP

전체 집계 함수


group by roullup([컬럼명],[컬럼명])

*rollup()* 
21c에서는 rollup에서 정렬기능을 제외함. 왜냐하면 빅데이터가 되면서 데이터가 많아져서
정렬까지 자동으로 하게되면 성능이 느려지기 때문(DBA관점에서 확인)

	#문법 공식을 암기
	ROLLUP 함수안에 컬럼의 개수 + 1 개 만큼 집계한 결과 그룹이 출력

	select deptno, job, sum(sal) 
    from emp
    group by rollup(deptno, job) -> 2 + 1 = 3개의 집계 결과 그룹이 출력

	#출력되는 결과 집합
		1) 부서번호별 직업별 토탈월급(deptno, job)
		2) 부서번호별 토탈월급(deptno)
		3) 전체 토탈월급

예제1)

부서번호, 부서번호별 토탈 월급을 출력하시오


select deptno, sum(sal)
	from emp
	group by deptno;

예제2)

부서번호, 부서번호별 토탈월급을 출력하는데,

부서번호별 토탈월급들에 대한 전체 합계가 맨 아래에 출력되게 하시오.


select deptno, sum(sal)
	from emp
	group by rollup(deptno);

문제풀며 익히기

Q1.

통신사, 통신사별 인원수를 출력하는데 맨 아래에 전체 인원수가 출력되게하시오


A. select lower(telecom), count(*)
    from emp18
    where telecom is not null
    group by rollup(lower(telecom));

Q2.

위의 결과를 아래와 같이 출력하시오.

출력예시 ⇒ null로 출력된 부분을 “토탈:”로 출력


select nvl(lower(telecom),'토탈:'), count(*)
    from emp18
    where telecom is not null
    group by rollup(lower(telecom));

053 데이터 분석 함수로 집계 결과 출력하기 2(CUBE)

📖

데이터 분석 함수 CUBE()

전체토탈을 맨 위에 출력

rollup vs cube

rollup : 컬럼의 개수 + 1개가 그룹핑 되어 출력됨
cube : 2의 n승(n = 컬럼의 개수) 만큼 그룹핑 되어 출력됨

예제)

부서번호, 부서번호별 토탈월급을 출력하는데, 전체 토탈월급을 맨 위에 출력하시오.


A. select deptno, sum(sal)
		from emp
		group by cube(deptno);

문제풀며 익히기

Q1.

통신사, 통신사별 인원수를 출력하는데, 전체 학생 인원수가 맨 위에 출력되게 하시오.


A. select lower(telecom), count(*)
    from emp18
    group by cube(lower(telecom));

Q2.(SQLD + SQLP공통)

다음과 같이 CUBE에 컬럼을 여러개 기술해서 출력하시오.


A. select deptno, job, sum(sal)
		from emp
		group by cube(deptno, job);

#위의 sql의 그룹핑 된 결과 집합이 어떻게 4개인지?
1. deptno, job
2. deptno
3. 전체
4. job

Q3.

위의 공식을 이용해서 아래의 SQL의 그룹핑된 결과 집합을 적으시오.


select deptno, mgr, job, sum(sal)
	from emp
	group by cube(deptno, mgr, job);

정답)

2^3 = 8

1) deptno, mgr, job

2) deptno, mgr

3) mgr

4) 전체

5) job

6) deptno

7) mgr, job

8) deptno, job

054 데이터 분석 함수로 집계 결과 출력하기 3(GROUPING SETS) → rollup, cube 보다 많이 사용

📖

데이터 분석 함수 GROUPING SETS ROLLUP과 CUBE 보다 결과를 예상하기 쉬워서 많이 사용하는 레포팅 함수 → 올리브영과 투썸에서 많이 사용


group by grouping sets([컬럼명], ())
#()은 전체를 의미함

group by grouping sets(([컬럼명],[컬럼명]),([컬럼명]),());
#직관적임 #그룹핑할 컬럼 직접 기입

예제)

부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급을 출력하시오.


select deptno, sum(sal)
	from emp
	group by grouping sets(deptno, ());

DEPTNO|SUM(SAL)|
------+--------+
    10|    8750|
    30|    9400|
    20|   10875|
      |   29025|

문제풀며 익히기

Q1.

아래의 sql의 결과를 grouping sets로 구현하시오.


<구현 전>
select deptno, job, sum(sal)
	from emp
	group by rollup(deptno, job)
	order by deptno, job;


<구현 후>
select deptno, job, sum(sal)
	from emp
	group by grouping sets((deptno, job),(deptno),())
	order by deptno, job;

Q2.

아래의 sql의 결과를 grouping sets로 구현하시오.


<구현 전>
select deptno, sum(sal)
	from emp
	group by cube(deptno);


<구현 후>
select deptno, sum(sal)
	from emp
	group by grouping sets(deptno,())
	order by deptno nulls first;

Q3.(현업에서 사용하는 SQL) UNION ALL을 사용하지 않아 튜닝된 SQL 임

아래와 같이 결과를 출력하시오.

EMPNO ENAME SUM(SAL)

7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 7521 WARD 1250 7902 FORD 3000 7369 SMITH 800 7788 SCOTT 3000 7876 ADAMS 1100 7934 MILLER 1300 29025


A. select empno, ename, sum(sal)
    from emp
    group by grouping sets((empno, ename),());

055 데이터 분석 함수로 출력 결과 넘버링 하기(ROW_NUMBER)

📖

데이터 분석 함수 ROWNUM 출력되는 결과에 번호를 넘버링하는 SHADOW 컬럼(즉 감춰진 컬럼)


select rownum

예제1)

직업이 SALESMAN인 사원들의 이름, 월급, 직업을 출력하시오.


select ename, sal, job
	from emp
	where job = 'SALESMAN'

예제2)

위에서 출력되는 결과 앞에 번호를 순서대로 부여하여 출력하시오.


select rownum AS 번호, ename, sal, job
	from emp
	where job = 'SALESMAN'

주의

select 절에서 rownum을 사용하기 때문에 orderby로 정렬하면 넘버링이 뒤섞이게 됨

해결방법

from 절에 테이블명을 쓰는대신 괄호를 열고 서브 쿼리 작성

괄호 안에 있는 쿼리문의 결과가 하나의 집합이 되면서 마치 테이블처럼 사용되는 것.( 괄호안의 쿼리가 먼저 수행됨)

⇒ 위와 같이 FROM절에 쿼리문을 사용한것을 IN LINE VIEW라고 함

⇒ 서버 프로세서의 메모리 영역에 서브쿼리가 먼저 올라감

주로 사용하는 경우

어떤 큰 테이블의 일부만 보고싶을 때 사용


select * 
	from emp 
	where rownum < 4;  #맨 위에 3줄만 보겠다는 것

# 데이터의 양이 많을 때!
## where절 없이 select 하게 되면, db에 부하를 주게 됨

특징(주의)

앞에 있는 번호들을 전부 봐야 특정 번호의 rownum 데이터를 볼 수 있음


select ename, sal
    from emp
    where rownum = 1; #출력됨
    
select ename, sal
    from emp
    where rownum = 2; #출력되지 않음 

즉 순서대로 출력되게 해야 볼 수 있음 

select ename, sal
    from emp
    where rownum <= 2; #이렇게 수정해야함

특정 번호의 rownum데이터만 출력하고 싶으면 별칭 사용!


select 번호, ename, sal    
    from (select rownum 번호, ename, sal
            from emp
             where job = 'SALESMAN')
    where 번호 = 2;

#만약

select rouwnum, ename, sal    
    from (select rownum, ename, sal
            from emp
             where job = 'SALESMAN')
    where rownum = 2;

#으로 출력하면 from 절 밖의 rownum으로 인식 되기 때문에
##from절 안의 rownum을 가져오기 위해 확실하게 별칭 사용

📖

데이터 분석 함수 ROW_NUMBER()

정렬된 결과에 번호를 넘버링하는 SHADOW 컬럼(즉 감춰진 컬럼)

rownum을 보완


select row_number() over (order by [정렬 기준 컬럼명] desc)

ROW_NUMBER() vs ROWNUM

ROW_NUMBER() : 정렬된 결과에 번호를 넘버링 하고싶을 때 사용
ROWNUM : 그냥 쿼리문의 출력 결과에 번호를 넘버링 하고 싶을 때

문제풀며 익히기

Q1.

위의 결과를 다시 출력하는데, 월급이 높은 사원부터 출력하시오.


A. select rownum, ename, sal, job
		from emp
		where job = 'SALESMAN'
    order by sal desc;

#실행 순서가 select문 이후 order by 이기 때문에 
##rownum 번호가 뒤섞임

Q2.

위의 결과를 다시 출력하는데, 번호가 1,2,3,4,순으로 부여되어서 출력되게 하시오.


A. select rownum, ename, sal, job
		from( select ename, sal, job
						from emp
						where job = 'SALESMAN'
						order by sal desc);

#from절에 서브쿼리를 사용함

Q3.

위와 같이 from 절의 서브쿼리를 이용하지 않고 row_number()함수를 이용해서 위의 결과를 출력하시오.


A. select row_number() over (order by sal desc) 번호, ename, sal, job
		from emp
		where job = 'SALESMAN';

Q4.

통신사가 kt인 학생들의 이름과 나이와 통신사를 출력하는데, 나이가 높은 학생부터 출력하고, 앞에 번호를 붙여서 출력하시오.


A. select row_number() over ( order by age desc) 번호, ename, age, telecom
		from emp18
		where lower(telecom)='kt';