개발기록

1. 조인을 사용하여 뉴욕에서 근무하는 사원의 이름과 급여를 출력해라.

2. 조인을 사용하여 ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력해라.

3. 직급이 MANAGER인 사원의 이름, 부서명을 출력해라.

4. 매니저가 KING인 사원들의 이름과 직급을 출력해라.

5. SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력해라.

6. 부서 테이블의 40번 부서와 조인할 사원 테이블의 부서 번호가 없지만 40번 부서의 부서 이름도 출력되도록 해라.

(* : 주로 사용)

01) 숫자 함수

1. 사원 번호가 홀수인 사람들을 검색해라.

02) 문자 처리 함수

2. 직급이 'manager'인 사원을 검색해라. (LOWER 사용)

3. 82년도에 입사한 직원을 구해라. (SUBSTR, BETWEEN 사용)

4. 이름이 E로 끝나는 사원을 검색해라. (LIKE, SUBSTR 사용)

5. 이름의 세 번째 자리가 R로 끝나는 사원을 검색해라. (LIKE, INSTR, SUBSTR 사용)

03) 날짜 함수

6. 각 사원들의 현재까지의 근무 일수를 구해라. 

04) 형 변환 함수

05) NULL을 다른 값으로 변환하는 NVL 함수

7. MANAGER가 없는 사원만 출력하되 MGR 컬럼값은 NULL대신 CEO로 출력해라. 

06) 선택을 위한 DECODE 함수

DECODE (표현식, 조건1,결과1,
                       조건2, 결과2, 기본결과n)

8. 직급이 'ANAIYST'인 사원은 5%, 'SALESMAN'인 사원은 10%, 'MANAGER'인 사원은 15%, 'CLERK'인 사원은20% 급여를 인상해라.

07) 조건에 따라 서로 다른 처리가 가능한 CASE 함수

CASE WHEN 조건1 THEN 결과1
        WHEN 조건2 THEN 결과2
        ELSE 결과n
END

9. 직급이 'ANAIYST'인 사원은 5%, 'SALESMAN'인 사원은 10%, 'MANAGER'인 사원은 15%, 'CLERK'인 사원은20% 급여를 인상해라.

5-1. RANKING

RANKING 함수들은 주어진 기준 값에 근거하여 DATASET 내의 다른 RECORD와 비교되는 RECODE의 순서를 계산

5.1.1 RANK와 DENSE_RANK

• RANK() : 중복 순위 개수만큼 다음 순위 값을 증가 시킴
• DENSE_RANK() : 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시

• OVER : 쿼리 RESULT SET을 이용해 동작하는 함수
• PARTITION BY : RESULT SET을 value expression에 지정된 값에 근거하여 분할하는 역할 수행
• ORDER BY : 각 PARTITION 내에서 DATA가 어떤 값을 기준으로 정렬될 것인가를 지정 
• NULLS FIRST | NULLS LAST : NULL이 포함된 ROW가 순서상 제일 앞에 위치할 것인지 제일 뒤에 위치할 것인지를 지정

1) SALARY 값으로 순위를 부여하는 SQL 문장

• RANK와 DENSE_RANK의 차이점은 순위를 부여하는 간격 

  • 7등이 2명 나오면 다음 등수는 DENSE_RANK : 8등, RANK : 9등이 된다.

• OVER의 의미는 해당 함수가 쿼리의 RESULTEST을 이용하여 동작하는 함수라는 표시

  • ()안에 ORDER BY는 RESULTTEST에 순위를 부여할 때의 정렬 기준을 표시

2) 직원들을 부서별로 급여 기준 순위를 부여하는 문장 (PARTITION BY 기능)

• PARTITION BY DEPT_CODE에 의해 부서별 급여 RANKING이 구해졌다.
  
  • PARTITION BY는 분석용 함수가 작용하는 단위를 표시(생략될 경우 RESULTSET이 하나의 작용 단위)

3) 사원별 RANKING을 구하고 부서별로 구한 소계까지 RANKING을 부여해라.

