[SQLD자격증] # 2. (2과목) SQL 기본 및 활용 - SQL 활용 요점 정리

해당 글은 https://cafe.naver.com/sqlpd/7810 을 참조하고
기출을 풀며, 내용 추가하였습니다.

표준 조인
INNER JOIN	- JOIN 조건에서 동일한 값이 있는 행만 반환, USING이나 ON 절을 필수적으로 사용   - (ANSI 표준)      SELECT * FROM A, B WHERE A.aa = B.bb AND B.cc = 'dd'      SELECT * FROM  A INNER JOIN B ON A.aa = B.bb WHERE B.cc = 'dd'      같은 결과
NATURAL JOIN	- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행   - NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE 절에서 JOIN 조건을 정의할 수 X   - SQL Sever는 지원X
EQUAL JOIN NON EQUAL JOIN	- EQUAL JOIN은 두 개의 테이블 간에 컬럼 값이 일치하는 걸 조인,  두 개의 테이블에서 교집합을 찾음   - NON EQUAL JOIN은 <, >, <=, >=을 사용
SELF JOIN	- 한 테이블에서 두 개의 컬럼이 연관 관계를 가지고 있는 경우에 사용
USING 조건절	- 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUAL JOIN 수행   - JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 X   - SQL Server 지원X
ON 조건절	- ON 조건절과 WHERE 조건절을 분리하여 이해가 쉬우며, 칼럼명이 다르더라도     JOIN 조건을 사용할 수 있음   - ALIAS나 테이블명 반드시 사용
CROSS JOIN	- 양쪽 집합의 M*N건의 데이터 조합이 발생   - WHERE절에 조건 추가 가능
OUTER JOIN	- JOIN 조건에서 동일한 값이 없는 행도 반환 가능, USING이나 ON 조건절 반드시 사용해야 함
LEFT OUTER JOIN	- 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후,      나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어옴   - 우측 값에서 같은 값이 없는 경우 NULL 값으로 함
RIGHT OUTER JOIN	- LEFT OUTER JOIN의 반대   - 우측 테이블 값은 채워져있으나, 좌측 테이블 값은 없을 때   - (ANSI 표준)      SELECT * FROM A, B WHERE A.aa(+) = B.bb      SELECT * FROM A RIGHT OUTER JOIN B ON (A.aa = B.bb)
FULL OUTER JOIN	- 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성   - 중복 데이터는 삭제

SELECT COUNT(*) FROM A,B : A데이터 개수 * B데이터 개수

집합 연산자
UNION	합집합(중복 행은 1개로 처리), 자동으로 정렬 - 컬럼수와 데이터 타입이 완전 일치해야 함
UNION ALL	합집합(중복 행도 표시) - UNION ALL이 UNION보다 성능이 우수 - 정렬 자동적으로 발생X - 컬럼수와 데이터 타입이 완전 일치해야 함
INTERSECT	교집합(INTERSECTION)
EXCEPT(MS-SQL), MINUS(Oracle)	차집합(DIFFERENCE)
CROSS JOIN	곱집합(PRODUCT)

 

계층형 질의
STATRT WITH	계층 구조 전개의 시작 위치 지정
CONNECT BY	다음에 전개될 자식 데이터 지정   - 부모 계층형 쿼리에서 부모노드와 자식 노드 사이의 특정한 관계를 나타내는데 사용
PRIOR	CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.   - PRIOR 자식 = 부모 형태  :    계층구조에서 부모 데이터에서 자식 데이터(부모→자식) 방향으로    전개하는 순방향 전개, 하위의 모든 노드 추출   - PRIOR 부모 = 자식 형태      (자식→부모) 방향, 역방향 전개로, 상위의 모든 노드 추출
NOCYCLE	동일한 데이터가 전개되지 않음, 순환구조 발생 지점까지만 전개
ORDER SIBLINGS BY	형제 노드간의 정렬 수행
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)
LEVEL	루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	해당 데이터가 리프 데이터면1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터(최상위)를 표시. 단항 연산자.
- START WITH절에서 필터링된 시작 데이터는 결과목록에 포함!! - 이후 데이터는 CONNECT BY절에 의해 필터링 - [SQL Server] 계층형 질의문은    . CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조 전개    . 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행 - [Oracle] 계층형 질의문은    . WHERE절은 모든 전개를 진행한 이후 필터 조건. 조건을 만족하는 데이터만 추출하는데 활용    . PRIOR 키워드는 SELECT, WHERE절에서도 사용 가능

