[SQLD 암기] SQL 활용

* 순수 관계 연산자

 - SELECT 연산은 WHERE절로 구현

 - PROJECT 연산은 SELECT 절로 구현

 - (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현

 - DIVIDE 연산은 현재 사용되지 않음

 

* INNER JOIN

 조인 조건에서 동일한 값이 있는 행만 반환

 FROM 절에서 정의하고 ON이나 USING 사용 필수

 USING 조건 절에서 ALIAS나 테이블 이름의 접두사를 붙일 수 없음

 

* CROSS JOIN : 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합

 

* FULL OUTER JOIN

 조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어서 JOIN

 = LEFT OUTER JOIN + UNION + RIGHT OUTER JOIN

 = UNION ALL + UNION ALL

 

* OUTER JOIN

 SELECT X.K1, Y.K2 FROM TAB1 X LEFT OUTER JOIN TAB2 Y ON (X.K1=Y.K2)

 SELECT X.K1, Y.K2 FROM TAB1 X RIGHT OUTER JOIN TAB2 Y ON (X.K1=Y.K2)

 SELECT X.K1, Y.K2 FROM TAB1 X FULL OUTER JOIN TAB2 Y ON (X.K1=Y.K2)

 ORACLE에서 (+)는 OUTER JOIN 의미

 

* JOIN은 나중에 수행되면서 중복데이터를 제거함

 

* 1:1 관계

 - EXCEPT면 항상 공집합

 - UNION ALL 은 항상 전체 건수의 두배

 - INTERSECT는 JOIN 연산 수행 결과와 건수 같음

 - INTERSECT는 UNION 연산 수행 결과와 건수 같음

 

* PRIOR

 - CONNECT BY 절에 사용

 - PRIOR 자식 = 부모

 - 부모 -> 자식으로 순방향 전개

 

* 오라클 계층형 질의

 - START WITH 절은 계층 구조의 시작점을 지정하는 구문

 - ORDER SIBLINGS BY 절은 형제 노드 사이에서 정렬을 지정하는 구문

 - 루트 노드의 LEVEL 값은 1

 - SQL server에서 계층형 질의문은 CTE를 재귀 호출하여 계층 구조 전개

 - SQL server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 재귀 멤버를 지속적으로 실행

 - 오라클 계층형 질의문에서 WHERE절은 모든 전개를 진행하고 필터 조건을 만족하는 데이터만 추출하는 데 사용

 

* SELF JOIN

 동일 테이블 사이의 조인

 FROM 절에 동일 테이블이 두 번 이상 나타남, 식별을 위해 별칭 사용

 한 테이블 내에서 두 칼럼이 연관 관계가 있는 경우 수행

 

* DISTINCT A||B = (A,B)의 쌍

 

* 서브쿼리

 - single row 단일행 서브쿼리 : 서브쿼리의 실행 결과가 항상 1건 이하, 비교연산자(>,<,=)

 - multi row 다중행 서브쿼리 : 서브쿼리의 실행 결과가 여러 건, 비교연산자 IN,ALL,ANY,SOME,EXIST

                                          비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용할 수 있음

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

                                                 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 위치가 동일해야 함

                                                 서브쿼리의 결과인 여러 컬럼이 메인쿼리의 조건과 동시에 비교되는 것을 의미

 - 서브쿼리는 SELECT절, FROM절, HAVING절, ORDER BY절 등에서 사용 가능

 - 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리(SQL server에서는 지원하지 않음)

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

 - 서브쿼리에서는 ORDER BY를 사용하지 못함, ORDER BY절은 SELECT 절에서 오직 한 개만 올 수 있음

 - 비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용

 - 메인쿼리의 결과가 서브쿼리로 제공될 수 있고, 서브쿼리 결과가 메인쿼리로 제공될 수도 있음

 

* 뷰

 - 실행 시점에 질의를 재작성하여 수행

 - 복잡한 SQL 문장을 단순화 시켜주는 장점, 테이블 구조가 변경돼도 응용 프로그램 변경 안해도 됨

 - 보안 강화 목적

 - 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있음

 

* ROLLUP : 계층구조를 가진 SUB TOTAL 생성 함수, 컬럼 순서에 따라 결과가 달라짐

  CUBE : 결합 가능한 모든 값에 대해 다차원 집계, 시스템 부하가 큼

  GROUPING SET : 계층구조 없이 지역에 대한 합계와 월별합계가 각각 생성

- ROLLUP, CUBE, GROUPING SET은 일반 그룹 함수와 같은 결과를 낼 수 있음

- 대상 컬럼 중 집계된 컬럼 이외의 대상 컬럼 값은 NULL을 반환

 

* 윈도우 함수

 - PARTITION과 GROUP BY 구문은 의미적으로 유사

 - PARTITION 구문이 없으면 전체 집합을 하나의 PARTITION으로 정의하는 것과 동일

 - 윈도우 함수 처리로 결과 건수가 줄어들지는 않음

 - 윈도우 함수 적용 범위는 PARTITION을 넘을 수 없음

 - RANK : 중간 순위 비워 둠, DENSE_RANK : 중간 순위 안 비움, ROW_NUMBER : 동일 값 다른 순위

 

* RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING

 = 평균 가격 대비 -10000~+10000사이 존재

 

* LAG = 이전 값, LEAD = 이후 값

 

* ROLE

 - DBMS에서 DBMS 관리자가 사용자별로 권한을 관리해야 하는 부담과 복잡함을 줄이기 위해

   다양한 권한을 그룹으로 묶어 관리하도록 사용자와 권한 사이에서 중개 역할을 수행

 

* PL/SQL

 - 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE절의 조건 등을 대입

 - PROCEDURE, USER DEFINED FUNCTION, TRIGGER 객체 작성 가능

 - 작성자 기준으로 트랜잭션을 분리할 수 있음

 - 프로시저 내에서 프로시저를 호출할 경우 PRAGMA AUTONOMOUS_TRANSACTION 선언하여 자율 트랜잭션 처리

 - 프로시저 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리, 일반적인 SQL문장은 SQL 실행기가 처리

 - 동적 SQL 또는 DDL 시 execute immediate를 사용해야 함

 

* 절차형 SQL = STORED MOLUDE

 - 저장형 프로시저는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미

 - 저장형 함수는 단독적으로 실행되기 보다 다른 SQL문을 통해 호출되고 그 결과를 리턴하는 SQL의 보조 역할을 함

 - 트리거는 특정 테이블에 DML이 수행 됐을 때 데이터베이스에서 자동으로 동작하도록 함

 

* TRIGGER

 - 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용

 - 데이터베이스에 의해 자동으로 호출되고 수행

 - 특정 테이블에서 INSERT, DELETE, UPDATE 문이 수행될 때 호출되도록 정의

 - 데이터베이스에 로그인하는 작업에도 정의할 수 있음

 

* 프로시저

 - CREATE Procedure 문법 사용

 - EXECUTE 명령어로 실행

 - COMMIT, ROLLBACK 실행 가능

 cf) 트리거 : CREATE trigger, 생성 후 자동 실행, COMMIT ROLLBACK 실행 안됨