sql cookbook 数据库脚本 for db2

最近在看sql cookbook的电子书籍，故整了个数据库打算测试下相关sql脚本，网上都是sql servle和mysql的，自己看着改了下，以下是DB2脚本。

set schema="COOKBOOK";

CREATE TABLE COOKBOOK.emp
    (
      EMPNO INT NOT NULL ,
      ENAME VARCHAR(32) DEFAULT NULL ,
      JOB VARCHAR(32) DEFAULT NULL ,
      MGR VARCHAR(32) DEFAULT NULL ,
      HIREDATE DATE DEFAULT NULL ,
      SAL int DEFAULT NULL ,
      COMM VARCHAR(16) DEFAULT NULL ,
      DEPTNO VARCHAR(8) DEFAULT NULL ,
      PRIMARY KEY ( EMPNO )
    )
in     cookbook
index in cookbook_idx;
 
--
 
-- Dumping data for table emp
 
--
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7369 ,
          'SMITH' ,
          'CLERK' ,
          '7902' ,
          '1980-12-17' ,
          800 ,
          NULL ,
          '20'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7499 ,
          'ALLEN' ,
          'SALESMAN' ,
          '7698' ,
          '1981-02-20' ,
          1600 ,
          '300' ,
          '30'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7521 ,
          'WARD' ,
          'SALESMAN' ,
          '7698' ,
          '1981-02-22' ,
          1250 ,
          '500' ,
          '30'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7566 ,
          'JONES' ,
          'MANAGER' ,
          '7839' ,
          '1981-04-02' ,
          2975 ,
          NULL ,
          '20'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7654 ,
          'MARTIN' ,
          'SALESMAN' ,
          '7698' ,
          '1981-09-28' ,
          1250 ,
          '1400' ,
          '30'
 
        );
        

 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7698 ,
          'BLAKE' ,
          'MANAGER' ,
          '7839' ,
          '1981-05-01' ,
          2850 ,
          NULL ,
          '30'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7782 ,
          'CLARK' ,
          'MANAGER' ,
          '7839' ,
          '1981-06-09' ,
          2450 ,
          NULL ,
          '10'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7788 ,
          'SCOTT' ,
          'ANALYST' ,
          '7566' ,
          '1982-12-09' ,
          3000 ,
          NULL ,
          '20'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7839 ,
          'KING' ,
          'PRESIDENT' ,
          NULL ,
          '1981-11-17' ,
          5000 ,
          NULL ,
          '10'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7844 ,
          'TURNER' ,
          'SALESMAN' ,
          '7698' ,
          '1981-09-08' ,
          1500 ,
          '0' ,
          '30'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7876 ,
          'ADAMS' ,
          'CLERK' ,
          '7788' ,
          '1983-01-12' ,
          1100 ,
          NULL ,
          '20'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7900 ,
          'JAMES' ,
          'CLERK' ,
          '7698' ,
          '1981-12-03' ,
          950 ,
          NULL ,
          '30'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7902 ,
          'FORD' ,
          'ANALYST' ,
          '7566' ,
          '1981-12-03' ,
          3000 ,
          NULL ,
          '20'
 
        );
 
INSERT  INTO emp
        ( EMPNO ,
          ENAME ,
          JOB ,
          MGR ,
          HIREDATE ,
          SAL ,
          COMM ,
          DEPTNO
 
        )
VALUES  ( 7934 ,
          'MILLER' ,
          'CLERK' ,
          '7782' ,
          '1982-01-23' ,
          1300 ,
          NULL ,
          '10'
 
        );
       
       
 
--
 
-- table dept
 
--
 
CREATE TABLE COOKBOOK.dept
    (
      DEPTNO INT NOT NULL ,
      DNAME VARCHAR(32) DEFAULT NULL ,
      LOC VARCHAR(32) DEFAULT NULL ,
      PRIMARY KEY ( DEPTNO )
    )
    in cookbook
    index in cookbook_idx;
 
INSERT  INTO dept
        ( DEPTNO, DNAME, LOC )
VALUES  ( 10, 'ACCOUNTING', 'NEW YORK' );
 
INSERT  INTO dept
        ( DEPTNO, DNAME, LOC )
VALUES  ( 20, 'RESEARCH', 'DALLAS' );
 
INSERT  INTO dept
        ( DEPTNO, DNAME, LOC )
VALUES  ( 30, 'SALES', 'CHICAGO' );
 
INSERT  INTO dept
        ( DEPTNO, DNAME, LOC )
VALUES  ( 40, 'OPERATIONS', 'BOSTON' );


 
--
 
-- table t1
 
--
 
CREATE TABLE COOKBOOK.T1
    (
      ID INT NOT NULL ,
      PRIMARY KEY ( ID )
    )
 in cookbook
 index in cookbook_idx;
INSERT  INTO t1
        ( ID )
VALUES  ( 1 );


 
--
 
-- table t10
 
--
 
CREATE TABLE COOKBOOK.T10
    (
      ID INT NOT NULL ,
      PRIMARY KEY ( ID )
    )
    in cookbook
    index in cookbook_idx;
 
INSERT  INTO t10
        ( ID )
VALUES  ( 1 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 2 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 3 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 4 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 5 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 6 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 7 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 8 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 9 );
 
INSERT  INTO t10
        ( ID )
VALUES  ( 10 );
 
CREATE TABLE COOKBOOK.emp_bonus
    (
      empno INT ,
      received date ,
      TYPE INT
    )
    in cookbook
    index in cookbook_idx;
    
INSERT  INTO emp_bonus
VALUES  ( 7934, '2005-5-17', 1 );
INSERT  INTO emp_bonus
VALUES  ( 7934, '2005-2-15', 2 );
INSERT  INTO emp_bonus
VALUES  ( 7839, '2005-2-15', 3 );
INSERT  INTO emp_bonus
VALUES  ( 7782, '2005-2-15', 1 );