SQL-COOKBOOK-示例数据库脚本-for-SQLserver.doc

SQL COOKBOOK 示例数据库脚本 for SQLserver
2010年8月1日 17:20:53 发布:madgoat

 脚残心不残，拾起之前取自伟哥的SQL COOKBOOK，准备多动手写写sql脚本，发现由于示例简单，官网没有提供示例脚本的下载，自己动手丰衣足食。
其实也是从网上找的别人写的for mysql 的修改而成。
既然改好了，留下备用，也许以后别人也会用得到。

CRATE DATABASE TestCookBookDB
USE TestCookBookDB
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 )