귤귤의 대모험

카테시안(Cartesian) 곱을 이용한 조인 – 첨자 LOOP형 처리

이 사례는 대부분의 회사에서 발생할 수 있는 매출과 관련한 처리의 예이다

회사는 고객에게는 상품과 용역을 제공함으로써 매출이 발생한다 특히 이회사는 서비스 용역을 제공하기 전에 대부분의 금액을 고객으로부터 선수로 받는다 회계적인 입장에서 볼 때 미리 받은 대금은 아직 서비스 용역을 제공하지 않은 상태에서 발생한 금액이므로모두 매출로 인정할수 없고 선수금 계정으로 처리해야한다 아래와 같다

위의 테이블을 참조하여 새로운 정보를 출력하는데 그 테이블이 5개의 로우로 분활되어야하며 각 로우의 적용기간에 대한 일수 와 해당 금액을 산정하여 ‘월별매출’ 테이블에 저장되어야 한다.

먼저 테이블을 생성하면서 기존 컬럼형 테이블을 로우 행으로 만들 때 사용할 넘버가 해당된다

CREATE TABLE COPY_T(NO, NO2) AS

SELECT ROWNUM, SUBSTR(TO_CHAR(ROWNUM, '09'), 2, 2)

FROM TEST99

WHERE ROWNUM <=31;

위와 같이 만들면 NO, NO2의 두 개의 컬럼과 31개의 로우 수를 가지는 테이블이 된다

윈도우 함수를 사용하면 쉽게 할 수 있을 것 같다 한번 확인해 보자 답은 다음에~

CREATE TABLE PAYMENT (

SERIAL NUMBER(4),

OCRNC_DATE NUMBER(8),

STRT_D NUMBER(8),

END_D NUMBER(8),

FRRGT_STMD NUMBER(38)

);

INSERT ALL

INTO PAYMENT

VALUES ( 1101, 19970501, 19970520, 19970925, 5000000 )

INTO PAYMENT

VALUES ( 1102, 19970601, 19970615, 19970805, 3800000 )

INTO PAYMENT

VALUES ( 1103, 19970701, 19970712, 19971020, 6780000 )

INTO PAYMENT

VALUES ( 1104, 19970501, 19970507, 19970718, 2890000 );

SELECT

  SERIAL

  , substr(OCRNC_DATE, 1, 4) || y.no2 AS 적용월

  , ROUND((CASE y.NO2 WHEN SUBSTR(END_D, 5, 2) THEN SUBSTR(END_D, 7, 2)

                                                 ELSE TO_CHAR(LAST_DAY(TO_DATE(END_D,'yyyymmdd')),'dd' )

                       END

     -

                     (CASE y.NO2 WHEN substr(STRT_D, 5, 2) THEN SUBSTR(STRT_D,7 , 2) ELSE '01' END )+1)

    *

                     (FRRGT_STMD/(TO_DATE(END_D,'yyyymmdd') - TO_DATE(STRT_D,'yyyymmdd')+1))) AS 월별매출액

FROM PAYMENT x, COPY_T y

WHERE Y.NO BETWEEN SUBSTR(STRT_D, 5, 2) AND SUBSTR(END_D, 5, 2)

AND x.OCRNC_DATE LIKE x.OCRNC_DATE ||'%'

SERIAL 적용월   월별매출액

1101   199705   426357
1101   199706   1162791
1101   199707   1162791
1101   199708   1162791
1101   199709   968992
1102   199706   1242308
1102   199707   2265385
1102   199708   365385
1103   199707   1342574
1103   199708   2080990
1103   199709   2080990
1103   199710   1342574
1104   199705   989726
1104   199706   1227260
1104   199707   712603

카테시안 곱으로 조인을 할때에는 ROWNUM을 활용하여 현재의 데이터와 조인을 하여 사용해야 한다

지금 현재의 데이터는 일련번호 별로 05월부터 09월 까지이다 그렇기 때문에 01부터 31까지의 로우를 사용해도 추후에는  조건절에서 범위를 정하여 자기가 필요한 05부터 09월의 데이터를 추출하는 방식이다

데이터는 CASE문으로 종료일에 해당하는 09월에는 25일 (THEN SUBSTR(END_D, 7, 2))을 가져오고

나머지는 그 달의 마지막날TO_CHAR(LAST_DAY(TO_DATE(END_D,'yyyymmdd')), 'dd' )을 가져오게된다

시작일에 해당하는 05월이 나오면 20일(SUBSTR(STRT_D,7 , 2))을 가져오고 나머지는 01로 치환한다

이렇게 되면 빠른 숫자인  05의 ROWNUM으로 인해

종요일은 05가 아니므로 TO_CHAR(LAST_DAY(TO_DATE(END_D,'yyyymmdd')), 'dd' ) 로 인해 

