2012년 12월 24일 SQL 전문가, 개발자 자격증이 국가공인으로 승격하였다.
이에 따라 기존 자격증 취득자는 재시험을 봐야 국가공인으로 인증해준다는 것이다.
시험은 1월 23일인데 재시험 일정을 일주일 전에 알게 되었다.
응시비 무료에 과목 면제의 특혜(?)가 주어진다.
면제된 과목인 데이터 모델링의 이해를 제외한 시험에 나왔었던 문제나 헷깔렸던 내용,
내가 잘 모르는 내용 위주로 정리해보았다.
1. CHAR VS VARCHR
CHAR에서는 물자열을 비교할 때 공백(BLANK)을 채워서 비교하는 방법을 사용한다. 공백 채우기
비교에서는 우선 짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한다.
그리고 앞에서부터 한 문자씩 비교한다. 그렇기 때문에 끝의 공백만 다른 문자열은 같다고 판단한다.
그에 반해 VARCHAR 유형에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로
끝의 공백이 다르면 다른 문자로 판단한다.
ex) CHAR 유형 'AA' = 'AA '
VARCHAR 유형 'AA' <> 'AA '
2. CHECK 제약조건
CHECK(COL1 IN (1,2,3))
CHECK 제약조건이 걸린 컴럼이 NOT NULL인 경우 NULL 값이 들어갈 수 없지만 NOT NULL이
아니면 NULL 값 허용
3. SELECT 문장을 통한 테이블 생성 사례
1) ORACLE
CTAS : CREATE TABLE ~ AS SELECT ~
2) SQL SERVER
SELECT * INTO TABLE1FROM TABLE2
※ 기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용되고, 기본키, 고유키,
외래키 , CHECK 등의 다른 제약조건은 없어진다.
4. RENAME COLUMN
1) ORACLE
ALTER TABLE XPDLQMFAUD
RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 컬럼명;
2) SQL SERVER
SP_RENAME 변경해야할 컬럼명, 새로운 컬럼명, 'COLUMN';
5. RENAME TABLE
1) ORACLE
RENAME 변경 전 테이블 명 TO 변경 후 테이블명;
2) SQL SERVER
SP_RENAME 변경 전 테이블명, 변경 후 테이블명;
6. 연산자의 종류
1) 비교 연산자 : =, >, >=, <, <=
2) SQL 연산자 : BETWEEN A AND B, IN(LIST), LIKE '비교문자열', IS NULL
3) 논리 연산자 : AND, OR, NOT
7. 연산자의 우선순위
괄호() -> NOT 연산자 -> 비교 연산자, SQL 비교연산자 -> AND -> OR
8. ROWNUM
WHERE 절에서 행의 개수를 제한하는 목적으로 사용
한 행만 가져오고 싶을 때 WHERE ROWNUM = 1;
WHERE ROWNUM <= 1;
WHERE ROWNUM < 2;
두건 이상의 N행을 가져오고 싶을 때 WHERE ROWNUM = N; (X)
WHERE ROWNUM <= N; (O)
WHERE ROWNUM < N+1; (O)
9. 단일행 함수의 종류
1) 문자형 함수 : LOWER, UPPER, SUBSTR / SUBSTRING, LENGTH / LEN, LTRIM, RTRIM,
TRIM, ASCII
2) 숫자형 함수 : ABS, MOD, ROUND, TRUNC, SIGN, CHR / CHAR, CEIL / CEILING,
FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
3) NULL 관련 함수 : NVL / ISNULL, NULLIF, COALESCE
※ IS NULL은 함수가 아니라 연산자이다.
10. CEIL / CEILING() VS FLOOR()
1) CEIL / CEILING() : 숫자보다 크거나 같은 최소 정수를 리턴한다.
ex) CEIL(38.123) / CEILING(38.123) -> 39
CEILING(-38.123) -> 38
2) FLOOR() : 숫자보다 작거나 같은 최대 정수를 리턴한다.
ex) FLOOR(38.123) -> 38
FLOOR(-38.123) -> -39
※ CEILING은 천장, FLOOR은 바닥을 연상해서 외우면 좋을 듯 싶다.
11. NVL / ISNULL 함수를 이용해 공집합을 9999로 바꾸기
SELECT NVL(MGR, 9999) MGR FROM EMP WHERE ENAME = 'JSC';
데이터를 찾을 수 없다.
-> NVL / ISNULL 함수는 NULL 값을 대상으로 다른 값으로 바꾸는 함수이지 공집합을
대상으로 하지 않는다.
SELECT NVL(MAX(MGR), 9999) MGR FROM EMP WHERE ENAME = 'JSC';
-> 집계함수를 인수로 한 NVL / ISNULL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 999로
출력하게 한다. 다른 함수와 달리 집계함수나 SCALAR SUBQUERY의 경우는 인수의 결과 값이
공집합인 경우에도 NULL을 출력한다.
12. NULLIF 함수
NULLIF(EXPR1, EXPR2)
EXPR1이 EXPR2와 같으면 NULL, 다르면 EXPR1을 리턴
13. COALESCE 함수
COALESCE(EXPR1, EXPR2, ...)
임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다.
만약 모든 EXPR이 NULL이면 NULL을 리턴
14. 집계함수
COUNT(*) : NULL 값을 포함한 행의 수
COUNT(표현식) : 표현식의 값이 NULL 값인 것을 제외한 행의 수
SUM() : NULL 값을 제외한 합계
AVG() : NULL 값을 제외한 평균
※ 조건절에 해당하는 데이터가 없을 때 COUNT(*)의 결과 값은 0
|
COL1 |
COL2 |
COL3 |
COL4 |
|
NULL |
NULL |
50 |
30 |
|
30 |
20 |
10 |
30 |
|
NULL |
10 |
NULL |
NULL |
SUM(COL1) + SUM(COL2 + COL3) + SUM(COL4)의 값은?
1.120 2.180 3.NULL 4.90
답은 120
15. GROUP BY 절
1) GROUP BY 절에서는 ALIAS명을 사용할 수 없다.
2) WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
3) HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.
4) GROUP BY, HAVING 절에는 SELECT 절에 정의되지 않은 컬럼은 사용 못함
※ ORDER BY 절을 SELECT 절에 정의되지 않은 컬럼 사용 가능
16. NULL의크기
ORACLE에서는 NULL 값을 가장 큰 값으로 간주
SQL SERVER에서는 NULL 값을 가장 작은 값으로 간주
17. NATURAL JOIN
1) 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
2) NATURAL JOIN이 명시되면 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을
정의할 수 없다.
3) JON에 사용된 컬럼들은 같은 데이터 유형이어야 하며, ALIAS나 텡블명과 같은 접두사를
붙일 수 없다.
18. USING 조건절
FROM 절에 USING 조건절을 이용하면 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만
선택적으로 EQUI JOIN을 할 수 있다.
SELECT * FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO);
※ JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.
19. UNION VS UNION ALL
1) UNION : 여러개의 SQL의 결과에 대한 합집합으로 결과에서 모든 중복된 행을 하나의 행으로
만든다.
2) UNION ALL : 중복된 행도 그대로 결과에 표시한다.
20. 서브쿼리
1) 서브쿼리란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.
2) 서브쿼리는 메인커리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수
없다. 질의 결과에 서브쿼리의 칼럼을 표시해야 한다면 조인방식으로 변환하거나 함수,
스칼라 서브쿼리 등을 사용해야 한다.
3) 단일행 서브쿼리
서브쿼리가 단일행 비교 연산자 (=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과건수가
반드시 1건 이하이어야 한다.
4) 다중행 서브쿼리
서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중행 비교연산자 (IN, ALL, ANY, SOME)와
함께 사용해야 한다.
ex) IN (서브쿼리), ALL(서브쿼리), ANY(서브쿼리), EXISTS(서브쿼리)
5) 서브쿼리는 서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과집합이 생성된다.
예를들어, 메인쿼리로 조직(1), 서브쿼리로 사원(M) 테이블을 사용하면 결과집합은
조직(1) 레벨이 된다.
6) 서브쿼리에서는 ORDER BY 절을 사용하지 못한다. ORDER BY 절은 SELECT 절에서 오직 한개만
올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 한다.
21. ORACLE의 INDEX 액세스 기법의 종류
INDEX UNIQU SCAN, INDEX RANGE SCAN, INDEX RANGE SCAN DESCNDING,
INDEX FULL SCAN, FAST FULL INDEX SCAN, INDEX SKIP SCAN
22. 계층형 질의
1) START WITH 절은 계층구조 전개이 시작 위치를 지정하는 구문이다.
2) CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는 구문이다.
3) 루트 데이터는 LEVEL 1이다. (0이 아님)