-- 创建emp, dept, salgrade测试表
drop table if exists emp, dept, salgrade
;

create table dept
(
    deptno    int,
    dname     varchar(14),
    loc       varchar(13),
    primary key(deptno)
)
;

create table emp
(
    empno    int,
    ename    varchar(10),
    job      varchar(9),
    mgr      int,
    hiredate date,
    sal      numeric(7,2),
    comm     numeric(7,2),
    deptno   int,
    primary key(empno),
    constraint fk_deptno foreign key(deptno) references dept(deptno)
);
create table salgrade
(
   grade      int not null,
   losal      int not null,
   hisal      int not null
)
;

insert into dept values 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON')
;

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

insert into salgrade values 
(1,  700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999)
;