기술노트 블로그

* Youtube Link - https://www.youtube.com/watch?v=aAYU18AGyDg&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=11

01. 파티션 문제

MANAGER_ID를 파티션으로 나누어서 연봉으로 오름차순을 하고 처음 값 부터 누적계산을 하여

SUM(SALARY)를 도출해낸다.

이로써 또 다른 의미있는 데이터를 만들어낸다.

AND CURRENT ROW와 같은 결과가 나온다.

RANGE BETWEEN 10 PRECEDING AND 150 FOLLOWING을 하면

예를 들어 SALARY가 2500인 열은 SALARY가 2490부터 2650까지의 범위를 만들어내고

이를 파티션으로 COUNT(*)을 한 결과가 컬럼 TTT에 나오는 것이다.

ROWS BETWEEN  1 PRECEDING AND 1 FOLLOWING의 경우 

예를 들어 SALARY가 6500인 경우

그 위의 행인 SALARY가 5800인 행과

바로 그 아래 행인 SALARY가 7900인 행 이렇게 3개의 행의 연봉의 평균값을 계산해서 

결과를 도출하는 것이다.

02. 인라인 뷰 및 UNION 

(1)이 정답이다.

(2)의 경우 문법적으로 오류가 난 경우이다.

group by salary로 하고 select 절에서 job_id를 조회를 하면

예를 들어 1500인 salary를 갖는 컬럼이 job_id를 2개 갖게 된다.

그래서 group by를 사용을 하면 사용할 수 있는 컬럼이 한정이 된다.

그래서 (2)은 정답이 아니다.

union all은 중복도 되지 않고 정렬도 되지 않는다.

union 기능이 sort unique였지만

오라클이 업그레이드 되면서 hash unique(중복제거+정렬 안할 수 있음)

으로 변경되었다.

03. CASE 문법의 종류와 CASE 문법의 NULL 연산

SIMPLE CASE 문법과 

SEARCHED CASE 문법 이렇게 2개의 문법이 있다.

(1)의 경우

SIMPLE CASE 문이다.

WHEN NULL의 경우 '='의 연산을 하므로 NULL은 연산이 불가능하다. 그래서 함수를 사용해야 한다.

그래서 NULL인 컬럼은 else문인 0으로 출력된다.

NULL은 IS NULL 연산으로 연산할 수 있다.

DECODE() 함수는 NULL인 경우 -1. 아니면 있는 값 그대로를 배출하도록 만들어졌다.

예를들어 a.COL1이 NULL이면 -1 아니면 a.coll을 배출한다.

04. WHERE절 사용 방법

정답은 (4)이다.

(2)과 (4)의 다른 점은

정렬이 다르다는 것이다.

05. 데이터 입력 문제

숫자를 VARCHAR2(10) 데이터를 입력하려고 하면

형이 달라서 자기 형으로 바꿔본다. 그래서 TO_CHAR(002)를 한다.

그러면 '002'가 된다.

정답은 (4)이다.

숫자값을 DATE에 넣으려고 한다.

그러면 TO_DATE(20220420)을 하면 날짜 값으로 들어갈 수도 있다.

하지만 에러가 날 확률이 높다.

06. 여러 열 ORDER BY 문제

먼저 ORDER BY COL1 DESC를 한다.

그리고 COL2에서 같은 값들 중 COL3를 기준으로 정렬을 한다.

NULL은 제일 아래로 내려간다.

그래서 정답은

(3)이다.

07. 집계 함수

각 열을 집계해서 조회한다.

이렇게 다양한 컬럼들을 집계해서 조회할 수 있다.

정답은 (2)이다.

08. GROUP BY 활용

정답은 (3)이다.

각 계층별 결과를 정확히 알아야 한다.

GROUP BY DEPTNO, JOB: 이 경우는 전체를 GROUP BY한 결과가 나와선 안 된다.

GROUP BY GROUPING SETS(DEPTNO, JOB): JOB으로도 전체를 GROUP BY한 결과가 나와야 한다.

GROUP BY ROLLUP(DEPTNO, JOB): ROLLUP은 계층적으로 GROUP BY를 하여서 조회한다.

GROUP BY CUBE(DEPTNO, JOB): 모든 경우의 수를 GROUP BY하여 조회한다.

09. ORACLE을 ANSI 표준 SQL문으로 변환하는 문제

정답은 (4)이다.

10. 조인 문제

모든 경우의 수를 구하고

조인 조건에 맞는 결과만 조회한다.

카티션 조인이 된 순간 모든 경우의 수가 처리된 것을 볼 수 있다.

정답은 (1)번이다.

INNER JOIN은 키 값이 같은 결과만 조회한다.

LEFT OUTTER JOIN과 RIGHT OUTTER JOIN은 각각 키 값이 같지 않은 결과도 조회한다.

FULL OUTTER JOIN은 양 측의 테이블에서 키 값이 같지 않은 결과도 조회한다.

CROSS JOIN은 카티션 조인을 하여서 모든 경우의 수를 구한다. 예를 들어 4개 행과 3개 행을 곱해서 12개의 모든 경우의 수를 조인해서 조회한다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=rDBZ6q0QqBU&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=9

01. SQL 조회 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 의 순서로 조회가 된다.

HAVING의 경우 GROUP BY를 통해서 집계가 완료된 상태를 토대로 추가적인 필터링을 하는 것이다.

그리고 SELECT를 통해서 해당하는 데이터가 출력이 된다.

ORDER BY를 통해서 출력하는 대상을 특정 컬럼을 기준으로 정렬을 한다.