• EMP_ID만이 NULL로 나온 ROW는 부서별 SUBTOTAL이고 DEPT_CODE까지 NULL로 나온 것은 GRANDTOTAL(합계)

4) SALE_HIST의 자료를 이용하여 일자별 매출순위와 순위별 사업장, 품목을 보여라.

5.1.2 CUME_DIST, PERCENT_RANK, NTILE(N), ROW_NUMBER()

• CUME_DIST : 관련 DATASET 안에서 해당 ROW의 위치를 0에서 1까지의 값으로 표시해줌
  • RANK()와 비슷한 기능이지만 CUME_DIST는 최대값 1을 기준으로 0에서 1사이의 값으로 표시하고 자신과 동일한 순위를 모두 포함한 값을 기준으로 보여줌
• PERCENT_RANK : (자신이 속한 PARTITION내의 자신의 RANK - 1) / (자신이 속한 PARTITION내의 ROW 숫자 - 1)
• NTILE(N) : 자신이 속한 PARTITION의 ROW들을 지정한 숫자만큼으로 분류하고자 할때 각 ROW의 위치를 표시
• ROW_NUMBER() : PARTITION 내의 ROW들에 순서대로 UNIQUE한 일련번호를 부여

1) CUME_DIST와 RANK의 차이점

• C0은 RANKING을 표시
• C1은 CUME_DIST을 표시
• C2는 SALARY를 기준으로 자신과 동일한 값을 포함한 자신의 순위를 표현

2) 각 ROW를 SALARY 기준으로 CUME_DIST, PERCENT_RANK, NTILE(N), ROW_NUMBER()의 값을 구해라.

• C2 : 해당 ROW의 SALARY와 동일한 값과 이전 값의 수 / 20(ROW수) 
• C3 : (자신이 속한 PARTITION내의 자신의 RANK - 1) / (자신이 속한 PARTITION내의 ROW 숫자 - 1)
  • 이태백) 자신이 속한 PARTITION내의 자신의 RANK - 1 = 1 - 1 = 0 
  • 자신이 속한 PARTITION내의 ROW 숫자 - 1 = 20 - 1 = 19
  • PERCENT_RANK = 0/19 = 0

3) 사번의 앞 4자리를 입사년도라고 가정하고 동일 입사년도 내에서 각 ROW가 가지는 SALARY순의 값을 구해라.

5-2. WINDOWING 함수

• OVER : 쿼리 RESULT SET을 이용해 동작하는 함수라는 구분
• RESULT : SET을 value expression2에 지정된 값에 근거하여 분할하는 역할 수행
• ORDER BY : 각 PARTITION 내에서 DATA가 어떤 값을 기준으로 정렬될 것인가를 지정
• NULLS FIRST | NULL LAST : NULL이 포함된 ROW가 순서상 제일 앞에 위치할 것인지 뒤에 위치할 것인지를 지정
• ROWS | RANGE : 자료의 물리적 순서를 이용(ROWS)할 것인지 논리적 순서를 이용(RANGE)할 것인지를 결정
• BETWEEN... AND : 자료의 범위를 결정
• UNBOUNDED PRECEDING : 지정된 값 이전의 모든 ROW를 포함시킴
• UNBOUNDED FOLLOWING : 지정된 값 이후의 모든 ROW를 포함시킴
• CURRENT ROW : 현재 ROQ를 시작 값 또는 마지막 값으로 이용할 때 사용함 

1) SALE_HIST테이블에서 '01' 사업장의 품목별 당일 판매액과 당일까지의 누적 판매액을 구해라.

2) 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균을 구해라.

3) 자신의 급여와 바로 이전 사번 3명의 급여를 이용하여 합계, COUNT, 평균을 구해라.

4) 각 ROW의 판매액, 동일 일자/동일 품목의 최대 판매액, 최대 판매액 사업장, 해당 사업장 최소 판매액, 최소 판매액 사업장을 구해라.

