SQLP 요점정리 #03 : 2과목 SQL - 1장 SQL기본

2과목 SQL 기본 및 활용

• 20문제
• 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
• 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.

1장 SQL 기본

1.1 SQL 종류

1. DML (Data Manipulation Language) : 데이터 조작어
   • SELECT : 조회
   • INSERT, UPDATE, DELETE : 데이터에 변형을 가하는 명령어
2. DDL (Data Definition Language) : 데이터 정의어
   • CREATE, ALTER, DROP, RENAME : 테이블 등의 데이터 구조를 생성, 변경, 삭제하는 명령어
3. DCL (Data Control Langeage) : 데이터 제어어
   • GRANT, REVOKE : DB 및 객체의 접근, 사용 권한을 주고 회수하는 명령어
4. TCL (Transaction Control Language) : 트랜잭션 제어어
   • COMMIT, ROLLBACK : 트랜잭션을 제어하는 명령어

1.2 CREATE TABLE

1. 기본적인 생성법

CREATE TABLE 테이블명 (
    컬럼명    DATATYPE    [DEFAULT 형식],
    ...
    CONSTRAINT 테이블명_PK PRIMARY KEY (컬럼, ...),
    CONSTRAINT 테이블명_FK FOREIGN KEY (컬럼, ...) REFERENCES 테이블2(컬럼, ...)
);

2. CTAS (Create Table as Select)

• Oracle

CREATE TABLE EMP_COPY
AS SELECT * FROM SCOTT.EMP;

• MS-SQL

SELECT * INTO EMP_COPY FROM SCOTT.EMP;

1.3 TRUNCATE TABLE vs DROP TABLE, DELETE TABLE

• TRUNCATE TABLE
  • DROP TABLE과의 차이점 : Table을 삭제하지 않고 모든 row들을 제거한다.
  • DELETE TABLE과의 차이점 : 시스템 부하가 훨씬 적인 대신에 복구가 불가능하다. (rollback 불가)

1.4 TRANSACTION

특징

• 원자성 (atomicity) : 모두 정상적으로 실행되거나, 실행되지 않은 상태로 되거나 (all or nothing )
• 일관성 (consistency) : 실행전에 잘못된 상태가 아니었다면, 실행후에도 잘못이 있으면 안됨
• 고립성 (isolation) : 도중에 다른 트랜잭션의 영향을 받지 않음
• 지속성 (durability) : 수행후 갱신한 내용은 영구적으로 저장

명령어

• COMMIT : 변경된 상태를 DB에 반영
• ROLLBACK : 트랜잭션 수행 이전 상태로 되돌림
• SAVEPOINT [저장명칭] : 현시점까지만 ROLLBACK이 가능하도록 SAVEPOINT 지정 (MS-SQL에서는 SAVE TRANSACTION [저장명칭])
• ROLLBACK TO [저장명칭] : 해당 SAVEPOINT 까지 ROLLBACK (MS-SQL에서는 ROLLBACK TRANSACTION [저장명칭])

1.5 ROWNUM (Oracle), TOP (MS-SQL)

• ROWNUM : SQL 처리결과 각 행의 임시 일련번호
• 1개의 행만 출력할 경우

SELECT ... FROM ... WHERE ROWNUM = 1;
SELECT ... FROM ... WHERE ROWNUM <= 1;
SELECT ... FROM ... WHERE ROWNUM < 2;

• n개의 행을 출력할 경우

SELECT ... FROM ... WHERE ROWNUM <= n;
SELECT ... FROM ... WHERE ROWNUM < n+1;

• TOP : 출력결과의 행 수를 제한

SELECT TOP( n [PERCENT] [WITH TIES] ) ... FROM ...;

• n : n의 갯수 만큼 행을 출력
• PERCENT : n % 만큼 출력
• WITH TIES : 마지막 행과 동일한 값과 동일한 값은 추가로 같이 출력

1.6 CASE

• 프로그래밍의 IF-THEN-ELSE와 비슷한 표현식입니다.

1. 단순비교 : 해당 값에 따라 분류

CASE value WHEN 1 THEN 'one'
           WHEN 2 THEN 'two'
           ELSE NULL
END

2. 조건비교 : 해당 조건에 따라 분류

CASE WHEN value = 1 THEN 'one'
     WHEN value = 2 THEN 'two'
     ELSE NULL
END

