[ SQL 개발자 ] 과목 Ⅱ - SQL 활용

📌 목차

---

 과목 Ⅱ - SQL 기본 및 활용 

2. SQL 활용

◦ 표준 조인
◦ 집합 연산자
◦ 계층형 질의와 셀프 조인
◦ 서브쿼리
◦ 그룹 함수
◦ 윈도우 함수
◦ DCL
◦ 절차형 SQL

---

💡 표준 조인

 

1. NATURAL JOIN : 같은 이름을 가진 칼럼 전체에 대한 등가 조인

- USING 조건절이나 ON 조건절 사용 불가

- 같은 데이터 유형 컬럼만 조인 가능

- 앨리어스나 테이블명 사용 불가

- SQL : SELECT 컬럼 FROM 테이블1 NATURAL JOIN 테이블2;

 

2. INNER JOIN : 행에 동일한 값이 있는 컬럼 조인

- USING 조건절이나 ON 조건절 사용 필수

- 두 테이블에 동일 이름 컬럼이 있을 경우 SELECT절에 앨리어스 필수

- SQL : SELECT 칼럼 FROM 테이블1 A INNER JOIN 테이블2 B ON A.컬럼 = B.컬럼 (ANSI/ISO 표준)

 

3. USING 조건절 : 같은 이름을 가진 컬럼 중 등가 조인 대상 컬럼에 대해서 등가 조인

- SQL Server 에서는 지원하지 않음

- 조건절에 앨리어스나 테이블명 불가

SQL : SELECT 컬럼 FROM 테이블1 A JOIN 테이블2 B USING (컬럼명);

 

4. ON 조건절 : 다른 이름을 가진 컬럼 간 조인 가능

- 앨리어스나 테이블명 필수

- 괄호는 의무사항 아님

SQL : SELECT 컬럼 FROM 테이블1 A JOIN 테이블2 ON (A.컬럼 = B.컬럼);

 

5. CROSS JOIN : 가능한 모든 조합으로 조인

- 양쪽 집합의 M*N건의 데이터 조합이 발생

- 조인 조건이 없을 때 발생

SQL : SELECT 컬럼 FROM 테이블1, 테이블2;

 

6. OUTER JOIN : 조인 조건에서 행에 동일한 값이 없는 컬럼 조인

- USING 조건절이나 ON 조건절 필수

- LEFT OUTER JOIN : 좌측 테이블 데이터 조회 후 우측 테이블 조인 대상 데이터 조회

  - SQL : SELECT 컬럼 FROM 테이블1 A LEFT OUTER JOIN 테이블2 B ON A.컬럼 = B.컬럼;

  - SQL : SELECT 컬럼 FROM 테이블1 A, 테이블2 B A.컬럼 = B.컬럼(+); (ORACLE)

- RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대 

  - SQL : SELECT 컬럼 FROM 테이블1 A RIGHT OUTER JOIN 테이블2 B ON A.컬럼 = B.컬럼;

  - SQL : SELECT 컬럼 FROM 테이블1 A, 테이블2 B A.컬럼(+) = B.컬럼; (ORACLE)

- FULL OUTER JOIN : 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성

  - 중복 데이터 제거 (중복 데이터 1행 처리)

  - SQL : SELECT 컬럼 FROM 테이블1 A FULL OUTER JOIN 테이블2 B ON A.컬럼 = B.컬럼;

---

💡 집합 연산자

 

1. 일반 집합 연산 : 조인 없이 여러 테이블의 관련 데이터를 조회

일반 집합 연산	의미
UNION	합집합 테이블을 하나로 합치면서 중복된 데이터 제거 (중복 행은 1개 처리) 정렬(Sort) 과정을 발생 시킨다 SQL : SELECT 컬럼명 FROM 테이블명 A WHERE 조건절  UNION  SELECT 컬럼명 FROM 테이블명 B WHERE 조건절
UNION ALL	합집합  중복 행도 표시  정렬 안함 SQL : SELECT 컬럼명 FROM 테이블명 A WHERE 조건절  UNION ALL SELECT 컬럼명 FROM 테이블명 B WHERE 조건절
INTERSECTION	교집합 SQL : SELECT 컬럼 FROM 테이블명 A WHERE 조건절 INTERSECT SELECT 컬럼명 FROM 테이블명 B WHERE 조건절
DIFFERENCE	차집합 MINUS(오라클), EXCEPT(SQL Server) SQL : SELECT 컬럼 FROM 테이블명 A WHERE 조건절 MINUS (EXCEPT) SELECT 컬럼명 FROM 테이블명 B WHERE 조건절
PRODUCT	곱집합 (생길 수 있는 모든 데이터 조합) CROSS JOIN

 

2. 순수 관계 연산

순수 관계 연산	의미
SELECT	조건에 맞는 행 조회 (WHERE절)
PROJECT	조건에 맞는 컬럼 조회 (SELECT절)
JOIN	여러 JOIN
DIVIDE	공통요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

 

---

💡 계층형 질의와 셀프 조인

 