SELECT (스칼라 서브쿼리)
    FROM (인라인뷰)
  WHERE (서브쿼리)

서브 쿼리 :하나의 SQL문안에 포함되어 있는 또 다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용
1. 서브쿼리를 괄호로 감싸서 사용 2. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능     단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고     복수 행 비교 연산자는 결과 건수와 상관X 3. 서브쿼리에서는 ORDER BY를 사용하지 못함 4. SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능 WHERE절에 있는 서브쿼리
Top-N 서브쿼리	- INLINE VIEW의 정렬된 데이터를 ROWNUM을 이용해 결과 행 수를 제한   하거나 TOP(N) 조건을 사용  - SELECT *       FROM (                    SELECT ROWNUM TOP, A FROM B) a     WHERE a.TOP = 2;
단일 행 비교 연산자	=,<,>,<> 등 , 항상 1건 이하인 서브쿼리
다중 행 비교 연산자	IN, ALL, ANY, SOME 등 하나 이상의 데이터가 RETURN - ALL : 서브쿼리 값 모두가 조건에 만족하면 True  (WHERE 컬럼명 < ALL(A, B) : A보다 작으면서, B보다도 작아야 함) - ANY : 서브쿼리 값 중 어느 하나의 값이라도 만족하면 결과값을 리턴
다중 컬럼 서브쿼리	- 여러 컬럼을 반환 - 메인쿼리의 조건절에 여러 컬럼을 동시 비교 가능 - 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼 위치가 동일해야 함 - SQL Server에서는 현재 지원 x
상호 연관 서브쿼리 (Correlated SubQuery)	- 메인쿼리 값을 서브쿼리가 사용하고 서브쿼리의 값을 받아서 메인쿼리가 계산되는 쿼리 - 서브쿼리가 메인쿼리의 행 수 만큼 실행되는 쿼리 - 실행 속도가 상대적으로 떨어짐 - 복잡한 일반 배치 프로그램을대체할 수 있어 조건에 맞는다면 적극적 검토 필요
스칼라 서브쿼리	- 한 행, 한 칼럼만을 반환하는 서브쿼리 - 메인쿼리의 컬럼 사용 가능. 메인쿼리는 스칼라 서브쿼리의 컬럼 사용 불가능 - ORDER BY구 사용할 수 X
SQL 개선 측면 서브쿼리 종류	Access Subquery : 쿼리의 변형 X, 제공자 역할 Filter Subquery : 쿼리의 변형 X, 확인자 역할 Early Filter Subquery : 쿼리의 변형 X, 서브쿼리가 먼저 실행하여 데이터를 걸러냄
인라인 뷰(INLINE VIEW)	- 테이블 명이 올 수 있는 FROM절에서 정의 - ORDER BY 사용 가능 - SQL 문장 내 절차성을 주는 효과를 볼 수 있음 - 실질적인 오브젝트는 아니지만, SQL 문장에서 마치 VIEW나 테이블처럼 사용 - 메인쿼리에서 컬럼 사용 가능
뷰	- 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. 가상 테이블이라고도 함 - 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행  [뷰 사용 장점] 1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다. 2. 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 3. 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용 CREATE VIEW 뷰명 AS DROP VIEW 뷰명;

그룹 함수
ROLLUP	Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다. 인수 순서에 주의 - ROLLUP(A, B) : A별 합계,  A/B별 합계, 전체 합계 - ROLLUP(COL1, COL2) = GROUPING SETS ((COL1, COL2), (COL1), ())
GROUPING	Subtotal의 total을 생성
CUBE	결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하 심함 GROUPING SETS(A, B (A, B), ()) = CUBE(A, B)
GROUPING SETS	인수들에 대한 개별 집계를 구할 수 있다, 다양한 소계 집합 생성 가능

 

