2과목 : SQL 기본 및 활용

데이터베이스와 DBMS

• 데이터베이스
  • 데이터의 집합이며 데이터의 집합 형식을 갖추지 않아도 엑셀 파일을 모아 둔다면 그것 또한 데이터베이스이다
• DBMS
  • 데이터베이스를 관리하기 위한 시스템이다 (Oracle, MySQL등)

관계형 데이터베이스 (RDB)

• 데이터를 행(Row)과 열(Column)로 구성된 테이블(Table) 형태로 저장하고,
  데이터 간의 관계(Relation)를 공통 속성(키, Key)을 통해 표현하는 데이터베이스 모델입니다.
• 구성 요소
  • 계정 : 데이터의 접근 제한을 위한 여러 업무별/시스템별 계정이 존재한다
  • 테이블 : DBMS 의 DB 안에서 데이터가 저장되는 형식이다
  • 스키마 : 테이블이 어떠한 구성으로 되어있는지, 어떠한 정보를 가지고 있는지에 대한 기본적인 구조를 정의한다
• 특징
  • 데이터의 분류, 정렬, 탐색 속도가 빠름
  • 신뢰성이 높고, 데이터의 무결성 보장
  • 기존의 작성된 스키마를 수정하기 어려움
  • 데이터베이스의 부하를 분석하는 것이 어려움

테이블

• 엑셀에서의 워크시트처럼 행(로우)과 열(컬럼)을 갖는 2 차원 구조로 구성, 데이터를 입력하여 저장하는 최소 단위이다
• 특징
  • 하나의 테이블은 반드시 하나의 유저(계정) 소유여야 한다
  • 테이블간 관계는 일대일(1:1), 일대다(1:N), 다대다(N:N)의 관계를 가질 수 있다
  • 테이블명은 중복될 수 없지만, 소유자가 다른 경우 같은 이름으로 생성 가능하다

데이터 무결성 (integrity)

• 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하는 것이다
• 데이터베이스에 저장된 값과 그것이 표현하는 현실의 비즈니스 모델의 값이 일치하는 정확성을 의미한다
• 데이터 무결성을 유지하는 것이 데이터베이스 관리시스템에 중요한 기능이다
• 특징
  ① 개체 무결성 : 테이블의 기본키를 구성하는 컬럼(속성)은 NULL 값이나 중복값을 가질 수 없다
  ② 참조 무결성 : 외래키 값은 NULL 이거나 참조 테이블의 기본키 값과 동일해야 한다
  (외래키란 참조 테이블의 기본키에 정의된 데이터만 허용되는 구조이므로)
  ③ 도메인 무결성 : 주어진 속성 값이 정의된 도메인에 속한 값 이어야 한다
  ④ NULL 무결성 : 특정 속성에 대해 NULL 을 허용하지 않는 특징이다

🤪 SQL 정리

• 종류

구분	종류
DDL (Data Definition Language)	CREATE, DROP, ALTER, TRUNCATE
DML (Data Maniplation Language)	SELECT, INSERT, DELETE, UPDATE, MERGE
DCL (Data Control Language)	GRANT, REVOKE
TCL (Transaction Control Language)	COMMIT, ROLLBACK

• 중요한 함수 문법 정리

  • SUBSTR(str, m, n) : str의 m위치부터 n개의 문자열 추출
  • INSTR(str, 찾는 문자열, m, n) : str의 m위치부터 n번째 찾는 문자열의 인덱스 출력
  • TRIM(str, x) : str의 양쪽 x제거, LTRIM : 왼쪽 제거, RTRIM : 오른쪽 제거
  • CONCAT(x, y) : x와 y 결합
  • REPLACE(str, x, y) : str의 x를 y로 변경, y생략시 x 삭제함
  • TRUNC(num, n) : 소수점 n자리뒤는 버림

  📘 NULL 관련 함수 및 조건문 함수

함수명	함수 기능
DECODE(대상, 값1, 리턴1, 값2, 리턴2, …, 그외리턴)	대상이 값1이면 리턴1, 값2와 같으면 리턴2 … 그 외에는 그외리턴값 리턴
NVL(대상, 치환값)	대상이 널이면 치환값으로 치환하여 리턴
NVL2(대상, 치환값1, 치환값2)	대상이 널이면 치환값2로 치환, 널이 아니면 치환값1으로 치환하여 리턴
COALESCE(대상1, 대상2, …, 그외리턴)	대상들 중 널이 아닌 값 출력 (가장 첫번째부터)
ISNULL(대상, 치환값)	대상이 널이면 치환값이 리턴됨
NULLIF(대상1, 대상2)	두 값이 같으면 널 리턴, 다르면 대상1 리턴
CASE문	조건별 치환 및 연산 수행

👀 JOIN (조인)

