-- decode函数
select customer#,state
,decode(state,'CA',.08,'FL',.07,0) sales_tr
from customers;
-- 使用DECODE比对字符串
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
-- 使用DECODE测定数值范围
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;

-- 例子1：下面哪个SQL显示结果一样吗？
select to_char(orderdate,'MM/DD') from orders;
select to_char(orderdate,'fmMM/DD') from orders;
-- 例子2：为什么nvl(sysdate,'aa')会返回一个错误？

-- 不一样，fm在to_char函数中的作用是禁用fill mode
-- 使用nvl时类型需要一致或者可转换

-- 有关null的函数
NVL 'Converts a null value to an actual value'
NVL2 'If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1can have any data type.'
NULLIF 'Compares two expressions and returns null if they are equal, or the first expression if they are not equal'
COALESCE 'Returns the first non-null expression in the expression list'
-- 在使用COALESCE时，也要注意list中的类型要一致
NULLIF 类似 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

-- case when函数
select case when 1=2 then null else 'abc' end from dual;
select case 'abc' when 'abc' then 'ok' else 'err' end from dual;

-- 入职日期长短的图示
select ename,lpad('*',round(months_between(sysdate,hiredate)/6),'*') from emp;

-- 一个测试题,要求写一个SQL，结果如下
ENAME sql
---------- ---------------
SMITH $$$$$$$$$$$$800
ALLEN $$$$$$$$$$$1600
WARD $$$$$$$$$$$1250
JONES $$$$$$$$$$$2975
MARTIN $$$$$$$$$$$1250
BLAKE $$$$$$$$$$$2850
CLARK $$$$$$$$$$$2450
SCOTT $$$$$$$$$$$3000
KING $$$$$$$$$$$5000
TURNER $$$$$$$$$$$1500
ADAMS $$$$$$$$$$$1100
JAMES $$$$$$$$$$$$950
FORD $$$$$$$$$$$3000
MILLER $$$$$$$$$$$1300

-- 参考
select ename,lpad(trim(to_char(sal,'$99999')),15,'$') from emp;

-- 一个测试题
-- 原有数据
select ename,hiredate from emp;
ENAME HIREDATE
---------- ---------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
MARTIN 28-SEP-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
SCOTT 09-DEC-82
KING 17-NOV-81
TURNER 08-SEP-81
ADAMS 12-JAN-83
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82

-- 要求列出入职日期6个月后的第一个monday 结果如下，如何写SQL？
ENAME HIREDATE mydate
---------- --------- ----------------------------------
SMITH 17-DEC-80 Seventeenth of December , 1980
ALLEN 20-FEB-81 Twentieth of February , 1981
WARD 22-FEB-81 Twenty-Second of February , 1981
JONES 02-APR-81 Second of April , 1981
MARTIN 28-SEP-81 Twenty-Eighth of September , 1981
BLAKE 01-MAY-81 First of May , 1981
CLARK 09-JUN-81 Ninth of June , 1981
SCOTT 09-DEC-82 Ninth of December , 1982
KING 17-NOV-81 Seventeenth of November , 1981
TURNER 08-SEP-81 Eighth of September , 1981
ADAMS 12-JAN-83 Twelfth of January , 1983
JAMES 03-DEC-81 Third of December , 1981
FORD 03-DEC-81 Third of December , 1981
MILLER 23-JAN-82 Twenty-Third of January , 1982

-- 参考答案
select ename,hiredate,
to_char(
next_day(add_months(hiredate,6),'monday'),
'fmDdspth "of" Month "," yyyy'
) mydate from emp

-- 一个测试题
-- 源数据
SQL> select ename,comm from emp;

ENAME COMM
---------- ----------
SMITH
ALLEN 300
WARD 500
JONES
MARTIN 1400
BLAKE
CLARK
SCOTT
KING
TURNER 0
ADAMS
JAMES
FORD
MILLER

-- 写SQL，结果如下
ENAME DECODE(TO_CHAR
---------- --------------
SMITH No Commission
ALLEN 300
WARD 500
JONES No Commission
MARTIN 1400
BLAKE No Commission
CLARK No Commission
SCOTT No Commission
KING No Commission
TURNER No Commission
ADAMS No Commission
JAMES No Commission
FORD No Commission
MILLER No Commission
-- 参考答案
SQL> select ename,decode(to_char(nvl(comm,0)),0,'No Commission',to_char(comm)) from emp;

/** 又一个考题
12. Create a query that displays the employees’ last names and indicates the amounts of their annual
salaries with asterisks. Each asterisk signifies a thousand doll ars. Sort the data in descending order of
salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.
SMITH********
ALLEN****************
WARD*************
JONES******************************
MARTIN*************
BLAKE*****************************
CLARK*************************
SCOTT******************************
KING**************************************************
TURNER***************
ADAMS***********
JAMES**********
FORD******************************
MILLER*************
*/

-- 我的参考答案
select ename||lpad('*',round(sal/100),'*') from emp;

/*
13. Using the DECODE function, write a query that displays the grade of all employees based on the value
of the column JOB_ID, as per the following data:
Job Grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
None of the above 0
JOB D
--------- -
CLERK B
SALESMAN 0
SALESMAN 0
MANAGER C
SALESMAN 0
MANAGER C
MANAGER C
ANALYST A
PRESIDENT D
SALESMAN 0
CLERK B
CLERK B
ANALYST A
CLERK B

14. Rewrite the statement in the preceding question using the CASE syntax.
*/

-- 我的参考答案
select job,decode(job,'ANALYST','A','CLERK','B','MANAGER','C','PRESIDENT','D','0') FROM EMP;

SELECT JOB,
case job
when 'ANALYST' THEN 'A'
WHEN 'CLERK' THEN 'B'
WHEN 'MANAGER' THEN 'C'
ELSE '0' END
FROM EMP