SQL Miscellaneous Functions

TO_DATE converts char datatype to a value of DATE datatype.

TO_CHAR converts to database character set.

DECODE compares expr to each search value one by one.If expr is equal to a search, then Oracle Database returns the corresponding result.If no match is found, then Oracle returns default.If default is omitted, then Oracle returns null.
EX:Select decode(comm,null,sal,comm) from emp;
In the above example,it compares the comm column of emp table and if its null,it returns sal else returns comm.

GREATEST returns the greatest of the list of one or more expressions.Oracle Database uses the first expr to determine the return type.If the first expr is not numeric, then each expr after the first is implicitly converted to the datatype of the first expr before the comparison.If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype before the comparison, and returns that datatype.

LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.

NVL allows to replace null with a string in the results of a query.If expr1 is null,then NVL returns expr2.If expr1 is not null,then NVL returns expr1.
EX:Select nvl(comm,sal) from emp;
In the above example,it returns sal for all those whose comm is null.If comm is not null,NVL returns comm.

NVL2 allows to determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null,then NVL2 returns expr2. If expr1 is null,then NVL2 returns expr3.
EX:Select nvl(comm,comm,1000) from emp;
In the above example,if the comm is not null,NVL2 returns comm.If comm is null,NVL2 returns 1000.

USER returns the name of the session user with the VARCHAR2 datatype.
EX:select user,uid from dual;

ORDER BY clause is used to sort rows returned by a query.By default,Order by clause orders in ascending order.If it has to sort in descencing order,DESC command has to be used.
EX:select ename,sal from emp order by sal desc;

EX:select ename,sal from emp order by sal;