Functions in oracle:

-------------------------------------
> To Perform Task & Must Return Value.
> Oracle Supports Two Types Functions. Those Are

1) Pre-Define / Built in Functions (Use in Sql & Pl/Sql)

2) User Define Functions (Use in Pl/Sql)

1) Pre-Define Functions:
---------------------------------------------
> These Are Again Classified into Two Categories.

A) Single Row Functions (Scalar Functions)

B) Multiple Row Functions (Grouping Functions)

Single Row Functions:

--------------------------------------------
> These Functions Are Returns A Single Row (Or) A Single Value.

> Numeric Functions

> String Functions
> Date Functions
> Conversion Functions

How To Call a Function:

------------------------------
Syntax:
---------------
Select <Fname>(Values) From Dual;

What Is Dual:
------------------------
> Pre-Define Table In Oracle.
> Having Single Column & Single Row
> Is Called As Dummy Table In Oracle.
> Testing Functions (Pre-Define & User Define) Functionalities.

To View Strc.Of Dual Table:

----------------------------------------------------
Sql> Desc Dual;
To View Data Of Dual Table:
----------------------------------------------------
Sql> Select * From Dual;

Numeric Functions:
--------------------------------------
1) Abs():
> Converts (-Ve) Value Into (+Ve) Value.

Syntax:
---------------
Abs(Number)

Ex:
Sql> Select Abs(-12) From Dual; --------> 12
Sql> Select Ename,Sal,Comm,Abs(Comm-Sal) From Emp;

2) Ceil():
> Returns A Value Which Is Greater Than Or Equal To Given Value.

Syntax:
---------------
Ceil(Number)

Ex:
Sql> Select Ceil(9.0) From Dual;------9
Sql> Select Ceil(9.3) From Dual;-------10

3) Floor():

Syntax:
Floor(Number)

Ex:
Sql> Select Floor(9.0) From Dual;------9
Sql> Select Floor(9.8) From Dual;------9
 4) Mod():
Returns Remainder Value.

Syntax:
Mod(M,N)

Ex:
Sql> Select Mod(10,2) From Dual;-------0

5) Power():
The Power Of Given Expression

Syntax:
Power(M,N)

Ex:
Sql> Select Power(2,3) From Dual;----------8

6) Round():
> Nearest Value Given Expression.

Syntax:
Round(Number,[Decimal Places])

Ex:
Sql> Select Round(5.50) From Dual;------6
Sql> Select Round(32.456,2) From Dual;------32.46

7) Trunc:
-------
> Returns A Value Which Will Specified Number Of Decimal Places.

Syntax:
Trunc(Number,Decimal Places)

Ex:
Sql> Select Trunc(5.50) From Dual;---------5
Sql> Select Trunc(32.456,2) From Dual;----32.45
String Functions:
--------------------
Length():
------------------
> Length Of Given String.

Syntax:
Length(String)

Ex:
Sql> Select Length('Hello') From Dual;------------------------5
Sql> Select Length('Good Morning') From Dual;--------12

Sql> Select Ename,Length(Ename) From Emp;

Sql> Select * From Emp Where Length(Ename)=4;

Lower():
----------------
To Convert Upper Case Char's Into Lower Case Char's.

Syntax:
Lower(String)

Ex:
Sql> Select Lower('Hello') From Dual;
Sql> Update Emp Set Ename=Lower(Ename) Where Job='Clerk';

Upper():
---------------
Syntax:
Upper(String)

Ex:
Sql> Select Lower('Hello') From Dual;

Initcap():
------------------
To Convert First Char. Is Capital.

Syntax:
Initcap(String)
Ex:
Sql> Select Initcap('Hello') From Dual;
Sql> Select Initcap('Good Morning') From Dual;

Ltirm():
---------------
To Remove Unwanted Spaces (Or) Unwanted Characters From Left
Side
Of Given String.

Syntax:
Ltrim(String1[,String2])

