-- 创建emp,dept,salgrade测试表
-- 此脚本可在SQL Server运行
if object_id('emp') is not null drop table emp;
if object_id('dept') is not null drop table dept;
if object_id('salgrade') is not null drop table salgrade;
go
create table dept
(
    deptno    numeric(2),
    dname     varchar(14),
    loc       varchar(13),
    primary key(deptno)
);
go
create table emp
(
    empno    numeric(4),
    ename    varchar(10),
    job      varchar(9),
    mgr      numeric(4),
    hiredate date,
    sal      numeric(7,2),
    comm     numeric(7,2),
    deptno   numeric(2),
    primary key(empno),
    constraint fk_deptno foreign key(deptno) references dept(deptno)
);
go
create table salgrade
(
   grade      int not null,
   losal      int not null,
   hisal      int not null
);
go
insert into dept values 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
go
insert into emp values 
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-02',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
go
insert into salgrade values 
(1,  700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
go