SQL入門之3 函式2

-- 單行函式及幾個測試題,是否全面系統學習過ORACLE的函式,一試便知[@more@]

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