Ex:
Sql> Select Ltrim(' Sai') From Dual;
Sql> Select Ltrim('Xxxxxxsai','X') From Dual;
Sql> Select Ltrim('123SAI','123') From Dual;

Rtrim():
---------------
To Remove Unwanted Spaces (Or) Unwanted Characters From Right
Side
Of Given String.

Syntax:
Rtrim(String1[,String2])

Ex:
Sql> Select Rtrim('Saixxxxxxx','X') From Dual;

Trim():
------------
To Remove Unwanted Spaces (Or) Unwanted Characters From Both
Sides
Of Given String.

Syntax:
----------------
Trim('Trimming Char' From 'String')

Ex:
Sql> Select Trim('X' From 'Xxxxxxsaixxxx') From Dual;
Lpad():
------------
To Fill A String With Specific Char. On Left Side Of Given
String.

Syntax:
---------------
Lpad(String1,Length,String2)

Ex:
Sql> Select Lpad('Hello',10,'@') From Dual;
@@@@@Hello

Rpad():
-------------
To Fill A String With Specific Char. On Right Side Of Given
String.

Syntax:
---------------
Rpad(String1,Length,String2)

Ex:
Sql> Select Rpad('Hello',10,'@') From Dual;
Hello@@@@@

Concat():
-----------------
Adding Two String Expressions.

Syntax:
--------------
Concat(String1,String2)

Ex:
Sql> Select Concat('Good','Bye') From Dual;

Replace():
-------------------
To Replace One String With Another String.

Syntax:
---------------
Replace(String1,String2,String3)
Ex:
Sql> Select Replace('Hello','Ell','Xyz') From Dual;
Hxyzo

Sql> Select Replace('Hello','L','Abc') From Dual;

Heabcabco

Translate():
------------------------
To Translate A Single Char With Another Single Char.

Syntax:
--------------
Translate(String1,String2,String3)

Ex:
Sql> Select Translate('Hello','Elo','Xyz') From Dual;
Hxyyz
Sol: E = X , L=Y , O=Z
Hello => Hxyyz

Ex:
Sql> Select Ename,Sal,Translate(Sal,'0123456789','$B@Gh*V#T%')
Salary From Emp;

Ename Sal Salary

---------- --------------- -------------------------
Smith 800 T$$

Sol: 0=$,1=B,2=@,3=G,4=H,5=*,6=V,7=#,8=T,9=%.

Substr():
-----------------
It Returns Req.Substring From Given String Expression.

Syntax:
---------------
Substr(String1,<Starting Position Of Char.>,<Length Of
Char's>)

Ex:
Sql> Select Substr('Hello',2,3) From Dual;
Ell
Sql> Select Substr('Welcome',4,2) From Dual;
Co

Sql> Select Substr('Welcome',-6,3) From Dual;

Elc

Instr():
--------------
Returns Occurence Position Of A Char. In The Given String.

Syntax:
---------------
Instr(String1,String2,<Starting Position Of Char.>,<Occurence
Position Of Char.>)

Ex:
Sql> Select Instr('Hello Welcome','O') From Dual;---------> 5
Sql> Select Instr('Hello Welcome','Z') From Dual;-----> 0
Sql> Select Instr('Hello Welcome','O',1,2) From Dual;-----11
Sql> Select Instr('Hello Welcome','E',5,2) From Dual;-------13
Sql> Select Instr('Hello Welcome','E',1,4) From Dual;--------8

Note:
----------
Position Of Char's Always Fixed Either Count From Left To Right
(Or) Right To Left.

Sol: Hello Welcome

12345 6 78910111213

Ex:
Sql> Select Instr('Hello Welcome','E',-1,3) From Dual;--------2
Sql> Select Instr('Hello Welcome','L',-4,3) From Dual;-------3
Sql> Select Instr('Hello Welcome','L',-6,3) From Dual;----------0
Date Functions:
------------------
1) Sysdate:
---------------------
> Current Date Information Of The System.