• FIRST_VALUE : PARTITION BY에 의해 분류된 범위 내에서 ORDER BY에 의해 정렬을 한 후 ROWS 또는 RANGE에 의해 범위가 지정되면 그 중 제일 앞에 위치하는 ROW의 값들을 읽어올 때 사용
• LAST_VALUE : PARTITION BY에 의해 분류된 범위 내에서 ORDER BY에 의해 정렬을 한 후 ROWS 또는 RANGE에 의해 범위가 지정되면 그 중 제일 뒤에 위치하는 ROW의 값들을 읽어올 때 사용

5) 일자별 매출액과 함께 각 일자의 매출액이 전체일자 매출액에서 차지하는 비율을 구해라.

• RATIO_TO_REPORT : 전체 대비 해당 ROW의 값이 차지하는 비율을 구하는 함수

6) 사업장별 품목의매출액과 함께 동일 사업장 동일 품목의 전일 매출액과 다음날 매출액을 구해라.

• LAG : 바로 이전 값을 참조, LEAD : 바로 이후 값을 참조
• LAG와 LEAD 함수 안에 지정되는 숫자가 각각 앞뒤로 몇 번째 ROW를 참조할 것인지를 결정

5-2-1) SALE_HIST 자료를 이용하여 '01' 사업장 'PENCIL' 품목의 일자별 누적 판매금액을 구해라.

5-2-2) 품목별/일자별로 과거 판매액을 모두 이용하는 이동 평균값을 구하라.

5-2-3) '01' 사업장 'PENCIL' 품목에 대해 일자별 매출액과 전일 매출, 당일과 전일의 매출액 차이를 구하시오.

5-3. CASE

1)  SALARY로 분류하여 30000000이하는 'D', 30000000 초과 50000000 이하는 'C', 50000000 초과 70000000 이하는 'B', 70000000 초과는 'A' 라고 등급을 분류하여 등급별 인원수를 구해라.

2) 행 단위로 나오는 값을 컬럼 단위로 표현해라.

5-3-1) 일자별 품목별로 '01', '02' 사업장 판매 금액 합, '02', '03' 사업장 판매 금액 합을 구해라.

5-4. 통계 함수

• VAR_POP : 모집단의 분산을 구해줌
• AVR_SAMP : 표본집단의 분산을 구해줌
• STDDEV_POP : 모집단의 표준편차를 구해줌
• SDTDEV_SAMP : 표본집단의 표준편차를 구해줌
• CORVAR_POP : 모집단의 공 분산을 구해줌
• CORVAR_SAMP : 표본집단의 공 분산을 구해줌
• CORR : 산관계수를 구해줌

5-5. 회귀 분석용 함수

• REGR_COUNT : 회귀선상에 찍히는 값을의 숫자를 구해줌
• REGR_AVGY, REGR_AVGX : 회귀선의 독립변수와 종속 변수의 평균을 구해줌
• ...

1. ROW(행) 단위 자료를 COLUMN(열) 단위로

1-1. SAM_TAB02에 있는 19건의 자료를 4건씩 묶어서 한 중에 보여주고 해당 행의 번호를 보여주는 쿼리를 작성해라.

	결과 값

1단계	2단계	3단계

4단계	5단계
	그룹함수는 여러 개의 행을 받아서 하나의 행을 결과로 돌려줌

• CEIL(N) : 지정된 값 이상의 가장 작은 정수를 리턴
• MOD(M, N) : N으로 M을 나눈 나머지를 리턴 (N이 0일 경우 M 리턴)
• DECODE : IF문의 기능

1-2. DY_TEMP의 자료를 이용해 한 행에 5명의 사번과 성명을 보여주는 쿼리를 작성해라.

	결과 값

1단계	2단계

3단계

4단계

2. COLUMN(열) 단위 자료를 ROW(행) 단위로

2-1. 각 행에 1학년부터 4학년까지를 분리해서 한 행에 하나의 학년만 나오도록 하고자 한다. 

	결과 값

