SQLD 2과목 2장 정리

 

Contents

2장 1절 - 서브 쿼리

1. 서브쿼리란?
   • 하나의 SQL문 안에 포함되어 있는 또다른 SQL문을 의미한다.

2. 서브쿼리 사용 시 주의 사항
   • 서브 쿼리는 괄호로 감싸서 기술
   • 서브 쿼리는 단일행(single row) 또는 복수 행(multiple row) 비교 연산자와 함께 사용 가능.
   • 단일행 비교 연산자는 서브쿼리 결과가 반드시 1건 이하여야 하고, 복수행 비교 연산자는 서브 쿼리의 결과 건수와 무관
   • 중첩 서브 쿼리 및 스칼라 서브 쿼리에서는 order by를 사용하지 못한다.

3. 서브 쿼리의 분류
   • 동작 방식에 따른 서브쿼리 분류

     

   • 반환 데이터 형태에 따른 서브쿼리 분류

     

4. 단일 행 서브쿼리
   • 서브쿼리가 단일행 비교 연산자(=, >, <, <> 등)과 함께 사용할 때는 서브쿼리 결과 건수가 반드시 1건 이하여야 함
   • 만일 2건 이상을 반환하려면 SQL문은 실행시간 오류 발생 (아래 예시 기준, 김남일의 동명이인이 있다면 오류가 발생)

     

5. 다중 행 서브쿼리
   • 서브 쿼리의 결과가 2건 이상 반환될 수 있다면, 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)과 함께 사용
   • 다중 행 비교 연산자는 아래와 같음.

     

     

6. 다중 칼럼 서브 쿼리
   • 다중 컬럼 서브쿼리는 서브 쿼리의 결과로 여러개의 칼럼이 반환돼 메인 쿼리의 조건과 동시에 비교하는 것을 의미. SQL Server에서는 미지원
   • 예로 소속팀 별 키가 가장 작은 사람들의 정보를 출력하고자 할 때 아래와 같이 작성 가능

     

7. 연관 서브 쿼리
   • 연관 서브 쿼리는 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리.
   • 예를 들어 선수 자신이 속한 팀의 평균키보다 작은 선수들의 정보를 출력하는 SQL문을 아래와 같이 작성 가능

     

8. 그 밖의 위치에서 사용하는 서브 쿼리
   • SELECT 절에 서브 쿼리 사용

     

   • FROM 절에서 서브 쿼리

     

   • HAVING 절에서 서브 쿼리 사용

     

     "/>

9. 뷰
   • 테이블은 실제로 데이터를 가진 반면 뷰는 실제 데이터를 가지고 있지 않음.
   • 다만 뷰 정의(View Definition)만을 가지고 있음.
   • 뷰의 장점은 아래와 같음.

     

   • 뷰의 생성은 아래와 같이 CREATE VIEW로 생성 가능

     

   • 뷰를 사용할 경우 DBMS가 내부적으로 SQL문을 다시 재작성
   • 뷰를 삭제하기 위해선 DROP VIEW를 사용

2장 2절 - 집합 연산자

1. 정의
   • 2개 이상의 테이블에서 조인을 사용하지 않고 연관 데이터를 조회하는 방법
   • 조인은 조인 조건을 사용해 여러 테이블의 행과 행을 연결하는 반면 집합 연산자는 여러개의 결과 집한 간의 연산을 통해 결합하는 방식
   • 즉, 2개 이상의 질의 결과를 하나의 결과로 반환

2. 집합연산자 종류

   

   

3. UNION과 UNION ALL 을 사용했을 때 결과가 달라짐
   • UNION은 중복값을 제외 / UNION ALL은 전체 표기

2장 3절 - 그룹 함수

1. 그룹 함수 종류
   1. AGGREGATE FUNCTION
      • COUNT, SUM, AVG, MAX, MIN 등의 집계함수 포함
   2. GROUP FUNCTION
      • 결산 개념의 업무가 있을 경우, 여러개의 테이블을 1개로 묶기 위해 GROUPING 함수와 CASE 함수를 사용
      • ROLLUP, CUBE, GROUPING SETS 함수가 있음
   3. WINDOW FUNCTION
      • 분석함수 내지 순위 함수로도 알려진 윈도우 함수는 데이터 웨어하우스에서 발전한 기능

2. ROLL UP 함수
   • 일반적인 GROUP BY SQL 문장에 사용 가능

     

3. CUBE 함수
   • ROLLUP에서는 가능한 SubTotal만을 생성하지만 CUBE에서는 결합 가능한 모든 값에 대해 다차원 집계 생성
   • 가능한 모든 경우의 수에 대해 SUB TOTAL을 생성할 때는 CUBE를 사용하는 것이 바람직하나 부하가 걸리기 때문에 주의 필요

     

4. GROUPING SETS 함수
   • GROUPING SETS 함수를 이용해 GROUP BY를 여러번 반복하지 않고도 원하는 결과 획득 가능
   • GROUPING SETS에 표시된 인수들에 대해 개별 집계를 구할 수 있으며, 이때 표시된 인수 간에는 계층 구조인 ROLLUP과 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 동일

     

2장 4절 - 윈도우 함수

1. 행과 행간의 관계를 쉽게 정의하기 위한 함수

