1. ANY
- IN과 유사한 구문으로, ANY 다음의 비교 대상에 대해 하나라도 만족하거나 참일 경우 해당 값을 출력한다.
- IN과의 차이점은, IN은 해당 구문만 사용(다른 비교 연산자 등을 사용하지 않음)하고 ANY는 비교 연산자(<, >, =, <=, >= 등)를 사용할 수 있다는 점이다.
2. ALL
- ANY와 마찬가지로 비교 연산자를 사용하며, 모든 조건을 만족하는 경우에만 해당 값을 출력하는 구문이다.
- WHERE A.DEPT_PERIOD <= ALL (30, 50)와 같이 사용하며, 해당 구문은 ALL 범위로 설정한 30과 50에 대해 모두 같거나 작아야 출력을 할 수 있다는 의미이다(즉, 30보다 같거나 작을 경우에만 출력).
3. NULL
- SUM과 같은 윈도우 집계 함수에서는 NULL값을 제외하고, 나머지 값이 있는 행에 대해서만 집계함수 처리를 한다.
- NOT IN NULL일 경우, unknown 처리 및 true 반환하여 NOT TRUE와 같고, 이로 인해 0건을 반환한다(NOT IN TRUE, 모든 조건에 대해 출력할 수 없는 조건으로 설정).
- WHERE절에서 = 비교연산자가 NULL을 만났을때, NULL값에 대한 비교 연산자 처리는 불가능하여 NULL=NULL에 대한 조건은 제외한다.
4. 반올림 관련 함수
- TRUNC : 소수 버림
- CEIL : 올림
- FLOOR : 버림
- ROUND : 반올림
5. 집계함수
- ROLL UP(A,B) : A,B 항목에 대해 group by한 후, A항목에 대한 소계를 계산하며 최종적인 총계까지 계산한다.
- GROUPING SETS(A,B) : A,B 항목에 대해 group by한 후, A항목에 대한 소계와 B항목에 대한 소계를 각각 계산한다.
※ 이것이 ROLL UP과 같게 하기 위해선 먼저 (A,B) 자체를 항목으로 두어서 소계하도록 하고, A항목을 다시 소계항목으로 넣고, NULL값까지 두어야 최종 소계까지 계산하여 ROLL UP과 동일한 결과를 출력
GROUPING SETS는 이곳을 참조
(=GROUPING SETS((A,B),A,NULL) - cube(A,B) : A항목에 대한 소계, A소계를 모두 총계한 최종 결과 출력, B항목에 대한 소계까지 모두 출력한다(GROUPING SETS는 각 소계에 대한 총계는 계산하지 않음)
6. IDENTITY(1,1)
create table PetOwner
(
Id int identity(1,1)
,Name varchar(200)
,Policy varchar(40)
)- identity로 정의된 Id컬럼은 1부터 시작하여, 데이터가 삽입될때마다 자동으로 1씩 증가하면서 저장된다(1, 2, 3, 4, 5...).
- identity로 정의된 컬럼은 데이터를 넣을 수 없고, 해당 컬럼에 데이터를 넣게 된다면 오류가 발생한다.
7. CHECK
- DDL 문에서 해당 컬럼에 대한 제한 조건 및 범위 조건을 명시한다.
CREATE TABLE emp
(
id CHECK (id is not NULL)
,SUM CHECK (SUM < 1000)
)- CHECK에서 기재한 비교조건을 만족하지 못하면 데이터 삽입이 안되지만, NULL 값을 넣을 경우 무시하고 넣는다(즉, NULL값의 데이터가 만들어짐, PK라면 안넣어질 것)
8. 실행 계획
0 SELECT STATEMENT Optimizer = ALL_ROWS(Cost = 7 Card = 9)
1 HASH JOIN (Cost = 7 Card = 9)
2 TABLE ACCESS(FULL) OF 'SCOTT.DEPT' (TABLE) (Cost = 3 Card = 1)
3 VIEW (Cost = 3 Card = 9)
4 COUNT (STOPKEY)
5 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost =3 Card = 14)
Predicate information
1 - access("A"."DEPTNO" = "B"."DEPTNO")
2 - filter("B"."DNAME"='SALES')
4 - filter(ROWNUM < 10)위 실행계획을 통해 알 수 있는 정보는 하기와 같다.
- 선행 테이블은 SCOTT.DEPT Table이다.
- DEPT table은 처음엔 full access를 하고 이에 대한 조인 조건 및 필터링 조건(DNAME = SALES)이 존재한다.
- DEPT table에 대한 행제한(ROWNUM) 구문이 존재한다.
- 두 테이블은 조인을 하긴 하지만, 본 실행계획에 조인 방법이 명기되어있지는 않다.
9. SQL 실행순서
FROM > WHERE(*FROM에 기재한 table 출력항목에 대해 WHERE 조건절로 필터링) > GROUP BY > HAVING > SELECT > ORDER BY
10. 인덱스를 효율적으로 사용하는 방법
- EQUI 조건(=, 동등조건), 그 후순위로 범위조건을 명기해야 효율적
- 인덱스 순서와 조건절의 순서가 서로 동일해야 효율적
11. VARCHAR, CHAR 유형의 문자열 비교
- VARCHAR : 길이가 다르다면 짧은 것이 끝날때까지만 비교하고, 길이가 긴 것이 크다고 판단(동등비교의 경우, 서로 다른 문자가 나올때까지 비교하고)
- CHAR : 두 문자열의 크기를 서로 동일하게 만든 후(짧은 문자열에 대해선 space를 추가), 길이를 같게 한 후에 비교
12. SQL SERVER의 TOP 구문
상위 3개의 A행을 출력하되, B행이 동률일 경우 A행을 추가 출력하는 구문은 아래와 같다.
SELECT TOP(3) WITH TIES A,B
FROM TABLE
ORDER BY SAL DESC이때 WITH TIES를 뺀다면 동률 조건없이 행을 출력하게 됨
13. Natural join 유의사항
- Natural join을 사용할 경우 식별자(Alias)를 사용할 수 없다.
SELECT A.EMPNO, B.EMPNO FROM EMP1 A, EMP2 B
NATURAL JOIN EMPNO (X)
SELECT EMPNO
NATURAL JOIN EMP (O)14. USING, ON
- join시 연결조건으로 사용하는 대표적인 구문은 ON이다.
- 이때 연결하는 두 컬럼의 이름이 같을 경우, USING을 사용할 수 있다.
15. 엔터티 유의사항
- 엔터티는 속성값을 가져야 한다.
16. 계층형 쿼리 관련 함수
- SELECT절에 사용할 수 있다.
- CONNECT_BY_ISLEAF : 전개과정에서 해당 데이터가 리프이면 1, 아니면 0 반환
- CONNECT_BY_ISCYCLE : 전개과정에서 해당 데이터가 조상일때 1, 아니면 0 반환
- SYS_CONNECT_BY_PATH : 하위레벨으 컬럼까지 모두 표기할때 사용
- CONNECT_BY_ROOT : Root 노트에 대한 정보를 표기
17. 집계함수에 대한 범위 설정시
- 시작점 : UNBOUNDED PRECEDING or 10000 PRECEDEING
- 끝점 : CURRENT ROW or UNBOUNDED FOLLOWING or 10000 FOLLOWING
18. WHERE절에서의 NULL값 및 집계함수결과로의 반영
SELECT SUM(A.N1) WHERE A.V1 <> B.V1;- 위 SQL에서 WHERE절 조건을 거칠때, A.V1과 B.V1의 값이 하나라도 NULL일 경우, 해당 연산은 포함하지 않는다, 즉 집계함수 결과에 반영되지 않는다.
- 기본적으로 테이블이 두개가 있고, 하나의 테이블에서만 집계함수를 한다면, 먼저 선행된 테이블을 기준으로 테이블의 컬럼들이 후행 테이블의 컬럼들을 비교하면서 조건을 만족하는 행에 대해 출력된다.
19. 유저와 권한
- 사용자가 실행하는 DDL 문장은 기본적으로 그에 대한 적절한 권한이 있어야만 실행할 수 있다.
- DBA 권한을 가진 유저나 System, Sys 등의 상위 유저 및 그에 대한 권한을 가진 유저가 권한을 부여할 수 있다.
- 테이블의 소유자는 해당 테이블의 DML 권한을 다른 유저에게 부여할 수 있다.
- 권한 부여를 편리하게 관리하도록 만들어진 권한의 집합인 ROLE이 존재한다.
20. SQL에서의 대문자/소문자 출력
- UPPER : 대문자로 출력
- LOWER : 소문자로 출력
21. CUBE(A,B)
A,B로 그룹화 일단 하고,
- A항목에 대한 소계
- A항목 전체에 대한 소계
- B항목에 대한 소계(A항목은 NUll로 표기)
