SQL Functions
Char Functions:
Few
basic functions
1.
Concat (empanme, address): only 2 parameters
a.
Select concat(empname, address) from emp;
2.
UPPER(empname) : 1 parameter
a.
Select UPPER(empname) from emp;
3.
LOWER(empname): 1 parameter
a.
Select LOWER(empname) from emp;
4.
initcap(empname) : First letter of each word
capital
a.
select initcap(empname) from emp;
b.
Result: “Test Result” for value “test result”
5.
REPLACE(empname, str1, str2): str1 in empname is
replaced by str2
NUMBER Functions:
1.
ROUND(num): float to integer
2.
ROUND(num, +/- n): nth digit after/before
decimal
a.
Select round(121.57, -2) from sysibm.sysdummy1;
b.
Result: 100 (if n = -1 –> 120, n=1 ->
121.60)
3.
CEIL(num): add 1 if value has any decimal value
4.
FLOOR(num): cuts 0ff decimal part FLOOR(2.1) =
2, FLOOR(-2.1) = 3
5.
SQRT(num): square root of number
6.
Sin, cos, tan etc – Takes 1 parameter
NULL value:
·
Always use is
[not ] null ( [!]= null is not
correct)
·
Any arithmetic operation with null returns null.
List functions:
Greatest (x1 .. x255) :
Select greatest (salary, 10000) from emp; returns salary if
>10000 else return 10000
Least(x1 .. X255)
Select least(salary, 5000) from emp; returns salary if
<5000 5000="" else="" o:p="" return="">
GROUP Functions/
Aggregate Functions:
1.
Sum(column)
2.
Avg(column)
3.
Min(column)
4.
max (column)
5.
count(sal)
6.
count(*)
7.
stddev (column)
8.
variance (column)
Rules for group functions:
·
cannot use in where clause
o
Select * from emp where sal> avg(sal) (Not
Valid)
o
Solution: Select * from emp where sal>
(Select avg(sal) from emp );
·
Cannot specify column in select having group
function unless column is specified in group by clause
o
Select dept, sum(salary) from emp; (Not valid)
o
Select dept, sum(salary from emp group by dept;
Valid
·
Whichever Column is in group by Clause need not
be present in select statement
o
Select sum(salary) from emp group by dept;
·
where clause can come before group by
·
sequence of columns in group by affects speed of
processing
·
Specify condition on group by output use having
o
Select dept, sum(salary) from emp group by dept
having sum(salary) > 1000;
·
For sorted o/p use order by after group by
·
Group function can be used in having clause
without using group by statement
Sequence
of statements in query
·
Select
·
Where
·
Group by
·
Having
·
Order by
