CREATE TABLE SAL_BASE (EMPL_ID      VARCHAR2(10) NOT NULL PRIMARY KEY,
                       REST_GUBUN   VARCHAR2(01) NOT NULL );
CREATE TABLE EMPL     (EMPL_ID      VARCHAR2(10) NOT NULL PRIMARY KEY,
                       EMPL_NAME    VARCHAR2(30),
                       DEPT_CODE    VARCHAR2(05) NOT NULL,
                       GRADE_CODE   VARCHAR2(02) );
CREATE TABLE GRADE    (GRADE_CODE   VARCHAR2(02) NOT NULL PRIMARY KEY,
                       GRADE_NAME   VARCHAR2(30) );
CREATE TABLE DEPT_T   (DEPT_CODE    VARCHAR2(05) NOT NULL PRIMARY KEY,
                       DEPT_NAME    VARCHAR2(30) );
--
INSERT INTO DEPT_T VALUES ('10','총무부');
INSERT INTO DEPT_T VALUES ('20','인사부');
INSERT INTO DEPT_T VALUES ('30','영업부');
INSERT INTO DEPT_T VALUES ('40','회계부');
INSERT INTO DEPT_T VALUES ('50','무역부');
INSERT INTO DEPT_T VALUES ('60','전산실');
--
INSERT INTO EMPL VALUES ('90001','홍길동','10','01');
INSERT INTO EMPL VALUES ('90002','박말뚝','20','02');
INSERT INTO EMPL VALUES ('90003','이쁜이','10','03');
INSERT INTO EMPL VALUES ('90004','강태공','20','03');
INSERT INTO EMPL VALUES ('91001','정말로','30','03');
INSERT INTO EMPL VALUES ('91002','전도환','30','02');
INSERT INTO EMPL VALUES ('91003','노타이','40','02');
INSERT INTO EMPL VALUES ('91004','김앵삼','40','01');
INSERT INTO EMPL VALUES ('92001','김대줘','40','04');
INSERT INTO EMPL VALUES ('92002','조심해','10','01');
INSERT INTO EMPL VALUES ('92003','최면제','10','01');
--
INSERT INTO GRADE VALUES ('01','부장');
INSERT INTO GRADE VALUES ('02','과장');
INSERT INTO GRADE VALUES ('03','대장');
INSERT INTO GRADE VALUES ('04','사원');
--
INSERT INTO SAL_BASE VALUES ('90001','1');
INSERT INTO SAL_BASE VALUES ('90002','2');
INSERT INTO SAL_BASE VALUES ('90003','1');
INSERT INTO SAL_BASE VALUES ('90004','2');
INSERT INTO SAL_BASE VALUES ('91001','1');
INSERT INTO SAL_BASE VALUES ('91002','2');
INSERT INTO SAL_BASE VALUES ('91003','1');
INSERT INTO SAL_BASE VALUES ('91004','2');
INSERT INTO SAL_BASE VALUES ('92001','1');
INSERT INTO SAL_BASE VALUES ('92002','2');
INSERT INTO SAL_BASE VALUES ('92003','1');
--
조건.
    SAL_BASE 에는 각 사원에대한 격주휴무 정보를 보관하고 있다.
    REST_GUBUN = '1' 이면 1,3주 휴무자, '2' 이면 2,4주 휴무자이다.
    EMPL  에서는 각 사원에 대한 부서,직급정보및 사원 명칭을 보관한다.
    DEPT_T 는 부서 테이블이며,GRADE는 직급 테이블이다.
    각 테이블의 정보는 다음과 같다.

    EMPL
    열 이름                        널?      유형
    ------------------------------ -------- ----
    EMPL_ID                        NOT NULL VARCHAR2(10)
    EMPL_NAME                               VARCHAR2(30)
    DEPT_CODE                      NOT NULL VARCHAR2(5)
    GRADE_CODE                              VARCHAR2(2)

    DEPT_T
    열 이름                        널?      유형
    ------------------------------ -------- ----
    DEPT_CODE                      NOT NULL VARCHAR2(5)
    DEPT_NAME                               VARCHAR2(30)

    SAL_BASE
    열 이름                        널?      유형
    ------------------------------ -------- ----
    EMPL_ID                        NOT NULL VARCHAR2(10)
    REST_GUBUN                     NOT NULL VARCHAR2(1)

    GRADE
    열 이름                        널?      유형
    ------------------------------ -------- ----
    GRADE_CODE                     NOT NULL VARCHAR2(2)
    GRADE_NAME                              VARCHAR2(30)
  
    이러한 테이블을 이용하여 정보를 다음과 같이 조회하고자한다.

        부서     SEQ  1,3주             2,4주
    --- -------- ---- -------- -------- ------- ---------
      1 총무부      1 부장     최면제   부장    조심해
      2 총무부      2 부장     홍길동
      3 총무부      3 대장     이쁜이
      4 인사부      1                   과장    박말뚝
      5 인사부      2                   대장    강태공
      6 영업부      1 대장     정말로   과장    전도환
      7 회계부      1 과장     노타이   부장    김앵삼
      8 회계부      2 사원     김대줘
