/* Formatted on 2009/11/13 02:23 (Formatter Plus v4.8.8) */
SELECT   d.dname, e.job, SUM (e.sal),
         CASE
            WHEN TO_CHAR (e.hiredate, 'yyyy') = '1981'
               THEN 'YTD 08'
            ELSE 'YTD 09'
         END "YEAR"
    FROM emp1 e, dept1 d
   WHERE e.deptno = d.deptno
   and job in ('ANALYST','CLERK')
GROUP BY d.dname,
         e.job,
         CASE
            WHEN TO_CHAR (e.hiredate, 'yyyy') = '1981'
               THEN 'YTD 08'
            ELSE 'YTD 09'
         END
         

CREATE TABLE DEPT1
(
  DEPTNO  NUMBER(2),
  DNAME   VARCHAR2(14 BYTE),
  LOC     VARCHAR2(13 BYTE)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE UNIQUE INDEX PK_DEPT1 ON DEPT1
(DEPTNO)
LOGGING
TABLESPACE SYSTEM
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE DEPT1 ADD (
  CONSTRAINT PK_DEPT1
 PRIMARY KEY
 (DEPTNO)
    USING INDEX 
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          12K
                NEXT             12K
                MINEXTENTS       1
                MAXEXTENTS       249
                PCTINCREASE      50
                FREELISTS        1
                FREELIST GROUPS  1
               ));

CREATE TABLE EMP1
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE UNIQUE INDEX PK_EMP1 ON EMP1
(EMPNO)
LOGGING
TABLESPACE SYSTEM
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE EMP1 ADD (
  CONSTRAINT PK_EMP1
 PRIMARY KEY
 (EMPNO)
    USING INDEX 
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          12K
                NEXT             12K
                MINEXTENTS       1
                MAXEXTENTS       249
                PCTINCREASE      50
                FREELISTS        1
                FREELIST GROUPS  1
               ));


ALTER TABLE EMP1 ADD (
  CONSTRAINT FK_DEPTNO1 
 FOREIGN KEY (DEPTNO) 
 REFERENCES DEPT1 (DEPTNO));

INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
COMMIT;


INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 800, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  TO_Date( '02/20/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1600, 300, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  TO_Date( '02/22/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 500, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  TO_Date( '04/02/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2975, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  TO_Date( '09/28/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 1400, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  TO_Date( '05/01/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2850, NULL, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  TO_Date( '06/09/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2450, NULL, 10); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  TO_Date( '04/19/1987 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  TO_Date( '11/17/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 5000, NULL, 10); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  TO_Date( '09/08/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1500, 0, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  TO_Date( '05/23/1987 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1100, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 950, NULL, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1300, NULL, 10); 
COMMIT;