IS NULL, IS NOT NULL, NVL(), NVL2(), COALESCE(), Order By,Group by (), Having()
Is null, is not null
Ø
Select
* from emp where comm is null;
Ø
Select
* from emp where comm is not null;
NVL function
It is used to convert null values
to given values
Ø Select sal, comm, nvl (comm,0)
comm1, sal+nvl (comm,0) net_amount from emp;
NVL2 function nvl(exp1, exp2, exp3) if exp1 is null it
written exp3 , otherwise it written exp2.
Ø Select nvl2(comm,100,200) from emp;
->Select NULLIF (100,200) from dual; it written result as 100
->Select NULLIF (100,100) from dual; it written result as NULL
COALESCE(exp1, exp2, exp3………)
->It written 1st non null value in the result
Ø Select COALESCE (comm, empno, sal)
from emp;
Order by
->It can be last of sql statements
Ø Select * from emp order by sal;
Ø Select * from emp order by sal desc; for descending order
Group by
->It divided into groups , by using this all group function will work
– max(), min(), sum(), avg(), stddev(), count()…..
Ø Select deptno, sum(sal), min(sal),
max(sal) from emp group by deptno;
->In the above grouping will done by deptno wise so we must use
deptno in select statement, after group by function also.
Having
->It is associated with group by clause
Ø Select deptno, sum(sal), min(sal),
max(sal) from emp group by deptno having max(sal)>=2500;
->1st groups formed and group function calculated and next
having clause executed
->having must use one group function in select statement
Please watch and subscribe my Channel
Comments
Post a Comment