2010年8月1日 17:20:53 发布:madgoat
CREATE TABLE emp
(
EMPNO INT NOT NULL ,
ENAME VARCHAR(32) DEFAULT NULL ,
JOB VARCHAR(32) DEFAULT NULL ,
MGR VARCHAR(32) DEFAULT NULL ,
HIREDATE DATETIME DEFAULT NULL ,
SAL int DEFAULT NULL ,
COMM VARCHAR(16) DEFAULT NULL ,
DEPTNO VARCHAR(8) DEFAULT NULL ,
PRIMARY KEY ( EMPNO )
)
--
-- 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 dept
(
DEPTNO INT NOT NULL ,
DNAME VARCHAR(32) DEFAULT NULL ,
LOC VARCHAR(32) DEFAULT NULL ,
PRIMARY KEY ( DEPTNO )
)
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 T1
(
ID INT NOT NULL ,
PRIMARY KEY ( ID )
)
INSERT INTO t1
( ID )
VALUES ( '1' )
--
-- table t10
--
CREATE TABLE T10
(
ID INT NOT NULL ,
PRIMARY KEY ( ID )
)
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 emp_bonus
(
empno INT ,
received DATETIME ,
TYPE INT
)
go
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 )