/*==================================================================*/
/* 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,
  ENAME  VARCHAR(50),
  JOB  VARCHAR(50),
  MGR  INTEGER,
  HIREDATE  DATE,
  SALARY  NUMERIC(7,2),
  COMMISSION  NUMERIC(7,2),
  SAL_ALL  NUMERIC(8,2),
  SALGRADE INTEGER,
  DEPTNO  INTEGER  NOT NULL,

  PRIMARY KEY (EMPNO)
);



CREATE TABLE DEPT2 (
  DEPTNO  INTEGER  NOT NULL,
  DNAME  VARCHAR(30),
  LOCATION  VARCHAR(30),

  PRIMARY KEY (DEPTNO)
);

CREATE TABLE SALGRADE2 (
  GRADE  INTEGER  NOT NULL,
  LOW_SAL  NUMERIC(7,2),
  HIGH_SAL  NUMERIC(7,2),

  PRIMARY KEY (GRADE)
);



CREATE TABLE SALARIES2 (
  DEPTNO  INTEGER,
  DNAME  VARCHAR(30),
  JOB_CODE  CHAR(30),
  SALARY  NUMERIC(10,2)
);





/*==================================================================*/
/*  Foreign Keys                                                    */
/*==================================================================*/
ALTER TABLE EMP2
   ADD CONSTRAINT FK_DEPT2_EMP2 FOREIGN KEY (DEPTNO) REFERENCES DEPT2(DEPTNO);  



ALTER TABLE BESTELLUNG
   ADD CONSTRAINT FK_ARTIKEL_BESTELLUNG FOREIGN KEY (ARTNR) REFERENCES ARTIKEL(ARTNR);  




/*==================================================================*/
/*  Check-Constraints                                               */
/*==================================================================*/
ALTER TABLE BESTELLUNG
     ADD CONSTRAINT CH_ATTRIBUT1 CHECK (
  Menge >0
);


/*==================================================================*/
/*  Indexes                                                         */
/*==================================================================*/




-- 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, '17-DEC-80', 800, 20);


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

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


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7566, 'JONES', 'MANAGER', 7839, '02-APR-81', 2975, 20);

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


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, DEPTNO)
 VALUES (7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-81', 2850, 30);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY, DEPTNO)
 VALUES (7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, 10);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-82', 3000, 20);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7839, 'KING', 'PRESIDENT', '17-NOV-81', 5000, 10);


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


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7876, 'ADAMS', 'CLERK', 7788, '12-JUN-83', 1100, 20);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7900, 'JAMES', 'CLERK', 7698, '03-DEC-81', 950, 30);

INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7902, 'FORD', 'ANALYST', 7566, '03-DEC-81', 3000, 20);


INSERT INTO EMP2 (  EMPNO, ENAME, JOB, MGR,  HIREDATE, SALARY,  DEPTNO)
 VALUES (7934, 'MILLER', 'CLERK', 7782, '23-JAN-82', 1300, 10);