ElavarasiGophannan

 Profile Answers by ElavarasiGophannan 

• Feb 11th, 2009

 

Aggregate Functions:
AVG() - Returns the average value 
COUNT() - Returns the number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum

Scalar Functions
UCASE() - Converts a field to upper case

LCASE() - Converts a field to lower case
MID() - Extract characters from a text field
LEN() - Returns the length of a text field
ROUND() - Rounds a numeric field to the number of decimals specified
NOW() - Returns the current system date and time
FORMAT() - Formats how a field is to be displayed

aghiad

• Sep 10th, 2011

 

The General function are use in SQL
1-NVL (exp1,exp2)
2-NVL2 (exp1,exp2,exp3)
3-NULLIF (exp1,exp2)
4-COALESCE (exp1,exp2,exp3,......,expn)
For example:

Code
SELECT last_name, salary, commission_pct, 


salary*NVL (commission_pct,0.10) Comm 


FROM employees

As you see in example above, we use NVL (dont forget that NVL takes in 2 arguments) so we put in it commission_pct,0.10
NOTE : Comm is the alias of the code NVL(commission,0.10)
Example 2 :

Code
SELECT last_name, salary, commission_pct, 


nvl2(commission_pct,salary*commission_pct,salary)


FROM employees

Example 3 :

Code
SELECT first_name,length(first_name)"expr1",


last_name, LENGTH(last_name) "expr2",


NULLIF (LENGTH(first_name),LENGTH(last_name))result


FROM employees

Example 4 :

Code
SELCT first_name,manager_id


ser_id,commission_pct,coalesce(manager_id,commission_pct,-1)


FROM employees

I hope you got the point ^_^