1. 계층형 질의(Hierarchical Query) : 계층형 데이터를 조회하기 위해 사용 (Oracle에서 지원)

- 계층형 데이터 : 엔티티를 순환관계 데이터 모델로 설계할 때 발생

계층형 질의	의미
START WITH	계층 구조 전개의 시작 조건 지정
CONNECT BY	다음 전개에 전개 될 자식 데이터 지정
PRIOR	CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정 - PRIOR 자식 = 부모 : 부모에서 자식 방향, 순방향 전개 - PRIOR 부모 = 자식 : 자식에서 부모 방향, 역방향 전개
NOCYCLE	이미 조회된 데이터를 조회하면 CYCLE 형성 NOCYCLE 사용시 동일 데이터 전개되지 않음
ORDER SIBLINGS BY	동일한 LEVEL인 형제노드 사이 정렬 수행
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출(필터링)
LEVEL	루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
CONNECT_BY_ROOT	최상위 계층 값 표시
CONNECT_BY_ISLEAF	최하위 계층 값 표시 해당 데이터가 리프 데이터면1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	순환구조의 발생지점까지만 전개 해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시

- LPAD : 계층형 조회 결과를 명확하게 하기 위해 사용 (LEVEL 값을 이용하여 결과 데이터 정렬) ( ex : LPAD(' ', 4) )

 

2. SQL Server 계층형 질의 : CTE(Common Table Expression)로 재귀 호출

 

3. 셀프 조인 : 한 테이블 내에서 두 컬럼이 연관 관계가 있는 경우 (동일 테이블 사이의 조인)

- FROM 절에 동일 테이블이 2번 이상 나타난다

- 앨리어스 필수 사용

 

---

💡 서브쿼리

 

1. 서브쿼리(Subquery) : 하나의 SQL문 안의 SQL문

주의사항

- 서브쿼리를 괄호로 감싸서 사용

- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능

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

- 서브쿼리에서는 ORDER BY를 사용 불가

- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

 

2. 반환 데이터 형태에 따른 분류

- 단일 행 서브쿼리 : 실행 결과가 1건 이하인 서브쿼리

  - 단일 행 비교 연산자와 함께 사용
  - =, <, >, <=, >=, <>

- 다중 행 서브쿼리 : 실행 결과가 여러 건인 서브쿼리

  - 다중 행 비교연산자와 함께 사용

다중 행 비교 연산자	의미
IN	서브쿼리 결과 중 하나의 값이라도 동일하다는 조건
ANY	서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건
ALL	서브쿼리의 모든 결과값을 만족한다는 조건
EXISTS	서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건(하나라도 존재하면 참) WHERE EXISTS (SELECT ~)

- 다중 컬럼 서브쿼리 : 실행 결과로 여러 컬럼 반환 

   - 주로 메인쿼리의 조건과 비교하기 위해 사용 (비교하고자 하는 컬럼 개수와 위치가 동일해야 한다)

 

3. 스칼라 서브쿼리 : 값 하나를 반환하는 서브 쿼리

- SELECT절에 사용하는 서브쿼리

 

4. 뷰(View) : 가상의 테이블

- FROM절에 사용하는 뷰는 인라인 뷰(Inline View)

장점

- 독립성 : 테이블 구조 변경 자동 반영

- 편리성 : 쿼리를 단순하게 작성할 수 있음, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용

- 보안성 : 뷰를 생성할 때 칼럼을 제외할 수 있음

뷰(View)

- 테이블을 참조하여 원하는 컬럼만 조회 (보안성)

- 데이터 딕셔너리에 SQL형태로 저장되어 실행시 참조

- 뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력, 수정, 삭제에는 제약이 있다

- ALTER문을 통해 변경할 수 없고, 변경을 원한다면 삭제 후 재생성 필요

 

5. WITH : 서브쿼리를 이용하여 뷰로 사용할 수 있는 구문

SQL : WITH 뷰명 AS (SELECT * FROM 뷰명 WHERE ~ );

 

---

💡 그룹 함수

 

1. ANSI/ISO 표준 데이터 분석 함수

- 집계 함수

- 그룹 함수

- 윈도우 함수

 

2. 그룹 함수(Group Function) : 합계 계산 함수, NULL을 뺴고 집계, 결과값 없는 행은 출력 안함

그룹 함수	의미
ROLLUP	GROUP BY로 묶인 컬럼의 소계(Subtotal) 계산 Subtotal을 생성하기 위해 사용 Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성 GROUP BY ROLLUP(E1, E2) >> E1과 E2별 소계 / E1별 소계 / 총합계
GROUPING SET	특정 항목에 대한 소계(Subtotal) 계산 GROUP BY 컬럼 순서와 무관하게 개별적으로 처리 GROUP BY GROUPING SETS(E1, E2) >> E1별 소계 / E2별 소계
CUBE	결합 가능한 모든 값에 대하여 다차원 집계를 생성 ROLLUP에 비해 시스템에 부하 심함 GROUP BY CUBE(E1, E2) >> E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계

- GROUPING : 그룹 함수에서 생성되는 합계를 구분해주는 함수