Ex:
Sql> Select Sysdate From Dual;
Sql> Select Sysdate+10 From Dual;
Sql> Select Sysdate-10 From Dual;

Add_Months():
--------------------------
> Adding No.Of Months To The Date.

Syntax:
---------------
Add_Months(Date,<No.Of Months>)

Ex:
Sql> Select Add_Months(Sysdate,3) From Dual;
Sql> Select Add_Months(Sysdate,-3) From Dual;

Last_Day():
---------------------
> Returns The Last Day Of The Month.

Syntax:
--------------
Last_Day(Date)

Ex:
Sql> Select Last_Day(Sysdate) From Dual;

Next_Day():
--------------------
> Returns The Next Specified Day From The Given Date.

Syntax:
---------------
Next_Day(Date,'<Day Name>')
Ex:
Sql> Select Next_Day(Sysdate,'Sunday') From Dual;

Months_Between():
------------------------------------
> Returns No.Of Months Between Two Date Expressions.

Syntax:
---------------
Months_Between(Date1,Date2)

Ex:
Sql> Select Months_Between('05-Jan-81','05-Jan-80') From Dual;---
-- 12
Sql> Select Months_Between('05-Jan-80','05-Jan-81') From Dual;---
-- -12

Note: Here, Date1 Is Always Greater Than Date2 Otherwise

Oracle Returns Nagative Value.

Conversion Functions:
-------------------------
1. To_Char()
2. To_Date()

To_Char():
-------------------
> Date Type To Char Type To Display Date In Different Fromat.

Syntax:
To_Char(Date,[<Format>])

Year Formats:
---------------------------
Yyyy - 2020
Yy - 20
Year - Twenty Twenty
Cc - Centuary 21
Ad / Bc - Ad Yaer / Bc Year
Ex:
Sql> Select To_Char(Sysdate,'Yyyy Yy Year Cc Ad') From Dual;
To_Char(Sysdate,'Yyyyyyyearccad')
----------------------------------------------------------
2020 20 Twenty Twenty 21 Ad

Q: To Display Employee Who Are Joined In Year 1982

By Using To_Char() Function ?

Sol:
Sql> Select * From Emp Where To_Char(Hiredate,'Yyyy')=1982;

Q: To Display Employee Who Are Joined In Year 1980,1982,1987

By Using To_Char() Function ?

Sol:
Sql> Select * From Emp Where To_Char(Hiredate,'Yyyy')
In(1980,1982,1987);

Month Format:
----------------------------
Mm - Month Number
Mon - First Three Char From Month Spelling
Month - Full Name Of Month

Ex:
Sql> Select To_Char(Sysdate,'Mm Mon Month') From Dual;

