【1.nvl/nvl2】
--select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) from employees
//如果commission_pct存在，则为其自身，否则赋值为0；
--select employee_id,last_name,commission_pct,nvl2(commission_pct,commission_pct+0.15,0.01) from employees
//如果commission_pct存在，则返回值为commission_pct+0.15，否则为0.01
【2. case…when…then…when…then…else…end】
select employee_id,last_name,department_id,
case department_id
when 10
then salary*1.1
when 20
then salary*1.2
else salary*1.3
end as "new_sal" //赋别名
from employees where department_id in (10,20,30)
【3.decode】
select employee_id,last_name,department_id,
decode(department_id,10,salary*1.1,20,salary*1.2,salary)
new_sal
from employees where department_id in(10,20,30)
//根据department_id进行判断，如果为10，则其salary*1.1；如果为20，则其salary*1.2；其他salary不变，并赋别名--new_sal
【4.to_char】
select to_char(sysdate,'yyyy"年"mm"月"dd"日" HH:mi:ss') from dual
select to_char(1234567.89,'999,999,999.99') from dual
【5.to_number】
select to_number('1,234,567.89','999,999,999.99')+100 from dual
//注意前后两个数的取值范围，后者一定要比前者大【6.|| 拼接符】
select last_name || ' earns' ||to_char(salary,'$99999')||' monthlym,but wants to earn'||
to_char(3*salary,'$99999') "Dream Salary" from employees
//控制台将会输出一句话
【7.group by…having】
//按照部门ID进行分组，并添加过滤条件，且使用了子查询
select department_id,min(salary) from employees
group by department_id
having min(salary) >
(select min(salary) from employees where department_id ='50' )
