sqld 2과목 정리(SQL 활용)

 

1) JOIN : 테이블 간의 결합 / 집합과 유사함!

*교집합 & 합집합 

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• (FULL) OUTER JOIN

 

* 합집합

- UNION (ALL)

 

* 차집합

- MINUS (#orable) = EXCEPT (#SQL Server)

 

* 결합되는 대상간의 일치 정도

- EQUI 조인 <> non-EQUI 조인

 

* 조건구 없는 CROSS JOIN(상호 결합)

 

INNER JOIN

완전한 교집합만 가져올 떄 INNER JOIN을 사용한다

 

INNER JOIN 표현 방식 2가지

1번
SELECT A.*, B.연령
FROM GENDER A INNER JOIN AGE B
         ON A.회원코드 = B.회원코드
WHERE A.성별 = 'F';

2번.
SELECT A.*, B.연령
FROM GENDER A, AGE B
WHERE A.회원코드 = B.회원코드
           AND A.성별 = 'F';

 

 

LEFT (OUTER) JOIN

 

 

LEFT (OUTER) JOIN 표현방식 2가지

1번
SELECT A.*, B.연령
FROM GENDER A LEFT JOIN AGE B
        ON A.회원코드 = B.회원코드;

2번
SELECT A.*, B.연령
FROM GENDER A, AGE B
WHERE A.회원코드 = B.회원코드(+);

 

 

RIGHT (OUTER) JOIN

 

 

RIGHT (OUTER) JOIN 표현방식 2가지

1번
SELECT B.회원코드, 성별, 연령
FROM GENDER A RIGHT JOIN AGE B
          ON A.회원코드 = B.회원코드;

2번
SELECT B.회원코드, 성별, 연령
FROM GENDER A, AGE B
WHERE A.회원코드(+) = B.회원코드;

 

 

(FULL) OUTER JOIN

 

(FULL) OUTTER JOIN  표현방식 2가지

1번.
SELECT A.*, B.연령
FROM GENDER A FULL OUTER JOIN AGE B
         ON A.회원코드 = B.회원코드;

2번.
SELECT A.*, B.연령
FROM GENDER A , AGE B
WHERE A.회원코드(+) = B.회원코드(+);
# Oracle9 버전 이후부터는 양쪽의 (+) 가능

 

 

예제1)

 

 => 정답!!

 

SELECT *

FROM T1 [ LEFT OUTER JOIN ] T2

ON T1.c2 = T2.c2;

 

 

UNION, UNION ALL - 두 플랫폼 기업이 합병할 떄!

UNION : 중복된 레코드를 제거한 후 반환 / UNION ALL : 중복된 레코드를 포함하여 반환

 

예제2)

=> 정답!!

A. JOIN

B. UNION

 

 

예제3)

 

예제4)

 

MINUS - A 회사에만 있는 고객 정보를 추리려고 한다!!!

EQUI JOIN, non-EQUI JOIN

 

CROSS JOIN - A 테이블의 모든 행과 B 테이블의 모든 행을 조인(A 레코드 수 * B 레코드 수)

둘을 곱셈하는 느낌!!

 

 

2) 계층형 조회 & 그룹 함수

 

계층형 조회 : 트리형태의 데이터에 대해 조회를 수행하는 것

 

>> 자식 노드가 없는 데이터 : LEAF 노드

 

예제 1)

=> 정답!!  12

 

계층형 조회 문제는...

조회를 통해 어떻게 레코드가 재배치 되는지 파악하는 것이 중요!!

1. 시작점 잡기

START WITH : 계층 구조가 시작되는 지점(ROOT NODE)을 알려줌, 여기에 적힌 조건에 맞는 레코드가 ROOT(부모), 현 문제에서는 첫번째 레코드가 트리 구조의 ROOT NODE

 

 

2. 재배치 기준

CONNECT BY, PRIOR : 다음 레코드가 어떤 것이 올지 알려줌
- CONNECT BY PRIOR a = b
- CONNECT BY a = PRIOR b
모든 레코드에 대하여 관계성(계층 구조)를 파악하여 테이블을 재배치하기 위함

1) a컬럼과 b컬럼이 동일한 레코드를 간에 계층화가 발생(a=b)
2) 앞선(PRIOR) 레코드의 a컬럼과 동일한 b컿험을 가진 레코드들이 뒤에 온다
CONNECT BY PRIOR a = b : 앞선 레코드의 a = b이다

현 문제에서는 
1) col1과 col2가 동일한 레코드를 간에 계층화가 발생한다
2) ㄱ의 col1과 동일한 col2를 가진 레코드가 다음 순서에 온다

 

 

그럼 ㄴ, ㄷ 중에 뭐가 먼저 올지 어떻게 암?? =? ORDER SIBLINGS BY 가 해결해줌!!

 ORDER SIBLINGS BY col3 
