FUNCTIONS CONTINUES..
GENERAL FUNCTIONS::
The General functions are mainly used for Process the "NULL" Values.
There are following four types of General functions are there::
1.NVL()
--It's having two arguments
2.NVL2()
--It's having three arguments
3.NULLIF()
--It's having two arguments
4.COALESCE()
--It's having "n" number of arguments.
1.NVL()::(Argument1,Argument2)
The NVL() function used for if the first argument having NULL value then it's return the second argument value.
Example::
SELECT NVL(Commission_PCT,'0') From employees;
O/P---> -
-
1.2
3.3
-
2.NVL2()::(Argument1,Argument2,Argument3)
The NVL2() function used for if the first argument having "NULL" value then it's return the third argument value.
Example::
SELECT NVL2(Commission_Pct,'0','1') From employees
O/P--O/P---> 1
1
2.2
3.3
1
3.NULLIF()::(Argument1,Argument2)
The NULLIF() function used for return,If the two argument having same values then its return "NULL",else it print
the "first" argument value.
Example::
SELECT NULLIF (1,,1)
,(2,2)
,(1,2)
,(2,7)
,(7,9)
FROM dual;
O/P-------->>> -
-
1
2
7
4.COALESCE()::(1,2,3,....n)
The COALESCE() function returns the first "non null Value" from the Passed arguments.
Examples:
SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,89,NULL,NULL,99,100,101) from dual;
O/P--89
II.MULTI ROW FUNCTIONS
======================
GROUP FUNCTIONS or AGGREGATE FUNCTIONS::
1.MIN()
2.MAX()
3.SUM()
4.AVG()
5.COUNT()
Example Table::
-----------------------------------------------
Name Dept_Id Salary
-----------------------------------------------
n1 10 25000
n2 20 45000
n3 20 17000
n4 30 13500
n5 30 73000
n6 30 72000
n7 40 1700
n8 40 2900
------------------------------------------------
1.MIN()::
The MIN() function is mainly used for display the Minimum of Value in the selected column.
Example::
SELECT MIN(Salary) FROM employees;
O/P--1700
2.MAX()::
The MAX() function is used for return the maximun of value in the selected column.
Example::
SELECT MAX(Salary) From employees;
O/P---73000
3.SUM()::
The SUM() function is used for returning the sum(Addition of selected column) selected column from the table.
Example::
SELECT SUM(1000 + 2000 + 3000) From dual;
O/P---6000
4.AVG()::
The AVG() function is used for display the value like (Add the Values and/divid the Number Of columns or Number of row)
Example::
SELECT ROUND(AVG(salary)) from employees;
O/P---6462
5.COUNT()::
The COUNT() function is used for Count the number in the selected rows.
Example::
SELECT COUNT(Salary) FROM employees;
O/P--107
GROUP BY & HAVING::
When ever use the Group function Must use the GROUP BY Clause for the non group function columns.
USING GROUP BY::
SELECT First_Name,COUNT(Salary) FROM employees;
GROUP BY First_Name;
O/P--Steven 2
Neena 1
USING HAVING::
*When ever we want to check the condition while using the Group function case we must use the "HAVING",do not use the
"WHERE" condition.
*We can use "HAVING" with out GROUP BY
Example::
SELECT First_Name,COUNT(Salary) FROM employees
GROUP BY First_Name
HAVING Count(Salary)>1;
O/P---Steven 2
Peter 3
John 3
USING ORDER BY While Use GROUP BY & HAVING::
If the "ORDER BY" Comes in the query ,It Should place after the "HAVING" statement.
Example::
SELECT First_Name,COUNT(Salary) FROM employees
GROUP BY First_Name
HAVING Count(Salary)>1
ORDER BY First_Name Asc;
O/P--- Alexander 2
David 3
James 2
DECODE & CASE::
*Decode is a Function
*CASE is a expression
*Decode produce the OUTPUT based on If the next ..next value satisfy the condition else it print the Final Value.
*In Case we can used Oracle Operators "LIKE,BETWEEN AND,=,<,>,<>"
*Compare to Decode , CASE is fast because it followes ANSII Standards.
*DECODE is an SQL Standard but CASE is an SQL and PL/SQL Standard.
Example Using DECODE::
SELECT DECODE('Infycle','Infycle','YES','NO')"EXAMPLE" FROM dual;
O/P---YES
SELECT DECODE('Infycle','infycle','YES','NO') "EXAMPLE" FROM dual;
O/P---NO
SELECT DECODE('Infycle','Infycle_Chennai','YES_1','Infycle','YES_2','NO') "EXAMPLE" FROM dual;
O/P---YES_2
SELECT DECODE('Infycle','Infycle_Chennai','YES_1','InFyCle','YES_2','NO MATCH') "EXAMPLE" FROM dual;
O/P---NO MATCH
Example Using CASE::
SELECT FIRST_NAME,DEPARTMENT_ID,
CASE DEPARTMENT_ID
WHEN 10
THEN 'ADMIN'
WHEN 20
THEN 'PURCHASE'
WHEN 30
THEN 'SALES'
WHEN 50
THEN 'HR'
ELSE 'OTHERS'
END
FROM employees WHERE DEPARTMENT_ID in (10,20,30,50);