해법.
     QUERY :

SELECT T1.SER " ",T2.DEPT_NAME 부서,T1.SEQ ,T3.GRADE_NAME "1,3주",
       T1.AE " ",T4.GRADE_NAME "2,4주",T1.BE " "        
FROM  (SELECT A.RNO SER,A.DEPT_CODE,B.RNO SEQ,
               MAX(DECODE(B.REST_GUBUN,1,GRADE_CODE)) AG,
               MAX(DECODE(B.REST_GUBUN,1,EMPL_NAME)) AE,
               MAX(DECODE(B.REST_GUBUN,2,GRADE_CODE)) BG,
               MAX(DECODE(B.REST_GUBUN,2,EMPL_NAME)) BE        
       FROM ( SELECT A.DEPT_CODE,B.NO,ROWNUM RNO            
              FROM  (SELECT ROWNUM NO FROM USER_TABLES) B,                  
                    (SELECT DEPT_CODE,MAX(CNT) CNT            
                     FROM ( SELECT A.DEPT_CODE,B.REST_GUBUN,COUNT(*) CNT                    
                            FROM   EMPL     A, SAL_BASE B                          
                            WHERE  B.EMPL_ID = A.EMPL_ID
                            GROUP BY A.DEPT_CODE,B.REST_GUBUN)
                      GROUP BY DEPT_CODE
                     ) A
               WHERE B.NO <= A.CNT
             ) A,
            ( SELECT A.NO,A.RNO,B.DEPT_CODE,B.REST_GUBUN,
                     B.GRADE_CODE,B.EMPL_NAME
              FROM ( SELECT ROWNUM NO,A.RNO
                      FROM  (SELECT ROWNUM RNO FROM   USER_TABLES) A,
                            (SELECT A.DEPT_CODE,B.REST_GUBUN,COUNT(*) CNT
                             FROM   EMPL     A,SAL_BASE B
                             WHERE  B.EMPL_ID = A.EMPL_ID
                             GROUP BY A.DEPT_CODE,B.REST_GUBUN) B
                      WHERE  A.RNO <= CNT) A,  
                  (SELECT ROWNUM RNO,DEPT_CODE,REST_GUBUN,
                          GRADE_CODE,EMPL_NAME
                   FROM  (SELECT A.DEPT_CODE,B.REST_GUBUN,
                                 A.GRADE_CODE,A.EMPL_NAME
                          FROM   EMPL     A,SAL_BASE B
                          WHERE  B.EMPL_ID = A.EMPL_ID
                          GROUP BY A.DEPT_CODE,B.REST_GUBUN,
                                   A.GRADE_CODE,A.EMPL_NAME)) B
              WHERE B.RNO = A.NO             ) B
       WHERE B.DEPT_CODE = A.DEPT_CODE
       AND   B.RNO    = A.NO
       GROUP BY A.RNO,A.DEPT_CODE,B.RNO      
      ) T1,
        DEPT_T  T2,
        GRADE T3,

        GRADE T4
WHERE  T2.DEPT_CODE(+)  = T1.DEPT_CODE
AND    T3.GRADE_CODE(+) = T1.AG
AND    T4.GRADE_CODE(+) = T1.BG