最近在看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 );