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 );