2006年01月09日

select ename,job,deptno from emp where job='CLERK';
select * from emp where deptno=10;
select * from dept;

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

select ename,sal,comm from emp where sal<2000;
select ename,sal,comm from emp where sal between 2000 and 3000;
select * from emp where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1982-01-01','yyyy-mm-dd');
select empno,ename,sal,mgr from emp where mgr in (7982,7566,7788);

select initcap('ab cd ef') from dual;
select concat('ab','cd') from dual;
select substr('abcdef',2,3) from dual;
select instr('abcdefg','de',1,1) from dual;
select lpad('abc',5,'*') from dual;
select ename,concat(ename,' is a ',job),length(ename),instr(ename,'A') from emp where substr(job,1,5)='SALES';

select round(45.926),trunc(45.926),mod(1600,300) from dual;
select round(45.926,2),round(45.926,0),round(45.926,-1) from dual;
select trunc(45.926,2),trunc(45.926,0),trunc(45.926,-1) from dual;

////////
select sysdate+10 from dual;
select sysdate-20 from dual;
select ename,sysdate-hiredate from emp;
select add_months(sysdate,6) from dual;
select next_day(hiredate,'星期五') from emp;
select next_day(hiredate,'Sunday') from emp;
select last_day(sysdate) from dual;

select round(sysdate+30,'month'),round(sysdate+30,'year') from dual;
select trunc(sysdate+30,'month'),trunc(sysdate+30,'year') from dual;
select round(sysdate,'month'),round(sysdate,'year') from dual;
select trunc(sysdate,'month'),trunc(sysdate,'year') from dual;

create table t (a varchar(10),b date,c number(2));
create index idx_a on t(a);

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'year-mm-dd hh24:mi:ss') from dual;

select nvl(comm,0) from emp;
select nvl(hiredate,sysdate) from emp;
select nvl(job,'no job') from emp;