select add_months(sysdate,12) "Next Year" from dual;
select months_between('2019/12/6', sysdate) "months" from dual;
select last_day(sysdate) from dual;
select round(sysdate,'MONTH') from dual;
select round(sysdate,'YEAR') from dual;
select trunc(sysdate,'MONTH') from dual;
select trunc(sysdate,'YEAR') from dual;
select next_day(sysdate,'星期五') nxt_day from dual;
select extract(month from sysdate) "month" from dual;
select extract(year from sysdate) from dual;
select initcap('hello') from dual;
select lower('HOW ARE YOU') from dual;
select upper('hello world') from dual;
select ename,job,sal from emp where upper(job)=upper('clerk');
select ltrim('xyzadams','xyz')from dual;
select rtrim('xyzadams','ams') from dual;
select translate('JACK and JUE', 'J','BL') "CHANGE" from dual;
select replace('JACK and JUE','J','BL') "CHANGE" from dual;
select instr('abcdeadab','a',2,2) from dual;
select instr('abcdeadab','a') from dual;
select substr('abcdefgh',2,4) from dual;
select concat('hello',' world') from dual;
select chr(65) from dual;
select ascii('a') from dual;
select lpad('abcde',10,'w')from dual;
select lpad('abcde',10,'wt') from dual;
select rpad('abcde',10,'w') from dual;
select trim(0 from 7600) from dual;
select trim('中' from '中秋八月中') as 诗 from dual;
select trim(leading '半' from '半夜二更半') as 诗 from dual;
select length('how are you') from dual;
select empno,ename,job,sal,
decode(job,'CLERK', sal*1.5,
'SALESMAN', sal*2.0,
'MANAGER', sal*3,
sal ) as "new salary"
from emp;
select abs(-12) from dual;
select ceil(44.778)from dual;
select cos(180) from dual;
select cosh(0)from dual;
select floor(100.2) from dual;
select power(4,2) from dual;
select mod(10,3) from dual;
select round(100.347,2) from dual;
select trunc(100.347,2) from dual;
select trunc(100.347,-2) from dual;
select sqrt(4) from dual;
select sign(-10) from dual;
select sign(5) from dual;
select sign(0) from dual;
select sysdate from dual;
select to_char(sysdate, ' YYYY "年" fmMM "月" fmDD "日" HH24:MI:SS ') FROM DUAL;
select ename, to_char(sal,'C99999')as SALARY from emp;
select to_date('2009-03-11','yyyy-mm-dd') from dual;
select to_number('100') from dual;
select sqrt(to_number('100'))from dual;
select empno,ename,sal,comm, sal+comm "new salary" from emp;
select ename, sal,comm, NVL (comm,0)+sal "new salary" from emp;
select ename, sal,comm, NVL2(comm,sal+comm,sal) "new salary" from emp;
select avg(sal) "avg salary" from emp;
select min(sal) "最低工资" from emp;
select max(sal) "最高工资" from emp;
select sum(sal) "工资总和" from emp;
select count(*)as 员工人数 from emp;
select count(distinct deptno) as 部门数 from emp;
select count(deptno) as 部门数 from emp;
select ename,job,deptno,sal, row_number() over (order by sal desc) as sal_rank;
select deptno,ename,sal,comm, rank() over(partition by deptno order by sal desc,comm) rank from emp;
select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) as denrank from emp e, dept d where e.deptno=d.deptno;