# 准备工作
#### 背景环境：
* 以《\[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);
```