그래서 순서를 잘 암기해야 한다.

02. 다중 컬럼 연산

예를 들어 컬럼 IN (1,2,3); 이면

컬럼 = 1 OR 컬럼 = 2 OR 컬럼 = 3이 됩니다.

다중 컬럼 연산의 경우

WHERE (COL1, COL2) IN ('x', 'y'), ('KK', 'BB')); 이면

COL1 = 'x' 이고 COL2 = 'y' 이거나 COL1 = 'KK' 이고 COL2 = 'BB'인 경우이다.

각각 AND 그리고 OR 별로 처리가 된다.

여기서 NOT 연산은 TRUE -> FALSE로 그리고 FALSE -> TRUE로 변환하는 것이다.

그러면 해당 컬럼들을 제외한 값을 조회하는 결과가 되므로

같은 결과가 나온다.

이외 2~4번까지를 보면

가로 안에 OR 또는 = 연산자가 들어가는 것을 볼 수 있다.

이러한 방식으로도 연산이 가능하다.

다중 연산을 풀어서 설명한 그림이다.

정답은 (3)이다.

AND와 OR에는 우선순위가 있다.

AND -> OR 순서로 처리가 된다. 마치 수학에서 곱하기와 더하기와 같다.

03. WITH GRANT OPTION 과 권한 회수

예를 들어

REVOKE 권한 FROM B CASCADE 라고 작성을 하시면

WITH GRANT OPTOIN으로 권한을 주었던 그 대상도 연쇄적으로 뺏어버린다.

CASCADE 를 작성을 안 하면 B만 뺏기고 C는 그대로 권한을 갖고 있는 것이다.

CASCADE를 사용을 한 다는 가정하에 정답은 (4)이다.

그러나 CASCADE를 사용을 안 한다면 정답은 (3)이다.

04. CASCADE CONSTRAINT

만일 DROP TABLE [테이블명] CASCADE CONSTRAINTS; 를 하면

테이블과 그에 연관된 제약조건들이 전부 다 삭제가 된다.

예를 들어 STUDENT 테이블을 삭제를 하면 STUDENT_TEL과 STUDENT_ADDR과의 관계가 제거됩니다.

즉 STUDENT_TEL과 STUDENT_ADDR의 FK였던 데이터들은 그대로 남아 있지만 FK였던 제약조건은 의미가 없어지므로 제거가 됩니다.

그 반대로 CASCADE CONSTRAINTS를 붙이지 않는다면 STUDENT_TEL, STUDENT_ADDR은 제약조건에 의해서 제거가 되지 않습니다.

* CASCADE는 SQL-SERVER에서는 지원하지 않는다. SQL-SERVER는 MS사이다. ORACLE사가 아니다.

정답은 (4)이다.

05. DDL/DML/DCL/TCL

정답은 (2) DML에 대한 설명이다.

DCL은 데이터 컨트롤 언어이다.

06. NVL() 함수 사용

GROUP BY가 없으므로 전체를 대상으로 집계를 한다.

NVL() 함수를 사용하므로 만일 CNT 열이 NULL이라면 그 대체할 값을 출력해주는 함수이다.

NVL(CNT, 0) 이면 CNT열의 값이 NULL이면 0으로 대체된다.

그리고 숫자와 NULL을 연산을 하면 NULL은 제외하고 연산이 된다.

정답은 (3)이다.

07. GROUP BY 연산 문제

COUNT(*)의 경우 NULL과 관계없이 개수를 파악한다.

FROM 부터 시작을 해서 GROUP BY를 CLASS열 기준으로 하였다.

그리고 distince NAME으로 NAME열을 기준으로 중복을 제거 하였다.

그리고 count() 함수를 사용한 결과를 얻었으므로 

정답은 (2)이다.

COUNT(1)의 경우

COUNT(*)과 같은 행의 수를 조회를 한다.

성능도 비슷하다. COUNT(*)을 사용할 것을 권장한다.

COUNT(컬럼)의 경우 만일 컬럼이 NULL이면 조회가 결국 되지 않고 개수가 파악된다.

이러한 결과가 조회된다.

08. WINDOW 함수 문제 (순위 문제)

OVER가 나온다면 WINDOW 함수라는 것을 캐치하시고

RANK()의 경우 중복된 순위 다음에는 그 다음 순위를 부여한다.

DENSE_RANK()의 경우 중복된 순위 다음에는 바로 다음 순위를 부여한다.

ROW_NUMBER()의 경우 그대로 1부터 차례대로 순위를 부여한다.

정답은 (2)이다.

09. 윈도우 함수 (집계 문제)

먼저 테스트할 데이터는 HR.EMPLOYEES의 SALARY, JOB_ID 그리고 EMPLOYEE_ID 이다.

OVER() 함수는 SUM(SALARY)의 기준을 정하는 함수이다. 집계 함수 바로 뒤에 붙여서 사용된다.

OVER() 함수에 PARTITION BY를 사용을 하면 JOB_ID를 파티션으로 연봉의 합계를 구한다.

ORDER BY를 통해서 EMPLOYEE_ID를 기준으로 오름차순한다.

RANGE BETWEEN은 행을 어디까지 처리할 것이냐는 의미이다.

UNBOUNDED PRECEDING AND UNBOUNDED FOLLWING 은 구분 없는 이전 그리고 구분 없는 이후까지 행을 처리하겠다는 의미이다.

하지만 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 

각 행별로 누적값을 구하게 된다.

정답은 오류가 발생하는 (3)이다. 이미 UNBOUNDED PRECEDING이 범위에 나왔기 때문이다.

감사합니다.