30일이 치환대어 뺄셈이 시작된다

시작일 05월에는 20일

그래서 결과 값은 10 이때 날자값에 +1를 잊지말자

그 10일로 일할매출액을 구하는 식이

(FRRGT_STMD/(TO_DATE(END_D,'yyyymmdd') - TO_DATE(STRT_D,'yyyymmdd')+1))

위와 같으며 TO_DATE을 사용하여 종요일과 시작일의 일수를 구하여 나누어 일할매출액을 구한다

그렇게 월별로 매출액을 구할수 있게된다

1. TEMP와 TCOM에 존재하는 사번의 교집합을 구한후, TEMP에서 TCOM에 존재하는 사번을 제외시킨 차집합을 구하고, 두결과의 합집합을 구해보자

(SELECT EMP_ID FROM TEMP INTERSECT SELECT EMP_ID FROM TCOM) UNION

(SELECT EMP_ID FROM TEMP MINUS SELECT EMP_ID FROM TCOM);

2. TEMP 테이블과 TCOM의 행의 개수를 카테시안 조인으로 구하여라

SELECT COUNT(*) FROM TEMP, TCOM

3. TEMP에서 사번, 성명, 부서코드, 부서명을 출력하시오

SELECT A.EMP_ID, A.EMP_NAME, A.DEPT_CODE, B.DEPT_NAME

FROM TEMP A, TDEPT B

WHERE B.DEPT_CODE = A.DEPT_CODE;

4. TEMP에 존재하는 직원들 중 과장직급을 가질만한 나이에 포함되는 사람이 누구인지 출력하시오

( EMP_LEVE, TEMP )

SELECT B.EMP_ID,B.BIRTH_DATE FROM EMP_LEVEL A, TEMP B

WHERE B.BIRTH_DATE BETWEEN ADD_MONTHS(SYSDATE,-1* TO_AGE*12)

                                                           AND ADD_MONTHS(SYSDATE,-1* FROM_AGE*12)

                                                           AND B.LEV='대리';

5. TEMP와 EMP_LEVE를 이용해 과장 직급의 연봉 상한/하한 범위내에 있는 직원 사번 성명 직급 SALARY를 출력

SELECT A.EMP_ID, A.EMP_NAME, A.LEV, A.SALARY

FROM TEMP A, EMP_LEVEL B

WHERE A.SALARY BETWEEN FROM_SAL AND TO_SAL

AND B.LEV='과장';

6. 사번, 이름 SALARY, 연봉 상한 금액을 보고자 한다. TEMP와 EMP_LEVEL을 조인하여 결과를 보여주되 연봉의 상하한이 등록되어있지않은 수습사원은 사번, 이름 SALARY까지만 출력하는 QUERY를 구성해보자

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY, B.TO_SAL

FROM TEMP A, EMP_LEVEL B

WHERE B.LEV(+)=A.LEV;

7. TEMP의 자료를 이용해 NON-EQUI JOIN이면서 SELF JOIN이고 QUTER조인인 QUREY를 하나 만들어 보자

사번, 성명, 생일, 자신보다 생일이 빠른 사람의 수를 읽어와 자신보다 생일이 빠른 사람의 수로 정렬하여 출력

SELECT A.EMP_ID, A.EMP_NAME,A.BIRTH_DATE, COUNT(B.BIRTH_DATE)

FROM TEMP A, TEMP B

WHERE B.BIRTH_DATE(+)<A.BIRTH_DATE

GROUP BY A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE

ORDER BY COUNT(B.BIRTH_DATE)

8. TEMP에 속한 수습사원만 순번을 붙여 출력하시오. 단 5번까지만 출력하시오

SELECT ROWNUM, EMP_ID, EMP_NAME

FROM TEMP

WHERE EMP_ID > 0 AND LEV=’수습‘ AND ROWNUM <= 5

9. TEMP 테이블의 자료를 이용하여 SELECT 결과를 3개행씩 묶어 하나의 번호를 부여하는 SQL을 만들어보자

SELECT ROWNUM, CEIL(ROWNUM/3), EMP_ID, EMP_NAME FROM TEMP WHERE EMP_ID>0

10. 강의 ID와 주당 강의 시간과 학점이 같으면 일반으로 표시하고 아니면 특별로 출력하시오

SELECT LEC_ID, DECODE(LEC_TIME, LEC_POINT,’일반‘,’특별’) FROM LECTURE

1. '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

FROM PLAYER

WHERE TEAM_ID = (SELECT TEAM_ID

                                                                        FROM PLAYER

                                                                        WHERE PLAYER_NAME = '정남일')

2. 선수들 중에서 키가 평균 이하인 선수들의 정보를 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

