/*==================================================================*/
/* Project Filename: D:\Daten\DP60\DBS-Dev-2\oracle\dbs\Labor pl_sql.sql*/
/* Project Name:     Projekt HS-Harz                                 */
/* Author:           HS-Harz                                         */
/* DBMS:             Firebird                                        */
/* Copyright:        Copyright by ???                                */
/* Generated on:     30.06.2006 20:45:38                             */
/*==================================================================*/


/*==================================================================*/
/*  Tables                                                          */
/*==================================================================*/
CREATE TABLE EMP2 (
  EMPNO  INTEGER  NOT NULL PRIMARY KEY,
  ENAME  VARCHAR2(50),
  JOB  VARCHAR2(50),
  MGR  INTEGER,
  HIREDATE  DATE,
  SALARY  NUMBER(7,2),
  COMMISSION  NUMBER(7,2),
  SAL_ALL  NUMBER(8,2),
  SALGRADE INTEGER,
  DEPTNO  INTEGER  NOT NULL
);
CREATE TABLE DEPT2 (
  DEPTNO  INTEGER  NOT NULL PRIMARY KEY,
  DNAME  VARCHAR2(30),
  LOCATION  VARCHAR2(30)
);
CREATE TABLE SALGRADE2 (
  GRADE  INTEGER  NOT NULL PRIMARY KEY,
  LOW_SAL  NUMBER(7,2),
  HIGH_SAL  NUMBER(7,2)
);
CREATE TABLE SALARIES2 (
  DEPTNO  INTEGER,
  DNAME  VARCHAR2(30),
  JOB_CODE  CHAR(30),
  SALARY  NUMBER(10,2)
);
/*==================================================================*/
/*  Foreign Keys                                                    */
/*==================================================================*/
ALTER TABLE EMP2
   ADD CONSTRAINT FK_DEPT2_EMP2 FOREIGN KEY (DEPTNO) REFERENCES DEPT2(DEPTNO);  




-- Daten

insert into dept2(DEPTNO, DNAME, LOCATION)
  VALUES (10, 'ACCOUNTING', 'NEW YORK');

insert into dept2(DEPTNO, DNAME, LOCATION)
  VALUES (20, 'RESEARCH', 'DALLAS');

insert into dept2(DEPTNO, DNAME, LOCATION)
  VALUES (30, 'SALES', 'CHICAGO');

insert into dept2(DEPTNO, DNAME, LOCATION)
  VALUES (40, 'OPERATIONS', 'BOSTON');

insert into dept2(DEPTNO, DNAME, LOCATION)
  VALUES (50, 'SOFTWARE', 'REDMOND');

insert into dept2(DEPTNO, DNAME, LOCATION)
  VALUES (60, 'MARKETING', 'LOS ANGELES');






INSERT INTO SALGRADE2 ( GRADE, LOW_SAL, HIGH_SAL)
  VALUES (1, 700, 1200 );

INSERT INTO SALGRADE2 ( GRADE, LOW_SAL, HIGH_SAL)
  VALUES (2, 1201, 1400);


INSERT INTO SALGRADE2 ( GRADE, LOW_SAL, HIGH_SAL)
  VALUES (3, 1401, 2000);

INSERT INTO SALGRADE2 ( GRADE, LOW_SAL, HIGH_SAL)
  VALUES (4, 2001, 3000);


INSERT INTO SALGRADE2 ( GRADE, LOW_SAL, HIGH_SAL)
  VALUES (5, 3001, 9999);





INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, DEPTNO)
 VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17.12.1980'), 800, 20);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, COMMISSION, DEPTNO)
 VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20.02.1981'), 1600, 300, 30);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, COMMISSION, DEPTNO)
 VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22.02.1981'), 1250, 500, 30);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('02.04.1981'), 2975, 20);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, COMMISSION, DEPTNO)
 VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28.09.1981'), 1250, 1400, 30);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, DEPTNO)
 VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('01.05.1981'), 2850, 30);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, DEPTNO)
 VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('09.06.1981'), 2450, 10);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09.12.1982'), 3000, 20);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, HIREDATE, SALARY,  DEPTNO)
 VALUES (7839, 'KING', 'PRESIDENT', to_date('17.11.1981'), 5000, 10);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, COMMISSION, DEPTNO)
 VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('08.09.1981'), 1500, 0, 30);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12.06.1983'), 1100, 20);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('03.12.1981'), 950, 30);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('03.12.1981'), 3000, 20);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23.06.1982'), 1300, 10);