윈도우 함수
- Partition, Group By 구문은 의미적으로 유사 - Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일 - 윈도우 함수 적용 범위는 Partition(레코드 범위)을 넘을 수 없음 - 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않음 - GROUP BY절과 함께 윈도우 함수를 사용해도 오류발생X   → GROUPING된 집합 데이터 안에서 윈도우 함수 적용) - 실제 행은 줄어들지 않음 - 순위함수에서 OVER()내에 ORDER BY를 사용하지 않으면 오류 발생
RANK	특정 항목에 대한 순위를 구하는 함수, 동일한 값에 대해서는 동일한 순위를 부여 중복된 순위 다음에는 해당 개수만큼 건너뛴 다음 순위 부여(1,2,2,4)
DENSE_RANK	동일한 순위를 하나의 건수로 간주(1,2,2,3)
ROW_NUMBER	동일한 값이라도 고유한(다른, 새로운) 순위 부여 (1,2,3,4)
SUM, MAX, MIN	파티션별 윈도우의 합/최대/최소
AVG, COUNT	원하는 조건에 맞는 데이터에 대한 평균/개수
FIRST_VALUE, LAST_VALUE, LAG, LEAD	파티션별 윈도우에서   가장 먼저 나오는 값,   가장 나중에 나온 값,   이전 몇 번째 행의 값,   이후 몇 번째 행의 값 (LEAD(대상컬럼, #순서, 디폴트 값) OVER (PARTITION BY) : 윈도우 내에서 해당 값 이후 #번째 값 없으면 디폴트 값으로) ※ SQL Server 지원 X
RATIO_TO_REPORT	파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점
PERCENT_RANK	파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행의 순서별 백분율
CUME_DIST	주어진 그룹에 대한 상대적인 누적분포도 값(0< <=1)
NTILE	파티션별 전체 건수를 인수 값으로 N등분한 결과 (ex. 8개 데이터 3등분 → (3, 3, 2))
UNBOUNDED PRECEDING UNBOUNDED FOLLOWING CURRENT ROW	윈도우의 시작 위치가 첫 번째 행 임을 의미 (start point에만 들어갈 수 있음)  윈도우의 마지막 위치가 마지막 행 임을 의미 (end point에만 들어갈 수 있음)  윈도우 시작 위치가 형재 행을 의미 (start, end point 둘 다 가능) - Default : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 현재 행 기준 파티션 내 앞의 한 건 현재 행 뒤의 한 건 범위 지정 : ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING - GROUP BY는 실제 출력되는 행을 줄여서 출력하나, Window function은 실제 행이 줄어들지 않음. 두 개 병행작성이 불가능한 것은 아니지만, 병행하여 사용하지 않고 필요에 따라 둘 중 하나를 선택해 사용 [예시] COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 10 PRECEDING AND 150 FOLLOWING  : SAL 기준 현재 행에서 SAL의 -10에서 +150 사이의 급여를 가지는 행 수  SUM(A) OVER (PARTITION BY B ORDER BY C RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) : B별 C순서별 누적 A값 SELECT COUNT(*) OVER(ORDER BY AVG(A) RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) FROM TABLE GROUP BY B : B별 평균 A를 기준으로 -100~100 사이에 존재하는  B의 개수를 구함

 

DCL :유저 생성하고 권한을 제어할 수 있는 명령어
Oracle과 SQL Server의 사용자 아키텍처 차이 Oracle : 유저를 통해 DB에 접속을 하는 형태, ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨 SQL Server : 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다. Windows 인증 방식과 혼합 모드 방식이 존재함
GRANT	권한 부여 (ex) GRANT CREATE USER TO SCOTT; CONN SCOTT/TIGER(ID/PW) CREATE USER PJS IDENTIFIED BY KOREA7; GRANT CREATE SESSION TO PJS; GRANT CREATE TABLE TO PJS; GRANT SELECT, INSERT, DELETEON 릴레이션 TO 유저 WITH GRANT OPTION - 테이블에  대한 권한은 각 테이블의 소유자가 가지고 있어 소유자로부터 권한 받지 않으면 다른 유저의 테이블에 접근할 수 없음 - WITH GRANT OPTION : 권한을 부여할 수 있는 권한을 부여한 것, 먼저 권한을 할당 받은 사용자의 권한이 취소되면 연쇄 취소 됨.
REVOKE	권한 취소 (ex) REVOKE INSERT ON TABLE FROM USER CASCADE (USER에게 권한을 받은 다른 USER들도 'INSERT'권한을 회수함)
ROLE	- 유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용하는 것 - DBMS 관리자가 사용자별로 권한을 관리해야 하는 부담과 복잡함을 줄이기 위해 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에 중개 역할 수행 (ex) CREATE ROLE LOGIN_TABLE; GRANT CREATE TABLE TO LOGIN_TABLE;
CASCADE	하위 오브젝트까지 삭제 (ex) DROP USER PJS CASCADE; - Oracle에서만 존재, SQL Server에서는 존재X   (SQL Server에서는 참조하는 외래키 제약조건, 참조하는 테이블을 먼저 삭제한 후 해당 테이블 삭제) - CASCADE CONSTRAINT : 해당 테이블과 관계 있었던 참조되는 제약조건에 대해 삭제 수행

 

절차형 SQL
절차형 SQL : SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다, Procedure, User Defined Function, Trigger 등이 있음   저장 모듈 : PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램 (Procedure, User Defined Function, Trigger) 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할을 함   PL/SQL 특징 1. Block 구조로 되어있어 각 기능별로 모듈화 가능 2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환 3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다. 4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다. 5. PL/SQL은 Oracle에 내장되어 있으므로 호환성 굳 6. 응용 프로그램의 성능을 향상시킨다. 7. Block 단위로 처리 -> 통신량을 줄일 수 있다. 8. Procedure, User Defined Function, Trigger 작성 가능 9. Procedure, User Defined Function은 작성자의 기준으로 트랜젝션을 분할할 수 있음 10. 프로시저 내 다른 프로시저 호출 시 호출 프로시저의 트랜젝션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언하여 자율 트랜젝션 처리 가능 11. 프로시저 내부작성된 절차적코드는 PL/SQL엔진이 처리하고 일반적인 SQL문장은 SQL실행기가 처리 12. 변수와 상수 등을 사용하여 일반 SQL문장을 실행할 때 WHERE절의 조건 등으로 대입 가능
DECLARE	BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부
BEGIN~END	개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용 필요한 로직 처리
EXCEPTION	BEGIN~END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부
CREATE RELPACE DROP	CREATE Procedure Procedure_name REPLACE Procedure Procedure_name DROP Procedure Procedure_name
동적 SQL 또는 DDL 문장실행 시	execute immediate (DEPT테이블에 데이터 입력 전 테이블의 모든 데이터를 ROLLBACK 불가능하도록 선언) execute immediate 'TRUNCATE TABLE DEPT'

T-SQL
T-SQL:	근본적으로 SQL Server를 제어하는 언어 CREATE Procedure schema_NAME.Procedure_name
Trigger	특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램, 사용자 호출이 아닌 DB 자동 수행 CREATE Trigger Trigger_name - 프로시저와 달리 COMMIT, ROLLBACK과 같은 TCL을 사용할 수 없음 - 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용 - DB에 로그인하는 작업에도 정의 가능

[프로시저와 트리거의 차이점]

프로시저	트리거
CREATE Procedure 문법 사용	CREATE Trigger 문법 사용
생성하면 소스코드와 실행코드가 생성됨	생성하면 소스코드와 실행코드가 생성됨
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능(BEGIN~END 절 내에 )	COMMIT, ROLLBACK 실행 불가

 
 
[데이터베이스 테이블의 제약조건]
- 기본키는 테이블당 하나의 제약만을 정의할 수 있음
- Check 조건은 테이블에 데이터를 입력하기 전에 검사를 수행
- 외래키는 테이블 간의 관계를 정의하고 참조 무결성을 준수하게 함
- 고유키로 지정된 모든 컬럼은 중복된 값을 허용하진 않지만, null값을 가질 수도 있음