: col3 기준, 오름차순으로 배치 순서 결정
>> col3 : [ㄴ] 12 < [ㄷ] 13 

 

 

예제 2)

 

GROUP 함수

GROUP BY 성별, 연령	1. 성별 * 연령 별 소계
GROUP BY GROUPING SETS( 성별, 연령)	1. 성별 별 소계 2. 연령 별 소계 * 괄호 묶은 집합별 집계 가능
GROUP BY ROLLUP(성별, 연령)	1. 성별 * 연령 별 소계 2. 성별 별 소계 3. 전체 집계
GROUP BY CUBE(성별, 연령)	1. 성별 * 연령 별 소계 2. 성별 별 소계 3. 연령 별 소계 4. 전체 집계 * 그룹화될 수 있는 모든 경우에 대해 생성

 

ex) QUERY 사례

SELECT 성별, 연령, SUM(결제금액)

FROM 결제

GROUP BY GROUPING SETS(성별, 연령대);

 

외우기!!!

• Grouping Sets (set1, set2) :그룹핑 세트 세트(UNION ALL과 같은 결과)
• Rollup(앞, 뒤) : 롤업 압뒤
• CUBE(모든경우) : 큐브

 

예제 3)

 

=> 정답!! 2번

CUBE는 가능한 모든 조합의 경우

 

예제 4)

=> 정답!! 1, 2번

1번 => ROLLUP에 해당하는 설명

2번 => 집계 대상 컬럼 값은 NULL이 아니라 기준이 되는 값으로 설정해야 한다

 

 

3) TOP(N) 쿼리

: 테이블에서 N 개의 행만 가져오고 싶다!

 

1. ORACLE 기준 : ROWNUM 슈도 칼럼
 => 컬럼과 비슷한 성격의 Pseudo Column 으로서, SQL 처리결과 집합의 각 행에 대해 임시적으로 부여되는 일련번호

SELECT *
FROM C_INFO
WHERE ROWNUM <= 100;

** (오류) C_INFO 테이블에서 나이가 어린 100명을 조회하려면?
SELECT *
FROM C_INFO
WHERE ROWNUM <= 100
ORDER BY AGE;
=> C_INFO에서 무작위로 추출된 100개의 레코드를 AGE 순으로 정렬한 것과 같다

** (정답) C_INFO 테이블에서 나이가 어린 100명을 조회하려면?
=> 인라인 뷰에서 먼저 정렬하기!
SELECT *
FROM (SELECT *
            FROM C_INFO
            ORDER BY AGE)
WHERE ROWNUM <= 100;

 

 

2. SQL SERVER 기준 : TOP 절

** C_INFO 테이블에서 나이가 어른 100명을 조회하려면?
SELECT TOP(100)
              NAME, AGE
FROM C_INFO
ORDER BY AGE;

** WITH TIES
=> TOP N의 마지막 행과 동일한 값이 있는 경우 추가 행이 출력됨
SELECT TOP(2) WITH TIES
               NAME, AGE
FROM C_INFO
ORDER BY AGE;

=> C_INFO 테이블에서 AGE가 낮은 2명을 오름차순으로 출력하는데, 같은 AGE인 고객이 잇으면 같이 출력한다
=> ORDER BY가 지정된 경우에만 사용 가능

 

 

3. ROW LIMITING 절(ANSI 표준 SQL)

** C_INFO 테이블에서 나이가 어린 100명을 조회하려면?
SELECT NAME, AGE
FROM C_INFO
ORDER BY AGE FETCH FIRST 100 ROWS ONLY;

* FETCH : 반환할 것 지정해줌
* ONLY : 지정한 만큼(with ties 마지막행 동순위 포함)

- ORACLE 
SELECT *
FROM (SELECT *
             FROM C_INFO
             ORDER BY AGE )
WHERE ROWNUM <= 100;

- SQL SERVER
SELECT TOP(100)
               NAME, AGE
FROM C_INFO
ORDER BY AGE;

**상위 100개 행을 건너뛴 이후의 전체 행 반환하려면?
SELECT NAME, AGE FROM C_INFO ORDER BY AGE OFFSET 100 ROWS;

* OFFSET : 건너뛸 것 지정해줌

 

 

3. 윈도우 함수 & 파티션

 

WINDOW 함수

: 레코드(행) 사이의 관계를 쉽게 정의하기 위한 함수

매장 코드	지역명	메뉴명	판매량	판매순위
101	서울	연어스시	500	1
101	서울	참치스시	400	2
101	서울	와규	300	3
102	부산	연어스시	600	1
102	부산	튀김	300	2

 

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITON BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING] ) FROM 테이블명;

