Functions available in SQL

There are two types of SQL functions:

• Single row functions: This type of functions operate in single row only and result one result per row. Some useful single row functions are: Date, Character, Conversion, Number. It is usually used with SELECT statement,  WHERE and ORDER BY clause
• Multiple row fictions: Also known as group function, this type of functions manipulate group of rows to give one result per group of rows.

Character Functions: 

Character functions accept character data as an input and can return both character and numeric values. Character functions can be divided into case-conversion and character manipulation types.

LOWER, UPPER, INITCAP are the three case conversion functions. 

Some Character -manipulations functions are : CONCAT, SUBSTR, INSTR, LENGTH, LPAD, TRIM

Number functions:
Number functions accept numeric input and return numeric input.
ROUND: rounds to the nearest unit of 100.
TRUNC: truncates the column, or value to n decimal places.
MOD: finds the reminder of the first argument divided by second argument. It is frequently used to determine whether a value is odd or even.

Date related functions:
Oracle's default date format is : DD-MON-RR. RR date is similar to YY but it also specify century. RR determines the default value for century in INSERT.
SYSDATE function: returns current database server date and time. If your database in London and you are in California, it will display London current time. CURRENT_DATE functions returns date, time in the session time zone. So use it for California time.
Some other date manipulation functions are: MONTHS_BETWEEN, ADD_MONTHS, NEST_DAY, ROUND, TRUNC.
Date function can be combined with Arithmetic function: date+number, Date-number, date-date, Date +number/24.

Conversion Functions:
Nesting Functions: