[sqld] SQL 자격검정 실전문제 핵심정리 (4장 SQL 활용) (6.10 시험 대비용 정리)

참고자료 : SQL 자격검정 실전문제

제4장 SQL 활용

제1절 표준 조인

제2절 집합 연산자

제3절 계층형 질의와 셀프 조인

제4절 서브쿼리

제5절 그룹 함수

제6절 윈도우 함수

제7절 DCL

제8절 절차형 SQL

 

순수 관계 연산자 (SPJD)

• SELECT, PROJECT, JOIN, DIVIDE

ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

• INNER JOIN : INNER JOIN을 여러 번 사용할 시 INNER JOIN ~ ON , INNER JOIN ~ ON과 같이 작성해야 함
• NATURAL JOIN
• USING 조건절 : 두 테이블간 동일한 이름을 갖는 칼럼들에 대해 EQUI JOIN 수행 -> USING(칼럼명)
• ON 조건절 
• CROSS JOIN(M*N) = (Cartesian Product) : 발생 가능한 모든 경우의 수에 대해 행이 출력됨 => PRODUCT 연산
• OUTER JOIN(LEFT, RIGHT, FULL) 

+  컬럼 ALIAS는 첫 번째 모듈을 기준으로 표시됨, 정렬 기준은 마지막 SQL 모듈에 표시(ORDER BY)

 

LEFT OUTER JOIN 

• 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 테이터를 읽어옴 
• A LEFT OUTER JOIN B에서 테이블의 기준점은 A가 되고, B의 JOIN 칼럼에서 같은 값이 없는 경우, 테이블에서 가져오는 칼럼들은 NULL값으로 채움

FULL OUTER JOIN

• 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과 생성 
• A FULL OUTER JOIN B에서 기준점은 A, B 둘다 가능함
• FULL OUTER JOIN = LEFT JOIN + UNION + RIGHT JOIN

집합 연산자의 종류

1. UNION : 합집합(중복 행 제거) 
2. UNION ALL : 합집합(중복 행 제거 X)
3. INTERSECT : 교집합 
4. EXCEPT : 차집합 (NOT IN, NOT EXISTS로 대체 O, 일부 MINUS 적용) => DIFFERENCE 연산

계층형 질의(Hierachical Query)  : 테이블에 계층형 질의가 존재하는 경우 데이터를 조회하기 위해 사용, 계층적 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말합니다.

 

example)

상위 사원(관리자) -> 하위 사원(사원) ==> 조직관계 형성

 

1. STARTS WITH : 계층 구조의 시작 위치 지정하는 구문 (NULL로 시작하면 부모노드, NULL 아니면 자식노드)
2. ORDER SIBLING BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행하는 구문
3. 루트 노드의 LEVEL : 1
4. CONNECT BY 개념 
   1. PROIR 자식 = 부모, 부모 = PROIR 자식 (부모 -> 자식 방향으로 전개=순방향 전개)
   2. PROIR 부모 = 자식, 자식 = PROIR 부모 (자식 -> 부모 방향으로 전개=역방향 전개) 

 

SELF JOIN(셀프조인)

• 동일 테이블 사이의 조인
• 한 테이블 내에서 두 칼럼이 연관관계가 있음
• FROM 절에 동일 테이블이 두 번 이상 나타나며, 이를 식별하기 위해 ALIAS(별칭)을 반드시 사용해야 함

 

서브쿼리 : 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용

 

반환되는 데이터 형태에 따른 서브쿼리 종류

서브쿼리 종류	설명
Single Row 서브쿼리(단일 행 서브쿼리)	실행 결과가 항상 1건 이하, 비교 연산자는 =, >=, <=, <> 등 사용
Multi Row 서브쿼리(다중 행 서브쿼리)	실행 결과가 여러 건인 서브쿼리, 비교 연산자는 IN, ALL, ANY, SOME 등 사용
Multi Column 서브쿼리(다중 칼럼 서브쿼리)	실행 결과가 여러 칼럼을 반환, 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있으며, 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 함(SQL Server에서는 지원 X)

 

서브쿼리 사용 시 주의사항

1. 서브쿼리를 괄호로 감싸서 사용
2. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능, 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수 행 비교 연산자는 서브쿼리의 결과와 상관없다
3. 다중 행 서브쿼리 비교 연산자는 단일 행 서브쿼리 비교 연산자로도 사용이 가능
4. 메인쿼리의 결과 -> 서브쿼리의 결과, 서브쿼리의 결과 -> 메인쿼리의 결과
5. 서브쿼리에서 ORDER BY 사용 X

인라인 뷰(Inline View)

• From절에서 사용하는 서브쿼리, Order, Where 사용 O
• 서브쿼리의 결과가 동적으로 생성된 테이블인 것처럼 사용할 수 있음
• SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 정보 저장 X

뷰(View)

• 실제로 데이터를 가지고 있지 않은 가상 테이블

뷰(View)의 장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다
2. 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 이용 가능
3. 보안성 : 감추고 싶은 코드를 숨길 수 있음(특정 칼럼 숨길 수 있음)

 

그룹함수 종류 (ROLLUP, CUBE, GROUPING SETS)

1. ROLLUP : SubTotal(소계)를 생성하기 위해 사용, 집계에 대한 계층 구조를 나타냄, GROUPING COLUMN Count가 N이면, N+1 레벨의 SubTotal 생성, 컬럼 순서가 수행 결과에 영향을 미치기 때문에 주의
2. CUBE : 결합 가능한 모든 값에 대하여 다차원 집계 생성, ROLLUP에 비해 시스템 부하가 높음, 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 함. 컬럼 순서가 수행 결과에 영향을 미치지 않음 (CUBE(A,B) = GROUPING SETS(A, B, (A,B), ())
3. GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있음, ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 함
4. GROUPING : 집계 표시면 1, 아니면 0 

윈도우 함수(Window Function)

1. 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수
2. Partition과 Group By 구문은 의미적으로 유사
3. Partition 구문이 없다면, 전체 집합을 하나의 Partition으로 바라봄
4. 윈도우 함수 적용 범위는 Partition을 넘을 수 없음

윈도우 함수 구문

• SELECT FROM (SELECT 윈도우함수() OVER (PARTITION BY 컬1 ORDER BY 컬2 [SORT OPTION])

순위 관련 함수

1. RANK() : 동일 순위일 경우, 동일한 순위를 부여하고 NUMBER를 건너뜀(1,2,3,3,5)
2. DENSE_RANK() : 동일 순위일 경우, 하나의 건수로 취급(1,2,2,3,3,4)
3. ROW_NUMBER() : 동일한 값이라도, 서로 다른 고유 넘버를 부여(1,2,3,4)

평균을 구하기 위한 범위 지정

 

구문 예시

SELECT 상품분류코드
      ,AVG(상품가격) AS 상품가격
      ,COUNT(*) OVER(ORDER BY AVG(상품가격)
      		    RANGE BETWEEN 10000 PERCENDING AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드

적용된 윈도우 구문 

• ROWS BETWEEN UNBOUNDED(대신 숫자 가능) PERCENDING AND UNBOUNDED(대신 숫자 가능) FOLLOWING

 

LAG(), LEAD() : 이전 행, 이후 행의 값을 구하기 위한 함수

• LAG() : 이전의 행의 값을 구할 수 있음
• LEAD() : 이후의 행의 값을 구할 수 있음

노랭이 p123 118번 문항

 

START_VAL = LAG(END_VAL) : 현재 읽혀진 데이터의 이전 값을 구하기에 10을 기준으로 이전 값은 각각 NULL, 14, 15, 15, 18, 25

END_VAL = LEAD(START_VAL) : 현재 읽혀진 데이터의 이후 값을 구하기에 14를 기준으로 이후 값은 각각 14, 15, 16, 20, 25, 99 입니다. (화살표 보면 이해하기 쉬울 거라고 확신합니다)

LAG_END_VAL	LEAD_STRAT_VAL
NULL	14
14	15
15	15
15	20
18	25
25	99

이 값을 참고하여 FLAG1, FLAG2를 구해야 합니다. 

F1	F2
0	1
1	1
1	1
1	0
0	1
1	0

WHERE 조건절에서 F1, F2 값중 하나 이상 0이 나와야 하므로 답은 1행, 4행, 5행, 6행이 됩니다.

 

시스템 권한을 부여하기 위한 명령

1. GRANT : 권한 부여 
2. REVOKE : 권한 취소

구문 사용 방법

• GRANT [DML] ON [테이블명] TO [유저명] [권한옵션]
  • WITH GRANT OPTION : 권한을 부여받은 유저가 동일 권한을 줄 수 있는 옵션
• REVOKE [DML] ON [테이블명] FROM [유저명] [권한옵션]
  • CASCADE : WITH GRANT OPTION으로 부여된 권한까지 모두 회수
• DENY [DML] ON [테이블명] TO [유저명]

 

 PL/SQL의 특징

1. Block 구조로 되어있어 각 기능별로 모듈화 가능
2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
3. IF, LOOP 등의 절차적 언어를 사용하여 절차적인 프로그램이 가능하도록 함
4. DBMS 정의 에러나 사용자 정의 에러를 사용할 수 있음
5. PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
6. 응용 프로그램의 성능을 향상시킬 수 있음
7. 트랜잭션 분할 가능

저장 모듈(Stored Module)

• SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램, 독립적으로 실행되거나, 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램(Procedure, User Definition Function, Trigger)
• Procedure는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합
• User Definition Function은 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴
• Trigger는 특정 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때 데이터베이스 내에서 자동으로 동작하도록 작성된 프로그램 (데이터 무결성과 일관성을 위해 사용)

Oracle에서는 동적 SQL 적용 

execute immediate "TRUNCATE TABLE DEPT" (모든 데이터를 ROLLBACK이 불가능하도록 설정)

 

프로시저와 트리거 차이점

Procedure	Trigger
CREATE Procedure 문법 사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