SQL Basics- Part 3

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