- WINDOW_FUNCTION : 윈도우 함수
- ARGUMENTS : 인수(컬럼명 등 함수의 작업이 이뤄지는 대상)
- PARTITION BY : 테이블의 레코드들을 쪼개는 기준
- ORDER BY : 쪼개진 레코드들 내에서 혹은 전체 테이블에서 레코드들을 어떤 기준으로 정렬할지
- WINDOWING : 함수의 연산 대상이 되는 레코드의 범위를 정함

 

* WINDOW_FUNCTION 종류

- 그룹 내 집계 함수 : COUNT, SUM, MIN, MAX, AVG등

- 그룹 내 순위(RANK) 함수

- 그룹 내 비율 관련 함수

- 그룹 내 행 순서 함수

 

 

WINDOW 함수 - 그룹 내 RANK 함수

RANK	- 동일한 순위에 대해 동일한 순위를 부여한다 - 동일한 순위를 하나의 건수로 계산하지 않는다 (1 / 2, 2 / 4)
DENSE_RANK	- 동일한 순위에 대해 동일한 순위를 부여한다 - 동일한 순위는 하나의 건수로 계산한다 (1 / 2, 2 / 3)
ROW_NUMBER	- 동일한 순위에 대해 고유한 순위를 부여한다 (1 / 2 / 3 / 4 )

**RANK와 DENSE_RANK 차이 비교!!

 

예시1)

 

=> 정답!! 

 

A : DENSE_RANK

B : RANK

C : ROW_NUMBER

 

예시2) 

 

=> 정답!!  1번 (여타 SQL문과 동일하게 ORDER BY를 사용하여 오름차순, 내림차순 출력이 가능 / 순위로 자동으로 정렬되는 것은 아님)

 

예시 3)

 

=> 정답!!

 

SELECT 매장 코드, 지역명, 메뉴명, 판매량

                , DENSE_RANK () OVER (PARTITION BY 지역명 ORDER BY 판매량 DESC) AS

            판매순위

FROM 판매

 

 

 Window 함수 - 그룹 내 비율 함수

PERCENT_RANK	- 값이 아닌, 순서를 대상으로, 파티션 내에서의 순서별 백분율을 조회함
NTILE(n)	- 파티션별로 전체 건수를 n등분한 값을 반환한다. n=4이면 4등분한 것 내에서 몇인지 조회
CUME_DIST	- 파티션 내 전체에서 현재 행의 값 이하인 레코드 건수에 대해 누적 백분율을 조회 - 누적 분포 상에 0~1 값을 가짐

 

 

WIndow 함수 - 그룹 내 행 순서 함수

FIRST_VALUE	- 파티션 내에서 가장 처음 나오는 값 반환 - MIN과 동일한 결과
LAST_VALUE	- 파티션 내에서 가장 마지막에 나오는 값 반환 - MAX와 동일한 결과
LAG(컬럼명, 레코드 위치 차이값)	- 이전 행을 가져온다
LEAD(컬럼명, 레코드 위치 차이값,  null일 경우 대체값)	- 다음(특정 위치의) 행을 가져온다 - default는 1이다

 

예제4)

 

 

=> 정답 (ㄱ : E,  ㄴ : E)

 

 

Window 함수 중 WINDOWING

SELECT WINDOW _FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING] ) FROM 테이블명; 

 

- WINDOWING : 함수의 연산 대상이 되는 레코드의 범위를 정함

 

RANGE	ex ) RANGE BETWEEN 100 AND 200 범위를 지정해줄 때 사용
BETWEEN a AND b	a부터 b까지 윈도우가 적용됨
UNBOUNDED PRECEDING	윈도우의 시작 위치 = 첫 번째 행
UNBOUNDED FOLLOWING	윈도우의 마지막 위치 = 마지막 행
CURRENT ROW	윈도우 시작 위치가 현재 행

 

예제5)

 

=> 정답!! UNBOUNDED PRECEDING

 

 

테이블 파티션

 

- 대용량의 테이블을 여러 개의 데이터 파일에 분리하여 저장하는 것

물리적으로 분리된 데이터 파일에 저장되어 입력 / 수정 / 삭제 / 조회 성능이 향상되고, 독립적 관리가 가능

조회의 범위를 줄이는 효과가 있어 성능이 향상됨

** 막 엄청 중요하진 않음;;

 

RANGE PARTITION	값의 범위를 기준으로 파티션을 나눠 저장하는 방법 ex) 매출액에 50000 이상인 레코드와 미만인 레코드를 별도로 저장
LIST PARTITION	특정 값을 기준으로 분할 ex) 특정 값이 100일 때와 250일 때 각각 다른 데이터파일에 저장
HASH PARTITION	데이터베이스 관리 시스템이 자체적으로 해시 함수를 사용해 분할하고 관리하는 방식