SQL Functions

SQL Functions : These functions can be used to perform calculation,comparison,modification of data

The two types of functions in sql are
1.Single-row functions: Operates on single row and returns one result for row
2.Multiple-row functions :operates on group of rows

The functions are
1.character function :accepts character data as input and return both character and numeric value
a.case manipulation
b.character manipulation

2.Number function: accepts numeric input and return numeric value

3.Date function :stores date in internal numeric format default display as DD-MON-YY.

4.Conversion function: convert the data from char to varchar, varchar to char, number to date, date to varchar.


5.General function :These function work with any data type
NVL(expr1, expr2) : Converts nall value to actual value.
NVL2(expr1, expr2, expr3) : If expr1 is null return expr2,not null return expr3.
NULLIF(expr1, expr2) : Compare two expressions and return null if they are equal or the
first if they are not equal.
COALESCE(expr1, expr2,........ exprn):returns the first not null expr.

6.Aggregate function : SUM,AVG,MAX,MIN,COUNT(*),COUNT(column name). these all are the aggregate functions.

GROUP BY Clause : group by clause is used to categorize the query result
HAVING CLAUSE : Used for restricting groups.