• 조인 종류

  • 조인 형태에 따라

    • EQUI JOIN(등가 JOIN) : JOIN 조건이 동등 조건인 경우
    • NON EQUI JOIN: JOIN 조건이 동등 조건이 아닌 경우

  • 조인 결과에 따라

    • INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우
    • OUTER JOIN: JOIN 조건에 성립하지 않는 데이터도 출력하는 경우
      (LEFT/ RIGHT/ FULL OUTER JOIN 으로 나뉨)
    • NATURAL JOIN : 조인조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인
    • CROSS JOIN : 조인조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인
    • SELF JOIN: 하나의 테이블을 두 번 이상 참조하여 연결하는 조인
      

• INNER JOIN

  • 내부 조인이라고 하며, 조인 조건이 일치하는 행만 추출
  • ON 조건절이나 USING 조건절 사용

• NATURAL JOIN

  • 두 테이블 간의 동일한 이름을 가지는 모든 컬럼들에 대해 EQUI JOIN 을 수행
  • 조건절(ON, USING) 사용 불가
  • JOIN 에 사용된 컬럼들은 데이터 유형이 동일해야 하며 WHERE절에 별칭 사용불가 (DEPTNO = 10 가능 , EMP.DEPTNO = 10 불가)

• CROSS JOIN

  • 테이블 간 JOIN 조건이 없는 경우 생성 가능한 모든 데이터들의 조합
    (Cartesian product(카타시안곱) 출력)
  • 양쪽 테이블 행의 수의 곱한 수의 데이터 조합 발생(m * n)

• OUTER JOIN

  • INNER JOIN 과 대비되는 조인방식
  • JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용

  1. LEFT OUTER JOIN

  • FROM 절에 나열된 왼쪽 테이블에 해당하는 데이터를 읽은 후, 우측 테이블에서 JOIN 대상 읽어옴
  • 즉, 왼쪽 테이블이 기준이 되어 오른쪽 테이블 데이터를 채우는 방식
  • 우측 값에서 같은 값이 없는 경우 NULL 값으로 출력

  2. RIGHT OUTER JOIN

  • LEFT OUTER JOIN 의 반대
  • 즉, 오른쪽 테이블 기준으로 왼쪽 테이블 데이터를 채우는 방식
  • FROM 절에 테이블 순서를 변경하면 LEFT OUTER JOIN 으로 수행 가능

  3. FULL OUTER JOIN

  • 두 테이블 전체 기준으로 결과를 생성하여 중복 데이터는 삭제 후 리턴
  • LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION 연산 리턴과 동일함
  • ORACLE 표준에는 없음

🐥 서브쿼리 (SUBQUERY)

• 하나의 SQL 문 안에 포함된 또 다른 SQL 문을 의미
• 반드시 괄호로 묶어야 함
• 분류

  • 동작 방식

    1. 비연관 서브쿼리

       • 서브쿼리에 메인쿼리(외부쿼리) 테이블의 컬럼을 포함하지 않은 형태의 서브쿼리
       • 서브쿼리가 메인쿼리의 값을 참조하지 않고 독립적으로 실행됨
       • 서브쿼리는 한 번만 실행됨

    2. 연관 서브쿼리

       • 서브쿼리에 메인쿼리(외부쿼리) 테이블의 컬럼을 포함하는 형태의 서브쿼리
       • 서브쿼리가 메인쿼리의 컬럼을 참조하고 있기 때문에 서브쿼리의 실행이 메인쿼리와 독립적이지 않음
       • 메인 쿼리의 각 행에 대해 서브쿼리가 실행됨

  • 위치

    1. 스칼라 서브쿼리

       • SELECT 절에 오는 서브쿼리
       • 스칼라 서브쿼리를 사용한 조인 시 OUTER JOIN 이 기본(조인 조건에 일치하는 대상이 없어도 생략되지 않고 NULL 로 출력됨)

    2. 인라인 뷰

       • FROM 절에 오는 서브쿼리

    3. WHERE절 서브쿼리

       • 가장 일반적인 서브쿼리

  • 리턴 개수

    1. 단일 행 서브쿼리

    • 리턴되는 행이 하나인 서브쿼리
    • = , < , > 등의 비교연산자 사용

    2. 다중 행 서브쿼리

    • 리턴되는 행이 두 개 이상인 서브쿼리
    • IN, ANY, ALL, EXISTS, NOT EXISTS 등 연산자 사용

    3. 다중 컬럼 서브쿼리

    • 서브쿼리 결과가 여러 컬럼을 리턴하는 형태
    • 메인쿼리와 비교하는 컬럼이 2 개 이상인 형태

집합 연산자

구분	종류
합집합	UNION, UNION ALL
교집합	INTERSECT
차집합	MINUS

● 집합 연산자 사용시 주의 사항

1. 두 집합의 컬럼 수 일치
2. 두 집합의 컬럼 순서 일치
3. 두 집합의 각 컬럼의 데이터 타입 일치
4. 각 컬럼의 사이즈는 달라도 됨
5. 개별 SELECT 문에 ORDER BY 전달 불가(GROUP BY 전달 가능)

