Oracle Database 11g Fundamental SQL (Chapter 4)

Data conversion
Character ---> number menggunakan to_number
Number ---> character menggunakan to_char

Character ---> date menggunakan to_date
Date ---> character menggunakan to_chat

Rumusnya:
To_char(number,'format_model')
To_char(date, 'format_model')

Merubah format tanggal sesuai dengan yang kita inginkan

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';

SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees;

Merubah format number sesuai dengan yang kita inginkan

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';

Merubah format tanggal dalam kalkulasi matematika

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

Penggunaan nesting function

SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employees
WHERE department_id = 60;

Penggunaan NVL untuk menggantikan nilai NULL dengan suatu karakter / number

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,
(salary*12) + (salary*12*commission_pct) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);

Penggunaan NULLIF, untuk membandingkah dua nilai.
Bila sama akan null dan bisa tidak sama akan di tampilakan
nilai sesuai dgn ekspresi

SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;

Penggunaan coalesce, untuk membandingkan dua nilai

SELECT last_name, employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),
'No commission and no manager')
FROM employees;

SELECT last_name, salary, commission_pct,
COALESCE((salary+(commission_pct*salary)), salary+2000, salary) "New
Salary"
FROM employees;

Penggunaan kondisi

SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

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;