LightDB scott下的测试表emp

#测试数据

DROP TABLE EMP;
CREATE TABLE EMP
(   EMPNO    NUMBER(4, 0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4, 0),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2, 0),
    DNAME    VARCHAR2(100),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH',  'CLERK',    7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00,  null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN',  'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD',   'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES',  'MANAGER',  7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7698, 'BLAKE',  'MANAGER',  7839, to_date('1981-05-01','yyyy-mm-dd'), 2850.00, null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7782, 'CLARK',  'MANAGER',  7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7788, 'SCOTT',  'ANALYST',  7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7839, 'KING',   'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7876, 'ADAMS',  'CLERK',    7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7900, 'JAMES',  'CLERK',    7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00,  null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7902, 'FORD',   'ANALYST',  7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7934, 'MILLER', 'CLERK',    7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null,    10, null);
DROP TABLE DEPT;
CREATE TABLE DEPT
(   DEPTNO NUMBER(2, 0),
    DNAME  VARCHAR2(14),
    LOC    VARCHAR2(13),
    CONSTRAINT PK_DEPT 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');
commit;

-- merge into delete 测试

merge into emp e using (select * from dept ) d on (d.deptno = e.deptno)
when matched then update set e.sal = 10000, e.dname = d.DNAME
delete where d.DEPTNO = e.DEPTNO;


-- decode函数
SELECT 
    empno,
    ename,
    DECODE(
        ROUND(sal / 1000),
        3, '高薪',
        2, '中薪',
        1, '低薪',
        '其他'
    ) AS salary_description
FROM emp;

 empno | ename  |   sal   | salary_description 
-------+--------+---------+--------------------
  7369 | SMITH  |  800.00 | 低薪
  7499 | ALLEN  | 1600.00 | 中薪
  7521 | WARD   | 1250.00 | 低薪
  7566 | JONES  | 2975.00 | 高薪
  7654 | MARTIN | 1250.00 | 低薪
  7698 | BLAKE  | 2850.00 | 高薪
  7782 | CLARK  | 2450.00 | 中薪
  7788 | SCOTT  | 3000.00 | 高薪
  7839 | KING   | 5000.00 | 其他
  7844 | TURNER | 1500.00 | 中薪
  7876 | ADAMS  | 1100.00 | 低薪
  7900 | JAMES  |  950.00 | 低薪
  7902 | FORD   | 3000.00 | 高薪
  7934 | MILLER | 1300.00 | 低薪
(14 rows)