소계나 합계가 계산되면 1 아니면 0 반환

 

---

💡 윈도우 함수

 

1. 윈도우 함수(Window Function) : 여러 행 간의 관계 정의 함수

- 중첩 불가

- 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수
- OVER 문구가 키워드로 필수 포함
- 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다. 
- ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.

 

2. 윈도우 함수 문법

SQL : SELECT 윈도우함수(A) OVER (PARTITION BY 컬럼 ORDER BY 컬럼 윈도잉절) FROM 테이블명;

- PARTITION BY : 그룹핑 기준 (전체 집합을 기준에 의해 소그룹으로 나눈다)

- ORDER BY : 순위 지정 기준

- 윈도잉절(WINDOWING) : 함수의 대상이 되는 행 범위 지정

   - ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타냄

   - BETWEEN A AND B : 구간 지정

	의미
N PRECEDING	N번째 앞 행
N FOLLOWING	N번째 뒤 행
UNBOUNDED PRECEDING	첫 행
UNBOUNDED FOLLOWING	끝 행
CURRENT ROW	현재 행

   - SQL Server에서는 지원하지 않음

 

3. 순위 함수

- RANK : 중복 순위 포함, 동일한 값이면 중복 순위 부여, 다음 순위는 해당 개수만큼 건너 뛰고 반환

  ( ex : 1, 2, 2, 4 .... )

- DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음

  ( ex : 1, 2, 2, 3 .... )

- ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유 순위 부여

  ( ex : 1, 2, 3, 4 .... )

 

4. 일반 집계 함수

- SUM, MAX, MIN, AVG, COUNT

 

5. 행 순서 함수 : (SQL Server 지원하지 않음)

- FIRST_VALUE : 파티션별 윈도우에서 먼저 나온 값을 구한다 (MIN 함수를 통해 같은 결과 구할 수 있음)

- LAST_VALUE : 파티션별 윈도우에서 마지막에 나온 값을 구한다 (MAX 함수를 통해 같은 결과 구할 수 있음)

- LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다

- LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다 ( LEAD(E,A)는 E에서 A번째 행의 값을 호출하는 형태로도 쓰인다, A의 기본값 1 )

 

6. 비율관련 함수

- PERCENT_RANK() : 백분율 순서, 제일 먼저 나온 것을 0, 제일 늦게 나온 것은 1로 하여 백분율을 소수점으로 구한다

- CUME_DIST() : 현재 행 이하 값을 포함한 누적 백분율, 누적 분포상의 위치를 0~1 사이의 값을 가진다

- RATIO_TO_REPORT : 총합계에 대한 값의 백분율, 소수점까지 조회

- NTILE(A) : 파티션별 전체 건수를 인수 값으로 N등분 (1부터 시작)

 

---

💡 DCL

 

1. DCL : 유저를 생헝하거나 권한을 제어하는 명령어

- GRANT : 권한 부여

SQL : GRANT 권한 ON 오브젝트 TO 유저명; 

WITH GRANT OPTION : 특정 사용자에게 권한 부여 가능, 권한 회수 시 연쇄 회수

WITH ADMIN OPTION : 테이블에대한 모든 권한, 권한 회수 시 연쇄 회수 진행되지 않음

- REVOKE : 권한 제거

SQL : REVOKE 권한 ON 오브젝트 TO 유저명;

 

2. 권한(Privileges)

- SELECT, INSERT, UPDATE, DELETE, ALTER, ALL : DML 관련 권한

- REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한

- INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

 

3. Oracle 유저

- SCOTT : 테스트용 샘플 유저

- SYS : DBA 권한이 부여된 최상위 유저

- SYSTEM : DB의 모든 시스템 권한이 부여된 DBA

 

4. ROLE : 권한의 집합

- 유저에게 맞는 권한을 한번에 부여하기 위해 사용

 

---

💡 절차형 SQL

 

1. 절차형 SQL : 일반적인 개발 언어처럼 절차지향적인 프로그램을 작성할 수 있도록 제공

- SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성

 

2. 저장 모듈

- PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램

- 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

 

3. PL/SQL

- Block 구조로 되어있어 각 기능별로 모듈화 가능

- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환

- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능

- PL/SQL은 Oracle에 내장되어 있으므로 호환성 높음

- 응용 프로그램의 성능을 향상

- Block 단위로 처리 -> 통신량을 줄일 수 있다.

Declare(선언부) : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부

Begin(실행부) : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용 필요한 로직 처리

Exception(예외 처리부) : 블록에 발생한 에러 처리 로직 정의, 선택 항목

 

4. T-SQL : SQL Server를 제어하는 언어

 

5. 사용자 정의 함수 : 절차형 SQL을 로직과 함께 DB내에 저장해 놓은 명령문 집합

- RETURN을 통해 반드시 하나의 값 반환 

 

6. 프로시저(Procedure) : EXCUTE 프로시저명 으로 실행

- BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용 가능

 

7. 트리거(Trigger) : DML문이 수행되었을 때 자동으로 동작하는 프로그램

- BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용 불가