To_Char(Sysdate,
----------------
08 Aug August

Sql> Select To_Char(Sysdate,'Mm Mon Month') From Dual;

To_Char(Sysdate,
----------------
08 Aug August

Q: To Display Employee Who Are Joined In Feb,May,Dec Months

By Using To_Char() ?

Sol:
Sql> Select * From Emp Where To_Char(Hiredate,'Mm')
In(02,05,12);
Q: To Display Employee Who Are Joined In Feb 1981
By Using To_Char() ?

Sol:
Sql> Select * From Emp Where
To_Char(Hiredate,'Mmyyyy')='021981';

Day Formats:
-------------------------
Ddd - Day Of The Year.
Dd - Day Of The Month.
D - Day Of The Week
Sun - 1
Mon - 2
Tue - 3
Wen - 4
Thu - 5
Fri -6
Sat - 7

Day - Full Name Of The Day

Dy - First Three Char's Of Day Spelling

Ex:Sql> Select To_Char(Sysdate,'Ddd Dd D Day Dy') From Dual;

To_Char(Sysdate,'Ddddd
----------------------------------------------
220 07 6 Friday Fri

Q: To Display Employee Who Are Joined On "Friday" By Using

To_Char() ?

Sol:
Sql> Select * From Emp Where To_Char(Hiredate,'Day')='Friday';

Q: To Display Employee On Which Day Employees Are Joined ?

Sol:
Sql> Select Ename||' '||'Joined On'||' '||To_Char(Hiredate,'Day')
From Emp;
Note:
------
In Oracle Whenever We Using To_Char() And Also Within To_Char()
When We use Day / Month Format Then Oracle Server Internally
Allocate Some Extra Memory For Day/Month Format Of Data.
To Overcome The Above Problem That Is To Remove Extra
Memory Which Was Allocate By Oracle Server Then We Use A Pre-
Define Specifier Is
Called "Fm" (Fill Mode).

Ex:
Select * From Emp Where To_Char(Hiredate,'Fmday')='Friday';

Quater Format:
----------------------------
Q - One Digit Quater Of The Year

1 - Jan - Mar
2 - Apr - Jun
3 - Jul - Sep
4 - Oct - Dec

Ex:
Sql> Select To_Char(Sysdate,'Q') From Dual;
T
---
3

Q : Who Are Joined In 2ND Quater Of 1981 ?

Sol:
Sql> Select * From Emp Where To_Char(Hiredate,'Yyyy')='1981'
And To_Char(Hiredate,'Q')=2;

Week Format:
--------------------------
Ww - Week Of The Year
W - Week Of Month

Ex:
Sql> Select To_Char(Sysdate,'Ww W') From Dual;
To_C
---------
32 2

Time Format:
-------------------------
Hh - Hour Part
Hh24- 24 Hrs Fromat
Mi - Minute Part
Ss - Seconds Part
Am / Pm - Am Tme (Or) Pm Time

Ex:
Sql> Select To_Char(Sysdate,'Hh:Mi:Ss Am') From Dual;

To_Char(Sys
------------------------
12:04:21 Pm

To_Date():
------------------
To Convert Char Type To Oracle Date Format Type.

Syntax:
To_Date(String[,Fromat])

Ex:
Sql> Select To_Date('08/August/2020') From Dual;

To_Date('
---------
08-Aug-20

Sql> Select To_Date('08-Aug-2020')+10 From Dual;

To_Date('
---------
18-Aug-20
Multiple Row Functions:
-----------------------------------------------
These Functions Are Returns Either Group Of Values
(Or) A Single Value.

Sum():
-----------
> It Returns Sum Of A Specific Column Values.

Ex:
Sql> Select Sum(Sal) From Emp;
Sql> Select Sum(Sal) From Emp Where Job='Clerk';

Avg():
----------
> It Returns Average Of A Specific Column Values.

Ex:
Sql> Select Avg(Sal) From Emp;
Sql> Select Avg(Sal) From Emp Where Deptno=10;

Min():
----------
> It Returns Min.Value From Group Of Values.

Ex:
Sql> Select Min(Hiredate) From Emp;
Sql> Select Min(Hiredate) From Emp Where Job='Manager';
Sql> Select Min(Sal) From Emp;

Max():
-----------
> It Returns Max.Value From Group Of Values.

Ex:
Sql> Select Max(Sal) From Emp;

Count():
----------------
> It Returns No.Of Rows In A Tbale / No.Of Values In A Column
> Three Types,
I) Count(*)
Ii) Count(<Column Name>)
Iii) Count(Distinct <Column Name>)
Ex:
Test
--------
Sno Name
--- -----
101 A
102 B
103
104 C
105 A
106 C

Count(*):
----------------
> Counting All Rows (Duplicates & Nulls) In A Table.

Ex:
Sql> Select Count(*) From Test;

Count(*)
------------------
6

Count(<Column Name>):
--------------------------------------------
> Counting All Values Including Duplicate Values But Not Null Values
From A Column.

Ex:
Sql> Select Count(Name) From Test;

Count(Name)
-----------------------
5

Count(Distinct <Column Name>):

--------------------------------------------------------------
> Counting Unique Values From A Column.Here "Distinct" Keyword
Is Eliminating Duplicate Values.

Ex:
Sql> Select Count(Distinct Name) From Test;------- 3