DB2 In Built Functions - Column function and Scalar Function

Column functions
1.Column functions can be executed in SELECT Statement
2. Each Column function returns only one value for the set of rows
3.If we apply a column function to one column in a select Statement,we must apply column functions to all the columns specified in the same select statement, unless the GROUP BY clause is used.
4. Result of the column function will have the same data type like the column on which it was applied.
5. Column function will never return a SQLCODE +100. It returns NULL value if the where clause does not fetches any data. 
But COUNT and COUNT_BIG function returns value, not NULL value. 
So lets look into the below query which theoretically is doing the same thing, but results will be different.
Thus AVG(SALARY)   and   SUM(SALARY)/ COUNT(*)  will be different.
The reason being, count function takes all the rows into account, whereas SUM function ignores the rows that has null values.

The COUNT function and COUNT_BIG function: The COUNT_BIG function is same as COUNT function except that it returns a decimal value.
Ex: select COUNT(*) from EMP_TBL   or Select COUNT_BIG(*) from EMP_TBL

The MAX and MIN functions: MAX will give the largest value in the expression and MIN will give the smallest one.The Result is of the same data type on which it is applied.
It can not be applied on data types like CLOB, BLOB, DBCLOB

STDDEV function returns the standard deviation of  a set of number
ex:  select stddev(salary)  from emp where dept='cse'

SUM function gives the total of values specified in the expression.
ex:  select SUM(SALARY+BONUS) FROM EMP  where month='Oct'

Scalar  functions :
Scalar functions operate on single value as compared to column function.
Below are the Queries along with their output for few scalar functions.

SELECT LEFT('THIS IS VALUE',2) FROM SYSIBM.SYSDUMMY1;
TH
                                                           
SELECT (RAND()*100) FROM SYSIBM.SYSDUMMY1;
+0.3388811137242620E+02

SELECT LOCATE('S','INDIA IS GREAT') FROM SYSIBM.SYSDUMMY1;
 8  
Returns the position of first occurrence of 'S' in the string

SELECT NEXT_DAY(CURRENT DATE,'FRIDAY') FROM SYSIBM.SYSDUMMY1;
2013-11-15-00.00.00.000000

SELECT REPLACE('BATATA','TA','NA')  FROM SYSIBM.SYSDUMMY1;
BANANA

SELECT DATE('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-15

SELECT WEEK('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
52
SELECT YEAR('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013

SELECT DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
15 

SELECT LAST_DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-30


SELECT MONTH('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
11

SELECT TIME('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
00.00.00

SELECT HOUR('2013-11-15-23.22.21.056788') FROM SYSIBM.SYSDUMMY1;
23

SELECT SECOND('2013-11-11-00.00.13.0000') FROM  SYSIBM.SYSDUMMY1
13

SELECT MICROSECOND('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
889924 

SELECT JULIAN_DAY('2013-11-15') FROM SYSIBM.SYSDUMMY1;
2456612

SELECT DAYOFWEEK('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
6 
Represents the day of the week.Like 1 for Sunday,2 Monday

SELECT DAYOFYEAR('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
319 

SELECT GRAPHIC('319') FROM SYSIBM.SYSDUMMY1;
.â3â1â9.

SELECT LCASE('JIM') FROM SYSIBM.SYSDUMMY1;
jim

SELECT LTRIM('     JIM') FROM SYSIBM.SYSDUMMY1;
JIM
Trims the space from the string.

SELECT HEX('RI5') FROM SYSIBM.SYSDUMMY1;
D9C9F5

SELECT INT(123.90) FROM SYSIBM.SYSDUMMY1;
123 

SELECT CHAR(CURRENT DATE,USA) FROM SYSIBM.SYSDUMMY1
11/11/2013
Displays  the current date in the USA date format 

SELECT CONCAT('CON','CAT') FROM SYSIBM.SYSDUMMY1
CONCAT

SELECT INSERT('JIM  PRAT', 5, 0 ,'NORM') FROM SYSIBM.SYSDUMMY1 
JIM NORM PRAT 
Accepts 4 arguments. Returns the string which contains the 4th argument inserted into the 1st argument at the position specified by the 2rd argument. 3rd arg specifies the number of bytes to delete

SELECT QUARTER('2013-11-11-00.00.00.0000') FROM  SYSIBM.SYSDUMMY1
4
Vaue of 1=1s quarter,2=2nd quarter,3=3rd quarter,4=4th quarter