[ SQLD 이론 심화] Chapter 04. SQL 활용

1. 표준 조인

• 일반 집합 연산자 vs SQL
  UNION연산 : UNION 기능으로 구현. 
  INTERSECTION : INTERSECT 기능으로 구현
  DIFFERENCE : EXCEPT 기능으로 구현
  PRODUCT : CROSS JOIN 기능으로 구현
  
  
• 순수 관계 연산자 vs SQL
  SELECT : WHERE절로 구현
  PROJECT : SELECT절로 구현 
  JOIN : JOIN절로 구현 
  DIVIDE : 현재 사용되지 않음
  
  
• 조인의 형태
  INNER JOIN : JOIN조건에서 동일한 값이 있는 행만 반환
  NATURAL JOIN : 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI 조인 수행
  USING 조건절 : NATURAL JOIN 에서 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 가능
  ON 조건절 : 조인 조건
  CROSS JOIN : JOIN조건이 없는 경우 생길 수 있는 모든 데이터의 조합. (PRODUCT의 개념)
  OUTER JOIN : 외부 조인이라 불리며 JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용
  
  

2. 집합 연산자 (SET OPERATOR)

 

• 종류
  UNION : 여러 개의 SQL 문의 결과에 대한 합집합. 중복된 행은 한개의 행으로 출력됨
  UNION ALL : 여러 개의 SQL 문의 결과에 대한 합집합. 중복된 행도 그대로 표시함.
  INTERSECT : 교집합. 중복된 행은 한개로 출력
  EXCEPT : 아래의 SQL 문의 집합을 뺸 결과를 표시함

 

3. 계층형 질의와 SELF 조인

 

• 계층형 질의
  테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용함
  
  
• 오라클 계층형 SQL
  SELECT FROM TABLE
  WHERE 
  START WITH 조건 : 루트 데이터를 지정
  CONNECT BY : 다음에 전개될 자식 데이터를 지정.
  - PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터방향으로 전개하는 순방향 전개를 한다
  - PRIOR 부모 = 자식 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터방향으로 전개하는 역방향 전개를 한다
  - NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않음
  ORDER SIBLINGS BY 칼럼 : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행함

 

4. 서브 쿼리

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문

메인쿼리의 칼럼을 모두 사용할 수 있음. (메인쿼리는 서브쿼리 컬럼 사용 못함)

단일 행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 서브 쿼리의 결과 건수와 상관 없음

ORDER BY는 사용 못함

 

• 반환 형태에 따른 서브 쿼리 분류
  단일 행 서브 쿼리 : 실행 결과가 항상 1건 이여야 함
  다중 행 서브 쿼리 : 실행 결과가 여러건 이여야 함
  다중 칼럼 서브 쿼리 : 여러 칼럼이 반환됨. 메인쿼리의 조건 절에 여러 칼럼을 동시에 비교 가능하며 순서가 동일해야 함.
• 뷰 사용의 장점 
  - 독립성
  - 편리성
  - 보안성

 

5. 그룹 함수

 

그룹 함수를 이용하여 특정 집합의 소계, 중계, 합계, 총 합계 구할 수 있음

 

• 그룹 함수의 종류
  - ROLLUP : 소 그룹간의 소계 나타냄. ROLLUP함수 내에 인자로 지정된 GROUPING 칼럼은 SUBTOTAL을 생성하는 데 사용됨
  - CUBE : 다차원적 소계를 계산
  - GROUPING SETS : 특정 항목에 대한 소계를 계산하는 기능
  
  

6. 윈도우 함수

행과 행간의 관계에서 다양한 연산 처리를 할 수 있는 함수
일반 함수와 달리 중첩하여 호출 될 수는 없음.

 

• 순위관련함수
  - RANK() OVER(PARTITION BY "부분집합" ORDER BY 정렬순서) : 순서출력
    (DENSE_RANK : 중복이 있더라도 오름차순에서 빈 숫자가 없게 출력함)
  
  
• 집계관련함수
  SUM, MAX, MIN, AVG, COUNT
  
  
• 행순서 관련함수
  - FIRST_VALUE, LAST_VALUE
  - LAG
  - LEAD
  
  

7. DCL (DATA CONTROL LANGUAGE)

유저를 생성하고 권한을 제어 할 수 있는 명령어

 

• 오라클에서 제공하는 유저들
  SCOTT : 테스트용 샘플 유저
  SYS : DBA 권한을 부여 받은 유저
  SYSTEM : SYSTEM 데이터베이스의 모든 시스템 권한을 부여 받은 유저
  
  
• 유저 생성
  CREATE USER "사용자" IDENTIFIED BY "비밀번호";
  
  
• 접속 권한 주기
  GRANT CREATE SESSION TO "사용자";
  
  
• ROLE을 이용한 권한 부여
  유저를 생성하면 다양한 많은 권한들을 부여 해야 함
  
  
• 롤을 생성한 후 롤 권한 부여
  CREATE ROLE "롤"
  GRANT "권한" TO "롤";
  GRANT "롤" TO "사용자"
  
  

8. 절차형 SQL

절차형 SQL 사용 시 연속적인 실행이나 조건에 따른 분기 처리를 수행하는 모듈을 생성할 수 있다.

이러한 종류는 사용자 정의 함수, 프로시저, 트리거, PL/SQL이 있음.

 

• PL/SQL
  Block 구조로 되어 있고 SQL문, IF, LOOP 등이 존재함. 다양한 모듈 개발 가능
  
  DECLARE (선언부) : BEGIN~END에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언
  BEGIN (실행부) : 개발자가 처리하고자 하는 SQL문과 필요한 로직이 정의되는 실행 부
  EXCEPTION(예외 처리 부) : 
  END
  
  
• 사용자 정의 함수
• 트리거
  특정한 테이블에 INSERT, UPDATE, DELETE를 수행할 때 DBMS에서 자동으로 동작하도록 작성된 프로그램
  직접 호출하는 것이 아니고 DBMS가 자동적으로 수행함
  테이블과 뷰, DB 작업을 대상으로 정의 할 수 있으며 전체 트랜잭션 작업에 대해 발생되는 트리거와 각 행에 대해 발생되는 트리거가 있음
  COMMIT/ROLLBACK 실행 안됨