Miscellaneous Functions
=======================
=> All miscellaneous functions can work as multi-row functions.

1) nvl():
=========
=> this function can be used to convert the null value in the table of particular column to non-null value.
Syntax:
	nvl(a, b);
Here:
	a ==> column-name
	b ==> replace value

Scenario:
--------
create the table with the name of "bank system"
which includes:
	accountNumber number
	customer name varchar
	balance decimal

create table bankSystem
(
	accountNumber number,
	customerName varchar(100),
	balance decimal(10,2)
);

2) Insert the data into the above table including the null values
------------------------------------------------------------------
insert into bankSystem values(&accountNumber, &customerName, &balance);

3) Replace the null values of balance in the bankSystem table:
---------------------------------------------------------------
select accountNumber, customerName, nvl(balance,1000) from bankSystem;

4) Replace null with non-null value of accountNumber and balance:
---------------------------------------------------------------
select nvl(accountNumber, 12121212), customerName, nvl(balance,1000) from bankSystem;

2) nvl2():
==========
=> which is same as nvl().
=> nvl2() can convert a null value into non-null value based on the codition.
Syntax:
	nvl2(a, b, c);
here:
	a ==> column name
	b and c ==> replace values
if 'a' is null
	then:
		value of 'c' can be replaced
otherwise:
	value of 'b' can be replaced.

Ex:
select accountnumber, customerName, nvl2(balance,balance,1000) from bankSystem;

3) nullif():
============
=> when we want to convert normal value (not-null value) into null value, we can use "nullif()".
Syntax:
	nullif(a, b);
here:
	a and b ==> values
if a and b are same (equal), then: nullif() can return "null"
if a  and b are different, then: nullif() can return value of 'a'

Ex:
select nullif(10,10) from dual;
select nullif(10,20) from dual;
select nullif(20,10) from dual;

4) coalesce():
==============
=> which can check all the values of specified column
and returns the first occurrence of the not-null value.
Syntax:
	coalesce(column-name)

Ex:
coalesce(null, null, null, 1000, 2000, null, 3000, null) ==> 1000

User Defined Functions
=====================
Syntax:
	create or replace function functiona-name
	(par1/arg1 datatype, par2/arg2 datatype,...)
	return return-type/datatype
	is
	begin
		function-logic
		return value;
	end;
	/

Ex: 1)
Create a function to calculate the four power to the given number.

create or replace function my_fun1(p number)
return number
is
begin
	return p * 4;
end;
/

// calling of the function
select my_fun1(123) as fourMultiple from dual;

2) Create a function for finding the square of a given number.
create function square_number(a number)
return number
is
begin
	return a * a;
end;
/

select sqaure_number(10) from dual;


Assignment:
-----------
count(salary)
count(*)

1) Write a sql query to get the number of salaries of the employee table by excluding the duplicates.
Syntax:
	count(distinct salary);

2) Write a sql query to create the user-defined function to find the average of 5 numbers.