准备工作

背景环境：

• 以《[SQL.Cookbook(中文版)].(美)莫利纳罗.扫描版》为教材

• 所有操作在SQL Server Management Studio中完成

（一）创建数据库TestCookBookDB

CREATE DATABASE TestCookBookDB

（二）建立员工表——EMP

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

字段说明：

EMP(员工表)字段	说明
EMPNO	工号
ENAME	姓名
JOB	工种
MGR	上级编号
HIREDATE	雇佣日期
SAL	工资
COMM	奖金
DEPTNO	部门编号

（三）建立部门表——DEPT

create table DEPT
(
    DEPTNO    integer,
    DNAME     VARCHAR(14),
    LOC       VARCHAR(13)
);

字段说明：

DEPT(部门表)字段	说明
depno	部门编号
dname	部门名称
loc	办公地点

（四）EMP表和DEPT表插入数据

给员工表EMP和部门表DEPT插入数据

begin transaction

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,   '12/17/1980', 800,  NULL, 20)
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,   '2/20/1981', 1600,  300, 30)
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,   '2/22/1981', 1250,  500, 30)
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,   '4/2/1981',  2975, NULL, 20)
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,   '9/28/1981', 1250, 1400, 30)
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,   '5/1/1981',  2850, NULL, 30)
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,   '6/9/1981',  2450, NULL, 10)
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,   '12/9/1982', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,   '11/17/1981', 5000, NULL, 10)
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,   '9/8/1981',  1500,    0, 30)
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,   '1/12/1983',1100, NULL, 20)
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,   '12/3/1981', 950, NULL, 30)
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,   '12/3/1981', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,   '1/23/1982', 1300, NULL, 10)
        


INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS')
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO')
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')

commit transaction;

查询数据

select * from dept;
select * from emp;

结果：

DEPTNO	DNAME	LOC
1	10	ACCOUNTING	NEW YORK
2	20	RESEARCH	DALLAS
3	30	SALES	CHICAGO
4	40	OPERATIONS	BOSTON

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	SMITH	CLERK	7902	1980-12-17 00:00:00.000	800.00	NULL	20
2	7499	ALLEN	SALESMAN	7698	1981-02-20 00:00:00.000	1600.00	300.00	30
3	7521	WARD	SALESMAN	7698	1981-02-22 00:00:00.000	1250.00	500.00	30
4	7566	JONES	MANAGER	7839	1981-04-02 00:00:00.000	2975.00	NULL	20
5	7654	MARTIN	SALESMAN	7698	1981-09-28 00:00:00.000	1250.00	1400.00	30
6	7698	BLAKE	MANAGER	7839	1981-06-01 00:00:00.000	2850.00	NULL	30
7	7782	CLARK	MANAGER	7839	1981-06-09 00:00:00.000	3260.95	NULL	10
8	7788	SCOTT	ANALYST	7566	1982-12-09 00:00:00.000	3000	NULL	20
9	7839	KING	PRESIDENT	NULL	1981-11-17 00:00:00.000	6655.00	NULL	10
10	7844	TURNER	SALESMAN	7698	1981-09-08 00:00:00.000	1500.00	0.00	30
11	7876	ADAMS	CLERK	7788	1983-01-12 00:00:00.000	1100	NULL	20
12	7900	JAMES	CLERK	7698	1981-12-03 00:00:00.000	950.00	NULL	30
13	7902	FORD	ANALYST	7566	1981-12-03 00:00:00.000	3000.00	NULL	20
14	7934	MILLER	CLERK	7782	1982-01-23 00:00:00.000	1300.00	NULL	10

（五）建立透视表T1，并插入数据

create table T1 (ID integer)

insert into T1 values(1);

查询数据

select ID from t1;

结果：

	ID
1	1

（六）建立透视表T10，并插入数据

CREATE TABLE T10 (ID INTEGER)

INSERT INTO T10 VALUES (1)
INSERT INTO T10 VALUES (2)
INSERT INTO T10 VALUES (3)
INSERT INTO T10 VALUES (4)
INSERT INTO T10 VALUES (5)
INSERT INTO T10 VALUES (6)
INSERT INTO T10 VALUES (7)
INSERT INTO T10 VALUES (8)
INSERT INTO T10 VALUES (9)
INSERT INTO T10 VALUES (10)

查询数据

select id from t10

结果：

	ID
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10

（七）创建 emp_bonus表

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 )

查询数据

select * from emp_bonus

结果：

八、创建NEW_SAL表并插入数据

create table NEW_SAL 
(
	DEPTNO int;
	SAL int;
)

insert into NEW_SAL (DEPTNO,SAL)values(10,4000);