FROM PLAYER

WHERE HEIGHT <= (SELECT AVG(HEIGHT)

                                                                        FROM PLAYER) ORDER BY PLAYER_NAME;

3. 각 부서별 급여 평균보다 더 많이 받는 사원의 사원번호, 이름, 급여를 출력하세요.

SELECT empno,ename,sal

FROM EMP

WHERE sal > all (SELECT AVG(sal)

                                  FROM EMP

                                  GROUP BY deptno)

4. CHICAGO 지역에 근무하는 사원들 중 BLAKE이 직속상관인 사원들의 사원번호, 이름, 직무를 출력하세요.

SELECT e.empno, e.ename, e.job

FROM EMP e, DEPT d

WHERE e.DEPTNO = d.DEPTNO

AND e.mgr = (SELECT empno

                            FROM EMP

                            WHERE ename = 'BLAKE')

                            AND d.loc = 'CHICAGO';

5. 각 부서별로 최고급여를 받는 직원의 Dept_id, ID, Last_name, Salary를 출력하시오.

SQL> SELECT dept_id, id, last_name, salary

FROM s_emp

WHERE (dept_id,salary) IN (SELECT dept_id, max(salary)

                                                          FROM s_emp

                                                          GROUP BY dept_id);

6. '20120501' 부터 '20120502' 사이에 경기가 있는 경기장을 출력하시오

SELECT STADIUM_ID ID, STADIUM_NAME 경기장명

FROM STADIUM A

WHERE EXISTS (SELECT 1

                                   FROM SCHEDULE X

                                   WHERE X.STADIUM_ID = A.STADIUM_ID

                                   AND X.SCHE_DATE BETWEEN '20120501' AND '20120502')

7. 소속팀별 키가 가장 작은 사람들의 정보를 출력하시오

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER

WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)

                                                                 FROM PLAYER

                                                                 GROUP BY TEAM_ID)

ORDER BY TEAM_ID, PLAYER_NAME;

8. 각 부서별로 최고급여를 받는 직원의 Dept_id, ID, Last_name, Salary를 출력하시오.

SELECT dept_id, id, last_name, salary

FROM s_emp

WHERE (dept_id,salary) IN (SELECT dept_id, max(salary)

                                                           FROM s_emp

                                                           GROUP BY dept_id);

9. K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력하시오

SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버

FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO

               FROM PLAYER

               WHERE POSITION = 'MF') P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID ORDER BY 선수명;

10. K리그 선수 중 가장 키가 큰 선수 5명을 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT

                FROM PLAYER

                WHERE HEIGHT IS NOT NULL

                ORDER BY HEIGHT DESC)

WHERE ROWNUM <= 5;

11. 최근 입사한 5명의 사원을 출력하시오

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM (SELECT *

               FROM EMP

               ORDER BY HIREDATE DESC)

WHERE ROWNUM <= 5;

12. 사원의 급여를 내림차순으로 모든 정보를 출력하시오

SELECT * FROM EMP ORDER BY SAL DESC

13. 사원이름과 급여를 최고 높은 순으로 정렬하여 출력하고 순위를 표시하는 컬럼을 생성하여 출력하시오

SELECT ROWNUM RNUM, ENAME, SAL

FROM (SELECT * FROM EMP ORDER BY SAL DESC)

14. 급여를 많이 받는 6 ~ 10번째 사원을 출력

SELECT RNUM, ENAME, SAL

FROM (SELECT ROWNUM RNUM, ENAME, SAL             -- FROM 절의 INLINE VIEW ROWNUM에 대해 별칭 부여

                FROM (SELECT *

                                  FROM EMP

                                  ORDER BY SAL DESC))

WHERE RNUM BETWEEN 6 AND 10;

15. 삼성블루윙즈팀(K02)의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 출력하시오

SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

GROUP BY P.TEAM_ID, T.TEAM_NAME

HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT)

                                                        FROM PLAYER WHERE TEAM_ID ='K02')

16. 사원이 속해있는 부서번호와 부서이름을 출력하시오, 단, 중복 값은 허용하지 않는다

위의 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우

추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 체크해야 한다.

두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 DISTINCT로 중복 제거를 한다.

SELECT DISTINCT d.deptno, d.dname

FROM dept d, emp e

WHERE d.deptno = e.deptno;

-- EXISTS를 사용하는 Subquery로 변경

-- 추출하고자 하는 테이블인 dept d을 FROM절에 놓고 emp테이블은 체크만 하기위해 EXISTS절에 위치시켰으며

이로 인해 수행속도가 대폭 감소하게 된다.

SELECT d.deptno, d.dname

FROM dept d

WHERE EXISTS (SELECT 1

                                  FROM emp e

                                  WHERE e.deptno = d.deptno);