[SQLD/P] SQL 활용 - 참고사항

1. 순수 관계 연산자

• SELECT, PROJECT, JOIN, DIVIDE

2. 쿼리 문제 팁

• 의미적으로 접근해본다.
• 보기별로 서로 비교하는 항목이 두개씩 존재하므로, 이 비교항목을 잘 살펴보도록 한다.

3. join 관련 유의사항

• 엔터티가 3개 있다면 최소한으로 필요한 join은 2번이다.
• 적절한 join 조건 및 비교조건이 없다면 해당 쿼리는 기본적으로 cartesian 곱, 즉 모든 조건을 출력하는 교차곱을 수행한다.

4. USING절

• join할때 조건을 붙이는 on절과 유사하다.
• 단 using은 서로 연결하는 컬럼의 명이 동일할때만 사용할 수 있고, using 절에서 alias 및 접두사는 붙일 수 없다(오로지 column명만 기재)
• 접두사를 붙일 수 없는 join이라는 관점에서 natural join과 비슷하다.

5. 곱집합

• CARTESIAN JOIN
• sql문 사용시 " CROSS JOIN "

6. join, on 유의사항

• join을 수행하고 여기에 조건절인 on을 붙였을때는 where절처럼 선별하는 것이 아니라, 일단 join한 기준 table을 일단 모두 출력하고 그 후에 조건에 맞는 항목만 join(연결)하는 형태이다.

7. LEFT, RIGHT, FULL OUTER JOIN

• 이어주는 외래키, 즉 연결 컬럼을 잘 살펴보아야 한다.
• LEFT : 왼쪽 기준으로 출력, 오른쪽 이어주는 테이블의 외래키 컬럼의 수보다 적어도 외래키가 동일하다면 그만큼 출력
• RIGHT : 오른쪽 기준으로 출력, 마찬가지로 왼쪽 이어주는 테이블의 외래키 컬럼수보다 적어도 외래키가 동일하게 존재한다면 그만큼 출력
• FULL : 외래키, 즉 이어주는 연결 컬럼이 모두 출력되어야 한다.

8. join 유의사항

• join시 alias는 첫번째 sql 문을 기준으로 출력됨
• order by 1,2는 각각 첫번째 컬럼과 두번째 컬럼을 기준으로 정렬한다는 것이고 desc, asc가 명기되어있지 않다면 오름차순으로 정렬

9. 계층형 구조

SELECT COLUMN
FROM A
START WITH COLUMN_A IS NULL
CONNECT BY PRIOR COLUMN_B = COLUMN_A
ORDER BY SIBLINGS BY COLUMN DESC

• 루트노드의 값은 1이다.
• PRIOR 자식 = 부모 -> 부모-자식 순으로 전개하는 순방향 전개
• PRIOR 부모 = 자식 -> 자식-부모 순으로 전개하는 역방향 전개
• 기본적으로 계층적으로, 즉 해당 부모에 대한 자식 순으로 출력되는 계층적인 구문이다.
• 계층적인 출력 형태를 기본으로 하고, 이후에 같은 level 내의 노드들에 대해 정렬한다.

10. 계층형 질의문

• SQL Server에서 계층형 질의문은 CTE(Common Table Expression)을 재귀 호출함으로써 계층 구조를 전개한다.
• SQL Server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본결과 집합을 만들고, 이후 재귀 멤버를 지속적으로 실행한다.
• 오라클 계층형 질의문에서 WHERE절은 모든 전개를 진행한 이후, 필터조건으로 실행한다.
• PRIOR 키워드는 CONNECT BY에 주로 사용되지만, SELECT나 WHERE절에서도 사용 가능하다.
• START WITH에서 특정 부서코드를 서브쿼리로 추출하고자 할 때, 해당 서브쿼리에 계층형 질의가 나와도 최상위 노드를 추출하고 있는지 등을 잘 살펴보아야 한다.

11. distinct A || B

• A, B 컬럼의 문자열을 합쳤을때, 그 결과가 중복이라면 동일한 것(count 1)으로 간주하겠다는 의미이다.

12. 서브쿼리 유의사항

• 서브쿼리는 단일 행(Single row), 복수 행(Multi Row) 비교 연산자와 사용 가능하다.
• 서브쿼리는 select, from, having, order by 절 등에서 사용 가능하다.
• 서브쿼리의 결과가 다중 행(Multi Row) 출력시, 반드시 IN, ANY, ALL 등의 복수행 비교 연산자와 같이 사용해야 한다.
• 연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리의 컬럼을 포함한다(비연관 서브쿼리는 반대)
• 다중 컬럼 서브쿼리는 여러 개의 컬럼이 반환되는 쿼리이며, 메인 쿼리의 조건과 비교할 수 있고 Oracle에서만 사용 가능하다(SQL server에서는 지원하지 않는다).
• 다중 컬럼 서브쿼리 비교 연산자(IN, ANY 등)는 단일 행 서브쿼리의 비교 연산자(<, >, <=, >=, <> 등)로도 사용 가능하지만, 그 반대는 불가능하다.
• 비연관 서브쿼리는 보통 (특히 일정표처럼) 메인쿼리에 값을 제공하기 위한 목적으로 사용한다.