3. DECODE : 단순비교를 보다 짧게 표한하는 Oracle 함수

DECODE(value, 1,'one', 2,'two', NULL)

1.7 NULL

• NULL 관련 문제는 반드시 출제된다.

NVL (Oracle), ISNULL (MS-SQL)

해당 값이 NULL일 경우 2번째 인자의 값을 출력한다.

SELECT NVL(SAL,0) FROM EMP; -- Oracle
SELECT ISNULL(SAL,0) FROM EMP; -- MS-SQL

COALESCE

인수 중 최초로 NULL이 아닌 값을 출력한다. 모두 NULL인 경우 NULL이 출력된다.

SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL FROM EMP;

위 문장은 아래의 CASE문으로 표현한 것과 같다.

SELECT ENAME, COMM, SAL,
       CASE WHEN COMM IS NOT NULL THEN COMM
            WHEN SAL IS NOT NULL THEN SAL
            ELSE NULL
       END COAL
  FROM EMP;

1.8 집계함수 (Aggregate function)

• GROUP 당 단 1개의 값만 출력하는 함수
• SELECT , HAVING, ORDER BY 절에서 사용할 수 있다.
• 집계함수에 들어온 NULL값은 계산에 포함시키지 않는다. (참고로 산술연산에 NULL 값이 포함되어 있으면 결과는 무조건 NULL이다.)

SELECT

GROUP BY에 명시된 컬럼, 집계함수의 결과값 만 가능하다.

GROUP BY

여기에 명시한 컬럼, 표현식 단위로 집계함수를 이용하여 계산한다.

WHERE

여기에 적은 조건에 부합하는 row만 집계함수 계산에 참여한다. (GROUP BY 이전 필터링 역할)

HAVING

집계된 결과 중 HAVING 절의 조건에 만족하는 것만 출력한다. (GROUP BY 이후 필터링 역할)

SELECT D.DEPTNO, MAX(DNAME) AS DNAME, SUM(SAL) AS SUM, AVG(SAL) AS AVG
  FROM DEPT D, EMP E
 WHERE D.DEPTNO = E.DEPTNO
   AND D.DEPTNO IN (10, 30)     -- DEPTNO가 10, 30 인것만 집계로 계산
 GROUP BY D.DNAME
HAVING SUM(SAL) >= 10000        -- 계산된 결과중 10000이 넘는 것만 출력
 ORDER BY SUM(SAL);

SELECT 문장 실행순서

1. FROM : 대상 Table 검색
2. WHERE : 검색 대상이 아닌 데이터 제거
3. GROUP BY : 집계할 단위로 그룹화
4. HAVING : 집계한 것중 조건에 맞는 것만 선택
5. SELECT : 출력할 값들을 계산 및 함수적용
6. ORDER BY : 데이터를 정렬하여서 출력

CASE - GROUP BY

1정규화로 모델링된 테이블들에서 보고서를 만들때 많이 사용되는 기법이다.

예를 들어서 각 부서별로 입사월별 급여합계가 필요한 경우가 있다고 가정했을 때

먼저 부서, 사원명, 입사월, 급여를 출력해보자.

SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) M, SAL
  FROM SCOTT.EMP
 ORDER BY SAL DESC;

위 문장을 이용하여 각 월별로 컬럼을 생성하여 해당 월에 급여를 출력해보자.
(CASE랑 기능이 같은 DECODE를 사용하겠다.)

SELECT ENAME, DEPTNO,
       DECODE(M, 1,SAL) AS M01,
       DECODE(M, 2,SAL) AS M02,
       DECODE(M, 3,SAL) AS M03,
       DECODE(M, 4,SAL) AS M04,
       DECODE(M, 5,SAL) AS M05,
       DECODE(M, 6,SAL) AS M06,
       DECODE(M, 7,SAL) AS M07,
       DECODE(M, 8,SAL) AS M08,
       DECODE(M, 9,SAL) AS M09,
       DECODE(M,10,SAL) AS M10,
       DECODE(M,11,SAL) AS M11,
       DECODE(M,12,SAL) AS M12
  FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) M, SAL
          FROM SCOTT.EMP )
 ORDER BY DEPTNO, ENAME;

이제 위 문장을 부서별로 GROUP BY 하는건 어렵지 않을 것이다.
참고로 NULL인 경우 0으로 표시하도록 NVL을 이용하였다.

