#title SQL초급 [[TableOfContents]] 여러분은 이제 SQL에 대한 감을 잡았으리라 생각합니다. 이제 좀더 깊이 있는 쿼리문을 작성해보도록 하겠습니다. 처음 부분은 집합적인 사고방식을 가지기 위한 쿼리문을 작성해보고 마지막부분 부터 쿼리의 성능에 대한 기본적인 사항들을 살펴보겠습니다. 이미 SELECT의 기본을 익혔으므로, 이제는 문제 해결능력을 길러야 할 때라고 생각합니다. 여러분이 이곳에 나온 문제를 스스로 해결할 수 있다면 여러분은 절대 초보가 아니라고 자신할 수 있습니다. ==== IN_LINE VIEW의 사용 ==== 앞에서 언급했듯이 사용자는 DBMS에게 SQL로 원하는 정보가 무엇인지 요구하고, DBMS는 사용자에게 결과를 테이블 형태로 돌려줍니다. 일반적인 쿼리문은 테이블 형태로 표현되는 것을 알아야 합니다. 아마도 여러분은 아래의 IN_LINE VIEW를 사용해 함으로써 집합적인 사고방식을 가지게 될 것입니다. {{{#!geshi sql SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM (SELECT * 3 FROM EMP 4 WHERE DEPTNO = 20) TABLE1; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 WHERE DEPTNO = 20; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 }}} 첫 번째 쿼리문과 두번째 쿼리문은 성능과 결과가 같습니다. 그러나 FROM 절에 SELECT 문으로 질의를 한 결과도 하나의 테이블로 취급을 하는 것입니다. VIEW라는 것은 가상의 테이블로 질의의 결과를 하나의 테이블로 취급을 하는 것입니다. VIEW는 CREATE VIEW 문으로 만들수 있으며, 다음과 같이 사용합니다. {{{#!geshi sql SQL> CREATE VIEW V_EMP_DEPT20 2 AS 3 SELECT * FROM EMP 4 WHERE DEPTNO = 20; 뷰가 생성되었습니다. SQL> SELECT * FROM V_EMP_DEPT20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7566 JONES MANAGER 7839 81/04/02 2975 20 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7876 ADAMS CLERK 7788 87/05/23 1100 20 7902 FORD ANALYST 7566 81/12/03 3000 20 }}} 즉, VIEW는 사용자가 바라보는 하나의 관점으로 표현될 수가 있습니다. 종이에 구멍을 뚫어놓고 책의 인쇄면을 덮으면 구멍난 부분의 글 밖에 보이지 않을 것입니다. VIEW의 개념을 이렇게 보면 일반적으로는 무난합니다. 이렇게 CREATE VIEW를 사용하면 DBMS의 데이타 사전에는 SELECT문으로 기술한 것들이 기록되고(실행계획도 포함), 하나의 테이블로 취급을 합니다. 그러나 많은 사용자가 바라보는 관점을 모두 표현하는 것은 매우 어려운 일일 것입니다. 그래서 나온 것이 IN_LINE VIEW 입니다. IN_LINE VIEW는 질의를 하는 잠시동안만 FROM 절에서 테이블의 자격을 가지는 질의문의 결과를 말합니다. 즉, 앞의 첫번째 질의문에서 2 ~ 4 라인까지에 속한 SELECT * FROM EMP WHERE DEPTNO = 20 의 결과가 TABLE1이라는 별칭을 가지는 테이블이 되는 것입니다. {{{ 2 FROM (SELECT * 3 FROM EMP 4 WHERE DEPTNO = 20) TABLE1; }}} 이러한 IN_LINE VIEW는 그 활용도가 매우 높습니다. 약간만 복잡해져도 기본적인 SELECT 문으로만 사용자가 원하는 정보가 무엇인지 나타내는 것은 매우 어렵습니다. 이제 IN_LINE VIEW와 다른 여러가지 요소를 응용하는 질의문을 작성해 보도록 하겠습니다. ==== IN_LINE VIEW를 응용한 예제1 ==== 다음은 필자가 데이타베이스를 시작하고 처음으로 가장 어려웠던 쿼리문입니다. 일반적으로집계함수를 사용하고, 또 다른 하나의 컬럼을 SELECT절에 명시해주면 반드시 GROUP BY 절을 사용해야 합니다. 이러한 집계함수와 GROUP BY 와의 관계에서 어떻게 또다른 컬럼을 출력해 낼수 있는가가 문제였습니다. 다음과 같은 쿼리는 쉽게 출력을 낼 수 있습니다. {{{#!geshi sql SQL> -- 사원 각각의 직무에서 가장 많은 월급을 출력 SQL> SELECT JOB, MAX(SAL) 2 FROM EMP 3 GROUP BY JOB; JOB MAX(SAL) --------- ---------- ANALYST 3000 CLE%RK 1300 CLERK 1300 MANAGER 2975 PRESIDENT 5000 SALESMAN 1600 6 개의 행이 선택되었습니다. }}} 그러나 이 쿼리에서 가장 많은 월급을 받는 사원의 사원번호까지 출력을 하려면 상당히 난감합니다. 그러나 우리는 앞서서 다루었던 IN_LINE VIEW를 활용하여 머리를 조금만 쓴다면 쉽게 원하는 정보를 출력할 수 있습니다. 이것을 깨우치신 분은 집합적인 사고방식을 가졌다고 보면 될 것입니다. 필자의 경우는 IN_LINE VIEW를 알고나서야 집합적인 사고방식이란 것이 무엇인가를 알았습니다. 여러분도 이것을 해보시기 전에 반드시 혼자 힘으로 해결하는 버릇을 들여야 할 것입니다. 여러분은 쿼리문을 잘 작성하는 것은 이렇게 출력이 제대로 나오는 것이라고 생각하면 큰 오산일 것입니다. 쿼리문은 사용자가 원하는 정보가 무엇인지 DBMS에게 알려주는 즉, DBMS와 통신을 하는 것입니다. 쿼리문을 작성한다는 것은 이러한 것과 어떤 실행경로를 통하여 원하는 정보를 가져오라는 것까지 내부적으로 포함하고 있다는 것을 염두해 주시기 바랍니다. {{{#!geshi sql SQL> -- 사원 각각의 직무에서 가장 많은 월급을 받는 사원의 EMPNO, JOB, SAL 출력 SQL> SELECT T1.EMPNO, T2.JOB, T2.MAX_SAL 2 FROM EMP T1, 3 (SELECT JOB, MAX(SAL) AS MAX_SAL 4 FROM EMP 5 GROUP BY JOB) T2 6 WHERE T1.JOB = T2.JOB 7 AND T1.SAL = T2.MAX_SAL; EMPNO JOB MAX_SAL ---------- --------- ---------- 7788 ANALYST 3000 7902 ANALYST 3000 1111 CLE%RK 1300 7934 CLERK 1300 7566 MANAGER 2975 7839 PRESIDENT 5000 7499 SALESMAN 1600 7 개의 행이 선택되었습니다. }}} 우리는 SELECT 기본부분에서 어떠한 절이 먼저 수행되는 가를 살펴보았습니다. 가장 먼저 수행되는 것은 FROM 절입니다. 그렇다면 FROM 절에서 테이블의 역할을 수행하는 SELECT 절부터 수행되겠지요. ==== IN_LINE VIEW를 응용한 예제2 ==== {{{#!geshi sql SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 1111 MILLER CLE%RK 7782 82/01/23 1300 10 SQL> SELECT JOB, COUNT(*) 2 FROM EMP 3 GROUP BY JOB; JOB COUNT(*) --------- ---------- ANALYST 2 CLE%RK 1 CLERK 4 --> 찾고자 하는 것 MANAGER 3 PRESIDENT 1 SALESMAN 4 --> 찾고자 하는 것 6 개의 행이 선택되었습니다. SQL> SELECT TBL_2.JOB 2 FROM (SELECT MAX(COUNT) AS MAX_COUNT 3 FROM (SELECT JOB, COUNT(*) AS COUNT 4 FROM EMP 5 GROUP BY JOB) T) TBL_1, 6 (SELECT JOB, COUNT(*) AS COUNT 7 FROM EMP 8 GROUP BY JOB) TBL_2 9 WHERE TBL_1.MAX_COUNT = TBL_2.COUNT; JOB --------- CLERK SALESMAN }}} ==== DECODE 함수의 사용 예 ==== 이에 대한 구체적인 설명은 하지 않도록 하겠습니다. 그러나 DECODE 함수는 그 활용도가 매우높다는 것을 알아야 합니다. DECODE 함수를 이용하면 일반 어플리케이션에서 행할 수 있는 IF 처리를 할 수 있습니다. 그러므로 오라클에서 제공하는 함수와 DECODE 함수(물론 DECODE 함수도 오라클에서 제공합니다.)를 이용하면, 어플리케이션에서의 복잡한 처리를 하지 않아도 되는 것입니다. 물론 데이타를 데이타 처리에 특화된 소프트웨어인 DBMS에서 처리하지 않고 어플리케이션에서 처리한다는 것 자체가 보검으로 벼를 베고 있는 격이 되는 것입니다. 이 책은 SQL에 대한 책이 아니므로 DECODE의 자세한 설명은 생략하도록 하겠습니다. 구체적인 SQL은 다른 서적이나 웹문서 또는 메뉴얼을 참고하시기 바랍니다. 여기서는 하나의 예를 통해서 IF처리를 하는 것을 보여드리도록 하겠습니다. 꼭 분석해서 자기의 것으로 만드셔야 합니다. {{{#!geshi sql SQL> SELECT CEIL(ROWNUM/3), EMPNO FROM EMP; CEIL(ROWNUM/3) EMPNO -------------- ---------- 1 7369 1 7499 1 7521 2 7566 2 7654 2 7698 3 7782 3 7788 3 7839 4 7844 4 7876 4 7900 5 7902 5 7934 5 1111 15 개의 행이 선택되었습니다. SQL> SELECT CEIL(ROWNUM/3) ID, 2 DECODE(MOD(ROWNUM,3), 1, EMPNO, NULL) NO1, 3 DECODE(MOD(ROWNUM,3), 2, EMPNO, NULL) NO2, 4 DECODE(MOD(ROWNUM,3), 0, EMPNO, NULL) NO3 5 FROM EMP; ID NO1 NO2 NO3 ---------- ---------- ---------- ---------- 1 7369 1 7499 1 7521 2 7566 2 7654 2 7698 3 7782 3 7788 3 7839 4 7844 4 7876 4 7900 5 7902 5 7934 5 1111 15 개의 행이 선택되었습니다. SQL> -- CEIL(ROWNUM/3)로 그룹지어서 그 그룹내에서 나머지 1,2,0을 계산하여 MAX를 취함 SQL> SELECT CEIL(ROWNUM/3) ID, 2 MAX(DECODE(MOD(ROWNUM, 3), 1, EMPNO, NULL)) EMPNO1, 3 MAX(DECODE(MOD(ROWNUM, 3), 2, EMPNO, NULL)) EMPNO2, 4 MAX(DECODE(MOD(ROWNUM, 3), 0, EMPNO, NULL)) EMPNO3 5 FROM EMP 6 GROUP BY CEIL(ROWNUM/3); ID EMPNO1 EMPNO2 EMPNO3 ---------- ---------- ---------- ---------- 1 7369 7499 7521 2 7566 7654 7698 3 7782 7788 7839 4 7844 7876 7900 5 7902 7934 1111 SQL> }}} ==== 일련번호 붙이기와 누적 계산 ==== {{{#!geshi sql --일련번호 붙이기 SQL> SELECT EMP_ID.ID, EMP.EMPNO, ENAME, JOB 2 FROM (SELECT E2.EMPNO, 3 (SELECT (COUNT(*)+1) FROM EMP E1 WHERE E1.EMPNO>E2.EMPNO) AS ID 4 FROM EMP E2 5 ORDER BY ID) EMP_ID, EMP 6 WHERE EMP_ID.EMPNO = EMP.EMPNO; ID EMPNO ENAME JOB ---------- ---------- ---------- --------- 1 7934 MILLER CLERK 2 7902 FORD ANALYST 3 7900 JAMES CLERK 4 7876 ADAMS CLERK 5 7844 TURNER SALESMAN 6 7839 KING PRESIDENT 7 7788 SCOTT ANALYST 8 7782 CLARK MANAGER 9 7698 BLAKE MANAGER 10 7654 MARTIN SALESMAN 11 7566 JONES MANAGER 12 7521 WARD SALESMAN 13 7499 ALLEN SALESMAN 14 7369 SMITH CLERK 15 1111 MILLER CLE%RK 15 개의 행이 선택되었습니다. }}} 다음과 같이 테스트용 테이블을 생성합니다. {{{#!geshi sql SQL> CREATE TABLE AA ( 2 COL1 VARCHAR(1), 3 COL2 NUMBER); 테이블이 생성되었습니다. SQL> INSERT INTO AA VALUES ('A', 10); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO AA VALUES ('B', 20); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO AA VALUES ('C', 10); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO AA VALUES ('D', 30); 1 개의 행이 만들어졌습니다. }}} 위의 스크립트로 만들어진 테이블은 다음과 같습니다. {{{ C COL2 - ---------- A 10 B 20 C 10 D 30 }}} 이 테이블을 다름과 같이 아래로 내려가면서 하나씩 더한 결과를 내는것이 문제입니다. {{{ C COL2 COL3 - ---------- ---------- A 10 10 B 20 30 C 10 40 D 30 70 }}} 그럼 누적을 계산해 보겠습니다. {{{#!geshi sql --누적 계산하기 SQL> SELECT COL1, COL2 2 ,(SELECT SUM(T1.COL2) FROM AA T1 WHERE ASCII(T1.COL1) <= ASCII(T2.COL1)) AS COL3 3 FROM AA T2 4 ORDER BY COL1; C COL2 COL3 - ---------- ---------- A 10 10 B 20 30 C 10 40 D 30 70 }}} 여러분은 위의 두 질의문을 이용해서 우선순위 구하기등의 질의문을 작성할 수 있을 것입니다. 저는 처음으로 이러한 문제를 풀고나서 SELECT 절에도 SELECT 절이 포함될 수 있는 것을 알았습니다. 저에게는 너무나도 감동적인 질의문이였습니다. ==== VIEW와 UNION을 이용한 쿼리 ==== 일단 실습을 위한 테이블을 생성합니다. 이것은 VIEW와 UNION의 사용법을 알기위한 실습 예제입니다. 이 예제는 예전에 제가 어떤 분이 질문한 것을 답변을 달은 것인데, 일단은 성능에 대한 고려를 하지 않고 VIEW와 UNION의 사용을 익힌다는 것을 전제로 실습하시기 바랍니다. {{{#!geshi sql --실습을 위한 테이블 생성 SQL> DROP TABLE TEST; 테이블이 삭제되었습니다. SQL> CREATE TABLE TEST( 2 DATE_YYYY_MM CHAR(6), 3 NAME CHAR(10), 4 CODE CHAR(1), 5 MONEY INT); 테이블이 생성되었습니다. SQL> INSERT INTO TEST VALUES ('200101', '홍길동', '1', 100); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200101', '홍길동', '2', 50); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200102', '홍길동', '1', 100); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200102', '홍길동', 'P', 110); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200103', '홍길동', '1', 100); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200103', '홍길동', '2', 50); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200103', '홍길동', 'P', 110); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO TEST VALUES ('200103', '홍길동', 'Q', 60); 1 개의 행이 만들어졌습니다. SQL> SELECT * FROM TEST; DATE_Y NAME C MONEY ------ ---------- - ---------- 200101 홍길동 1 100 200101 홍길동 2 50 200102 홍길동 1 100 200102 홍길동 P 110 200103 홍길동 1 100 200103 홍길동 2 50 200103 홍길동 P 110 200103 홍길동 Q 60 8 개의 행이 선택되었습니다. }}} 이 쿼리는 CODE 값이 1, 2, P, Q 이렇게 4개의 값을 가지고 있는 테이블에서 같은월의 코드값에 1과 P가 같이 있으면 P에 있는 금액을, 2와 Q가 같이 있으면 Q에 있는 금액을 추려내고자 하는 쿼리입니다. P, Q 가 없는 월은 1, 2 코드의 금액을 가져 오는 것입니다. P, Q 코드가 있는 월에는 항상 1,2 코드가 있습니다. 1, 2 코드가 있다해서 P, Q코드가 항상 있는건 아닙니다. {{{#!geshi sql SQL> --중간집합을 위한 뷰 생성 : SELECT 문의 단순화를 위한 뷰입니다 SQL> CREATE VIEW V_T1 2 AS 3 SELECT DATE_YYYY_MM, CODE, NAME, MONEY 4 FROM TEST 5 WHERE CODE = '1' OR CODE = '2'; 뷰가 생성되었습니다. SQL> CREATE VIEW V_T2 2 AS 3 SELECT DATE_YYYY_MM, CODE, NAME, MONEY 4 FROM TEST 5 WHERE CODE = 'P' OR CODE = 'Q'; 뷰가 생성되었습니다. SQL> SELECT * FROM V_T1; DATE_Y C NAME MONEY ------ - ---------- ---------- 200101 1 홍길동 100 200101 2 홍길동 50 200102 1 홍길동 100 200103 1 홍길동 100 200103 2 홍길동 50 SQL> SELECT * FROM V_T2; DATE_Y C NAME MONEY ------ - ---------- ---------- 200102 P 홍길동 110 200103 P 홍길동 110 200103 Q 홍길동 60 SQL> -- 이 뷰는 집계함수가 없이도 GROUP BY를 사용할 수 있다는 것을 보여주는 예제... SQL> -- 책을 보면 모든 GROUP BY, HAVING 문이 집계함수와 같이 사용되는 것 밖에 없어서 SQL> -- GROUP BY 가 꼭 집계함수와 사용되어야 한다는 오해를 하지 않기를... SQL> CREATE VIEW V_T3 2 AS 3 SELECT DATE_YYYY_MM, CODE, NAME, MONEY 4 FROM TEST 5 GROUP BY DATE_YYYY_MM, CODE, NAME, MONEY 6 HAVING CODE = 'P' OR CODE= 'Q'; 뷰가 생성되었습니다. SQL> SELECT * FROM V_T3; DATE_Y C NAME MONEY ------ - ---------- ---------- 200102 P 홍길동 110 200103 P 홍길동 110 200103 Q 홍길동 60 SQL> --UNION ALL을 이용한 결과집합 SQL> SELECT DATE_YYYY_MM, CODE, NAME, MONEY 2 FROM V_T1 3 WHERE NOT EXISTS (SELECT DATE_YYYY_MM 4 FROM V_T2 5 WHERE V_T1.DATE_YYYY_MM = V_T2.DATE_YYYY_MM) 6 UNION ALL 7 SELECT DATE_YYYY_MM, CODE, NAME, MONEY 8 FROM V_T2; DATE_Y C NAME MONEY ------ - ---------- ---------- 200101 1 홍길동 100 200101 2 홍길동 50 200102 P 홍길동 110 200103 P 홍길동 110 200103 Q 홍길동 60 }}} 이제 위의 쿼리문을 모두 이해하고 있다면 여려분은 쿼리문에 대해서 자신이 생기셨을 겁니다.