2. 윈도 함수 종류
   • 그룹 내 순위(RANK) 관련 함수 → RANK, DENSE_RANK, ROW_NUMBER
   • 그룹내 집계(AGGREGATE) 관련 함수 → SUM, AMX, MIN, AVG, COUNT
   • 그룹 내 행 관련 함수 → FIRST_VALUE, LAST_VALUE, LAG, LEAD
   • 그룹 내 비율 관련 함수 CUME_DIST, PERCENT_RANK_ NTILE, RATIO_TO_REPORT

3. 윈도우 함수에는 OVER 문구가 키워드로 필수 포함

   

4. RANK 함수 → RANK는 ORDER BY 를 포함한 QUERY문에서 특정 항목에 대한 순위를 구하는 함수다. 이때 특정 범위 내에서 순위를 구할 수도 있고, 전체 데이터에 대한 순위를 구할 수도 있다. 동일한 값에 대해서는 동일한 순위 부여

   

5. DENSE_RANK 함수 → DENS_RANK 함수는 RANK와 유사하나 동일한 순위를 하나의 건수로 취급

   

6. 일반 집계함수
   • SUM 함수
   • MAX 함수
   • MIN 함수
   • AVG 함수
   • COUNT 함수

7. FIRST_VALUE 함수 → FIRST_VALUE 함수를 이용해 파티션 별 윈도우에서 가장 먼저 나온 값을 구한다. SQL SERVER에서는 지원하지 않는다. MIN 함수와 동일한 값을 얻을 수도 있다.

   

8. LAST_VALUE 함수 → LAST_VALUE 함수를 이용해 파티션 별 윈도우에서 가장 나중에 나온 값을 구한다. MAX와 같은 결과를 얻을 수도 있다.

   

9. LAG 함수 → LAG 함수를 이용해 파티션 별 윈도우에서 이전 몇번째의 행의 값을 가져올 수 있다. SQL SERVER에서는 지원하지 않는다.

   

10. LEAD 함수 → LEAD 함수를 이용해 파티션 별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다. SQL SERVER 미지원

    

11. 그룹 내 비율 함수
    • RATIO_TO_REPOSRT → 파티션 내 전체 SUM 값에 대한 행별 칼럼 값의 백분율을 소수점을 구할 수 있다. 결과값은 >0 & ≤ 1의 범위를 가진다. 전체 RATIO의 합은 1일다.

      

    • PERCENT_RANK 함수 → 파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 해서 행의 순서 별 백분율을 구한다.

      

    • CUME_DIST 함수 → 파티션 별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 산출

      

    • NTILE 함수 → NTILE 함수를 이용해 파티션 별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있다.

      

2장 5절 - TOP N 쿼리

1. ROWNUM 슈도 칼럼
   • SQL 철 ㅣ결과 집합의 각 행에 대해 임시로 부여되는 일련번호다. 테이블이나 집합에서 원하는 만큼 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다.

     

2. TOP 절
   • TOP 절을 사용해 집합으로 출력되는 행의 수를 제한 가능

     

3. ROW LIMITING 절
   • ROW LIMITING 절로 TOP N 쿼리 작성 가능

     

2장 6절 - 계층형 질의와 셀프 조인

1. 정의
   • 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용

     

2. 셀프 조인
   • 셀프 조인이랑 동일 테이블 사이의 조인을 의미함. 따라서 FROM 절에 동일 테이블이 2번 이상 타나탐. 동일 테이블 사이의 조인을 사용하면 테이블과 칼럼이 동일하기 때문에 Alias를 사용해야 함

     

 

3. 계층형 질의

• 계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원한다. 여기서는 Oracle과 SQL Server 기준으로 설명한다.

• 오라클

  

• SQL SERVER

  

 

 

2장 7절 - PIVOT 절과 UNPIVOT 절

1. 개요
   • PIVOT은 회전시킨다는 의미를 가지고 있다. PIVOT 절은 행을 열로 회전시키고, UNPIVOT 절은 열을 행으로 회전시킨다

2. PIVOT 절
   • PIVOT절은 행을 열로 전환

     

3. UNPIVOT 절
   • UNPIVOT 절은 PIVOT 절과 반대로 동작하여 열이 행으로 전환된다.

     

2장 8절 - 정규 표현식

1. 개요
   • 정규 표현식(regular expression)은 문자열의 규칙을 표현하는 검색 패턴으로 주로 문자열 검색과 치환에 사용

2. POSIX 연산자
   • 정규 표현식의 POSIX 연산자

     

3. 정규 표현식 조건과 함수
   • REGEXP_LIKE 조건 → REGEXP_LIKE 조건은 source_char가 pattern과 일치하면 TRUE를 아닌 경우에는 FALSE를 반환.

     

   • REGEXP_REPLACE 함수 → REGEXP_REPLACE 함수는 source_char에서 일치한 pattern을 replace_string으로 변경한 문자 값을 반환한다.

     

   • REGEXP_SUBSTR 함수 → REGEXP_SUBSTR 함수는 sourc_char에서 일치한 pattern을 반환한다.

     

   • REGEXP_IUNSTR 함수 → soucr_char에서 일치한 patternd의 시작 위치를 정수로 반환한다.

     

   • REGEXP_COUNT 함수 → REGEXP_COUNT 함수는 source_char에서 일치한 pattern의 개수를 반환