GROUP BY FUNCTION

• GROUP BY절에 사용하는 함수
• 여러 GROUP BY 결과를 동시에 출력하는 기능

1. GROUPING SETS(X, Y...)

• X, Y 별 그룹 연산 결과 출력
• 나열 순서 상관없음
• 전체 총계는 출력하지 않음
• NULL이나 () 사용해서 전체 총계 출력할 수는 있다

2. ROLLUP(X, Y)

• X, (X, Y), 전체 총계 결과 출력
• 나열 순서 중요 인수 중 앞에가 고정됨

3. CUBE(X, Y)

• X, Y, (X,Y), 전체 총계 결과 출력
• 나열 순서 상관없음 - 어차피 X, Y 둘 다 출력해서

🐼 윈도우 함수 (WINDOW FUNCTION)

• 서로 다른 행의 비교나 연산을 위해 만든 함수
• GROUP BY 를 쓰지 않고 그룹 연산 가능
• 문법

• 종류

  • SUM OVER() : 파티션 별 총합, 그룹별 총합 출력 가능
  • AVG OVER() : 파티션 별 평균
  • MIN/MAX OVER() : 파티션 별 최소값/최대값
  • COUNT OVER() : 파티션 별 개수

• 연산 대상

  1) ROWS : 연산을 할 행을 지정
  2) RANGE(DEFALUT) : 연산을 할 범위를 지정

• 범위

  • 시작 지점

    1) CURRENT ROW : 현재 행부터
    2) UNBOUNDED PRECEDING : 처음부터(DEFAULT)
    3) N PRECEDING : N 이전부터

  • 마지막 지점

    1) CURRENT ROW : 현재 범위까지(DEFAULT)
    2) UNBOUNDED FOLLOWING : 마지막까지
    3) N FOLLOWING : N 이후까지

• 순위 함수

  • RANK()

    • 전체 또는 특정 그룹 내 값의 순위 확인
    • ORDER BY 절 필수
    • 1, 1, 3, 4, 4, 4, 7 같은 느낌

  • DENSE_RANK()

    • 누적순위
    • 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
    • 1, 1, 1, 2, 2, 2, 3, 3 같은 느낌

  • ROW_NUMBER

    • 연속된 행 번호
    • 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴
    • 1, 2, 3, 4, 5, 6 같은 느낌

• 행 순서 관련 함수

  • LAG(), LEAD()

    • 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
    • ORDER BY 절 필수

  • FIST_VALUE(), LAST_VALUE()

    • 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
    • 순서와 범위 정의에 따라 최솟값과 최댓값 리턴 가능
    • PARTITION BY, ORDER BY 절 생략 가능

  • NTILE()

    • 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위함 함수
    • 그룹 번호가 리턴됨
    • ORDER BY 필수
    • PARTITION BY 를 사용하여 특정 그룹을 또 원하는 수 만큼 그룹 분리 가능
    • 총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리됨
      ex) 11 명 3 개 그룹 분리 시 -> 4, 4, 3 으로 나뉨

• 비율 관련 함수

  • RATIO_TO_REPORT()

    • 각 값이 전체 합에서 차지하는 비율을 계산
    • ORDER BY 절 사용 불가
      

  • CUME_DIST()

    • 해당 값보다 작거나 같은 값의 비율을 계산
    • ORDER BY 절 필수

  • PERCECT_RANK()

    • 주어진 값이 데이터 집합 내에서 상대적으로 어디에 위치하는지를 비율(퍼센트)로 나타내는 함수
    • 0 과 1 사이의 값으로 표현
    • ORDER BY 절 필수

TOP N QUERY

• 페이징 처리를 효과적으로 수행하기 위해 사용

• 전체 결과에서 특정 N 개 추출

• 추출 방법

  1. ROWNUM

  • 출력된 데이터 기준으로 행 번호 부여
  • 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음(=연산 불가)
  • 첫 번째 행이 증가한 이후 할당되므로 '>' 연산 사용 불가(0 은 가능)

  2. RANK

  • RANK() + ORDER BY + ROWNUM으로 상위/하위 N개 추출

  3. FETCH

  • 출력될 행의 수를 제한하는 절
  • ORACLE 12C 이상부터 제공(이전버전에는 ROWNUM 주로 사용)
  • SQL Server 사용 가능
  • ORDER BY 절 뒤에 사용(내부 파싱 순서도 ORDER BY 뒤)

  4. TOP N(SQL Server)

  • SQL Server 에서의 상위 n 개 행 추출 문법
  • 서브쿼리 사용 없이 하나의 쿼리로 정렬된 순서대로 상위 n 개 추출 가능
  • WITH TIES 를 사용하여 동순위까지 함께 출력 가능