1단계	2단계

2-2. 1, 2학년과 3, 4학년의 인원 수가 각각 같은 줄에 나오도록 쿼리를 작성해라.

결과 값

SELECT에서의 산술 연산

<SALARY(연봉)을 이용하여 월 급여 계산>

1) DY_TEMP 테이블에서 SALARY을 이용하여 월 급여를 알아보는 SQL문. 월 급여는 연봉을 18로 나누어 홀수 달에는 연봉의 1/18이 지급되고, 짝수 달에는 연봉의 2/18가 지급된다고 가정했을 대 홀수 달과 짝수 달에받을 금액을 검색

2) 위에서 구한 월 급여에교통비가 10만원씩 지급된다면(짝수 달은 20만원) 위의문장이 어떻게 바뀔지 작성해라.

NULL의 사용

<NULL값인 것을 제외하고 보기>

1) DY_TEMP 테이블에서 HOBBY가 NULL이아닌 사람의 성명을 검색해라.

2) HOBBY가 NULL인 사람은 모두 HOBBY를 "없음"이라고 값을 치환하여 가져오고 나머지는 그대로 값을 읽어온다. (이름과 취미 검색)

3) HOBBY의 값이 NULL인 사원을 '등산'으로 치환했을 때 HOBBY가 '등산'인 사람의 성명을 가져와라.

컬럼 ALIAS와 테이블 ALIAS

1) DY_TEMP의 자료 중 EMP_ID와 EMP_NAME을 각각 '사번', '성명'으로 표시되어 DISPLAY 되도록 COLUMN ALIAS을 부여하여 검색해라.

CONCATENATION

두 개 이상의 문자열을연결하여 하나의 문자열을 만들어 낼 때 사용

방식은 CONCAT함수를 사용하거나 합성연산자(||) 를 이용

1) 성명을 보여줄 때 직급을 괄호 안에 함께 보여주는 경우를 생각해라.

2) 괄호 대신 작은따옴표로 묶어줄 땐 두개를 함께 사용 '(' => '''' 이런 식으로

연산자

<LIKE  검색>

1) 부서 코드가 A로 시작되는 ROW를 검색

2) 부서 코드 중에 A가 들어가는 ROW를 검색

3) 총 6자리 부서코드중 2번째 자리에 A가 들어가는 ROW를 검색

<BETWEEN 검색>

1) 사번이 1997로 시작하는 사원의 사번과 성명을 검색

2) 성명이 'ㄱ'으로 시작되는 사람의 사번과 성명을 검색

GROUP BY와 HAVING

1) LEV별로 최고액 연봉이 얼마인지 검색

2) AREA별로 최소 BOSS_ID를 골라내고 이 결과를 BOSS_ID 별로 정렬

3) 직급별로 연봉 평균을 구한 상태에서 평균 연봉이 5천만원 이상인 경우의 직급과 평균 연봉을 검색

4) 직급별로 사번이제일 늦은 사람을 구하고 그 결과 내에서 사번이 1997로 시작하는 결과를 검색

출처 blog.daum.net/why_i_am/8?category=2113847

문제 www.hackerrank.com/challenges/revising-the-select-query/problem

인구가 100000보다 큰 CITY 테이블의 모든 미국 도시에 대한 모든 열을 쿼리합니다. America의 CountryCode는 USA입니다.

정답

문제 www.hackerrank.com/challenges/revising-the-select-query-2/problem

인구가 120000보다 큰 CITY 테이블의 모든 미국 도시에 대한 NAME 필드를 쿼리합니다. America의 CountryCode는 USA입니다.

정답

Lev4. 우유와 요거트가담긴 장바구니

문제 programmers.co.kr/learn/courses/30/lessons/62284

정답

Lev2. DATETIME에서 DATE로 형 변환

문제 programmers.co.kr/learn/courses/30/lessons/59414

정답

Lev3. 오랜 기간 보호한 동물 (2)

문제 programmers.co.kr/learn/courses/30/lessons/59411

정답