Thursday, June 22, 2006

common sql functions # 1

SQL> select nvl2(null,1,2) from dual;

NVL2(NULL,1,2)
---------------
2

SQL> select nvl2(1,null,3) from dual;

NVL2(1,NULL,3)
---------------


SQL> select nvl2(1,2,null) from dual;

NVL2(1,2,NULL)
---------------
2



SQL> select nullif(10,10) from dual;

NULLIF(10,10)
---------------


SQL> select nvl(nullif(10,10),0) from dual;

NVL(NULLIF(10,10),0)
--------------------
0

SQL> select nullif(1,7) from dual;

NULLIF(1,7)
---------------
1

SQL> select nullif(7,1) from dual;

NULLIF(7,1)
---------------
7



SQL> select coalesce(1,2,null) from dual;

COALESCE(1,2,NULL)
------------------
1

SQL> select coalesce(1,null,3) from dual;

COALESCE(1,NULL,3)
------------------
1

SQL> select coalesce(null,2,3) from dual;

COALESCE(NULL,2,3)
------------------
2



1 select empno,deptno,sal,
2 case JOB when 'ANALYST' then sal+sal*.11
3 when 'CLERK' then sal+sal*.4
4 else
5 sal
6 end
7 "sal_riv"
8 from
9* emp
QL> /

EMPNO DEPTNO SAL sal_riv
-------------- --------------- --------------- ---------------
7369 20 800 1120 *
7499 30 1600 1600
7521 30 1250 1250
7566 20 2975 2975
7654 30 1250 1250
7698 30 2850 2850
7782 10 2450 2450
7788 20 3000 3330 *
7839 10 5000 5000
7844 30 1500 1500
7876 20 1100 1540 *
7900 30 950 1330
7902 20 3000 3330 *
7934 10 1300 1820 *

14 rows selected.

Display each employee's empno,name,hiredate,sal review date
sal review date=first monday after 6 months of service.

select empno,deptno,to_char(hiredate,'dd-mon-yyyy day'),
to_char(next_day(add_months(hiredate,6),'Monday'),'dd-mon-yyyy day') from emp
/


display emp's sal as *, each * for 1000

select sal,rpad(' ',sal/1000+1,'#') as "#" from emp

upto practice # 3

No comments:

Post a Comment