13. having count(*)

• join시 having count(*) 조건을 추가할 경우, table을 연결하고 해당 연결 조건을 만족하는 컬럼이 1개라도 존재할 경우에 대한 조건을 명기하는 구문이다.

14. roll up

group by rollup( A, B)

• group by 기준 자체는 A, B컬럼이다.
• roll up(소계)은 group by를 한 결과를 바탕으로 진행하며, A컬럼에 대해 집계한 결과를 최종적으로 합산하여 나타낸다.

집계함수는 이곳을 참조

15. GROUPING

• select 절에서 사용한다.
• 집계함수를 감지하는데, 만약 집계함수를 사용한 행이 있을 경우 1을 return하고 그렇지 않다면 0을 return 한다.
• 보통 집계함수를 사용시 아무런 조치를 취하지 않으면 null이 출력되기 때문에, 해당 컬럼에 대한 정보를 별도로 표시하기위해 GROUPING 구문을 사용한다.

16. 이외 그룹함수

• CUBE( A, B) : ROLLUP함수의 결과에 B컬럼에 대한 소계 결과가 합쳐진 것으로, A컬럼에 대한 소계/B 컬럼에 대한 소계/총 소계가 모두 구해진다.
• GROUPING SETS(A, B) : A항목별 각각의 소계, B항목별 각각의 소계만 각각 구해진다. 총 소계는 구하지 않는다.

※ grouping sets( (A,B) ) : A,B 다중 컬럼에 대한 소계 -> group by와 동일한 결과

17. 윈도우 함수

• partition과 group by는 의미적으로 유사하다.
• partition은 집계에 대한 권역을 나눈다, 이것이 없다면 전체 집합을 하나의 파티션으로 보는 것이다.
• 윈도우 함수 적용 범위는 partition을 넘을 수 없다.

18. rank, dense_rank

rank -> 11115
dense_rank -> 11112

18. 윈도우 함수 - count(*)

• count(OVER ~~) : over에 명기한 기준대로 정렬한 후, 해당 정렬한 개수를 출력하는 윈도우 함수이다.

19. 윈도우 함수 - preceeding, following

COUNT(*) OVER(ORDER BY AVG(상품가격) RANGE(or ROW)
BETWEEN 10000 PRECEEDING AND 10000 FOLLOWING)

-> -10000을 시작점으로 하고 +10000을 끝점으로 하는 행에 대한 갯수

• UNBOUNDED PRECEEDING : 첫번째 시작 행부터 끝까지
• UNBOUNDED FOLLWOING : 특정 시작 점부터 끝까지
• CURRENT ROW : 윈도우의 시작 위치까지

윈도우 함수는 이곳에서 참고

20. 윈도우 함수 - LAG, LEAD

• LAG(A) -> 지금 바로 이전에 읽었던 행의 A 컬럼의 값
• LEAD(A) -> 지금 바로 이후에 읽을 행의 A 컬럼의 값

21. GRANT, REVOKE

GRANT SELECT, INSERT, DELETE ON R TO KIM WITH GRANT OPTION

• KIM에게 R 테이블의 SELECT, INSERT, DELETE에 대한 권한을 부여
• 또한 kim이 부여받은 동일한 권한 만큼 다른 사람에게 부여할 수 있는 권한도 생김

REVOKE INSERT ON R FROM KIM CASCADE

• KIM에게 R 테이블의 INSERT 권한 회수
• 그러면서 KIM이 누군가에게 INSERT 권한을 부여하였다면, 그것도 같이 회수(cascade 옵션이 없다면 그 권한만 회수하고, 2차적으로 부여한 권한은 회수하지 않음)

22. 프로시저

• 프로시저 내 다른 프로시저 호출시, 호출 트랜잭션과는 별도로 자율적으로 트랜잭션 처리도 할 수 있다.
• execute 명령어로 실행하며 commit, rollback 시리행 가능
• 동적 SQL -> execute immediate, watcom -> execute 단독 사여ㅛㅇ

23. 트리거

• DB에 의해 자동적으로 실행되는, DML 문이 실행되었을때 자동으로 동작하는 프로그램이다.
• 프로시저를 통해 트리거를 제어할 수는 있으나, TCL을 통해 제어할 수는 없다.
• Trigger는 DB에 로그인하는 작업도 정의할 수 있다.
• commit, rollback으로 실행 불가