SELECT DEPTNO,
       NVL(SUM(DECODE(M, 1,SAL)),0) AS M01,
       NVL(SUM(DECODE(M, 2,SAL)),0) AS M02,
       NVL(SUM(DECODE(M, 3,SAL)),0) AS M03,
       NVL(SUM(DECODE(M, 4,SAL)),0) AS M04,
       NVL(SUM(DECODE(M, 5,SAL)),0) AS M05,
       NVL(SUM(DECODE(M, 6,SAL)),0) AS M06,
       NVL(SUM(DECODE(M, 7,SAL)),0) AS M07,
       NVL(SUM(DECODE(M, 8,SAL)),0) AS M08,
       NVL(SUM(DECODE(M, 9,SAL)),0) AS M09,
       NVL(SUM(DECODE(M,10,SAL)),0) AS M10,
       NVL(SUM(DECODE(M,11,SAL)),0) AS M11,
       NVL(SUM(DECODE(M,12,SAL)),0) AS M12
  FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) M, SAL
          FROM SCOTT.EMP )
 GROUP BY DEPTNO;

1.9 JOIN

JOIN은 한번에 2개의 집합간에만 가능하다.
FROM A, B, C, D 가 있더라도 이 중 2개를 먼저 JOIN하고 그 결과를 다음과 JOIN하는 식으로 처리된다.

JOIN에 대해서는 2장에서 자세히 다루려했으나...
거기에 시간을 빼았겨서는 안될듯하여 그냥 여기서 간단히 다루겠습니다.

JOIN 연산

• EQUI JOIN

일반적으로 PK - FK 의 관계에서 많이 이루어진다.
WHERE 절이나 ON 절에서 = 연산으로 JOIN 한다.

• Non EQUI JOIN

=연산이 아닌 연산으로 JOIN을 수행한다. ( BETWEEN, >, >=, <, <=, <>, != )

예를 들어 SALGRADE Table에 각 급여구간별 GRADE가 저장된 경우, EMP의 각 사원별로 급여등급을 구하고자할때 다음과 같이 작성하면 된다.

SELECT E.ENAME, E.DEPTNO, E.SAL, G.GRADE
  FROM SCOTT.EMP E, SCOTT.SALGRADE G
 WHERE E.SAL BETWEEN G.LOSAL AND G.HISAL
 ORDER BY SAL DESC;

JOIN 종류

• INNER JOIN
  • 양쪽 Table 모두 조건에 해당되는 데이터만 출력 (교집합)
  • Natural JOIN : INNER JOIN 에서 중복된 컬럼은 한번만 출력됨
    • 모든 일치된 칼럼에 대해서 JOIN 되지만, USING으로 원하는 컬럼만 선택이 가능 (실제로 해보니 USING 안쓰면 ERROR)

SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

SELECT * EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

SELECT * FROM EMP NATUAL JOIN DEPT USING (DEPTNO);

• OUTER JOIN
  • LEFT OUTER JOIN : 왼쪽 Table의 모든 row 출력, 오른쪽 Table에는 조건에 맞으면 출력하고 아니면 NULL
  • RIGHT OUTER JOIN : 오른쪽 Table의 모든 row 출력, 왼쪽 Table에는 조건에 맞으면 출력하고 아니면 NULL
  • FULL OUTER JOIN : 좌,우측을 모두 읽어서 서로 상대에게 있으면 출력 없으면 NULL (LEFT OUTER와 RIGHT OUTER를 UNION 한거랑 결과가 같음)

SELECT * FROM SCOTT.EMP E LEFT JOIN SCOTT.DEPT D ON E.DEPTNO = D.DEPTNO;

SELECT * FROM SCOTT.EMP E RIGHT JOIN SCOTT.DEPT D ON E.DEPTNO = D.DEPTNO;

SELECT * FROM SCOTT.EMP E FULL JOIN SCOTT.DEPT D ON E.DEPTNO = D.DEPTNO;

• CROSS JOIN (CARTESIAN PRODUCT)
  • 양쪽 Table의 모든 row 들을 모두 관계짓는다. (A에 10개 row, B에 5개 row가 있을 경우 결과는 10 x 5 = 50개가 된다.)

SELECT * FROM SCOTT.EMP CROSS JOIN SCOTT.DEPT;

SELECT * FROM SCOTT.EMP, SCOTT.DEPT;