网上收集摘录的sql cookbook数据库脚本

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 )

转载于:https://www.cnblogs.com/st-coordinate/p/3510545.html