Oracle Functions Types

1. User Defined Functions

2. System Defined Functions
Arithmetic Functions
Function action Example Displays
Ln(n) LN() returns the natural logarithm of a select ln(100) 4.605
number that is the base e logarithm of the from dual
number, where the number is greater than 0.

Log(B,N) The Oracle LOG() function is used to return select log(2,10) 3.321
the logarithm, base B of N. from dual

Sign(-n) Return sign of value n select sign(-10) -1

from dual

Sin(30) Return sin of value select sin(30) 0.98

from dual
 Oracle String Functions
Function action Example Displays

INITCAP( string1) INITCAP function sets the select initcap('hello') Hello

first character in each word from dual
to uppercase
LOWER(text) The LOWER() function SELECT LOWER('SQL sql
converts a string to lower- Tutorial is FUN!')from tutorial is
case. dual; fun!
UPPER(text) The UPPER() function SELECT UPPER('SQL SQL
converts a string to upper- Tutorial is FUN!'); TUTORIA
case. L IS FUN!

CONCAT(string1, The CONCAT() function adds select Techmax

string2, ...., string two or more strings concat('tech','max')
_n) together. from dual
REPLACE(string, o The REPLACE() function SELECT REPLACE('SQL SQL
ld_string, new_st replaces all occurrences of a Tutorial', 'T', 'M') from Mutorial
ring) substring within a string, dual
with a new substring
Function action Example Displays

TRANSLATE(string, The TRANSLATE() function SELECT TRANSLATE('Mon Sunday

characters, returns the string from the day', 'Monday', 'Sunday')
translations) first argument after the from dual
characters specified in the
second argument are
translated into the characters
specified in the third
argument.

LTRIM(string) Remove leading spaces from SELECT LTRIM(' SQL SQL

a string: Tutorial') from dual Tutorial
RTRIM(string) Remove trailing spaces from a SELECT RTRIM('SQL SQL
string: Tutorial ') from dual Tutorial
LPAD( string1, Add the given char to left side select lpad('tech',10,'@') @@@@
totallength , of string and maintain total from dual @@tech
padding char) length

RPAD( string1, Add the given char select rpad('tech',10,'@') tech@@

totallength , torightside of string and from dual @@@@
padding char) maintain total length
Function action Example Displays

LENGTH(string_ex The Oracle LENGTH() function select length('tech') from 4

pression); returns the number of dual
characters of a specified
string.

SOUNDEX(expressi Evaluate the similarity of two select soundex('yogi') Y200

on) strings and returns a four- from dual
character codebased on how
the string sounds when
spoken.

Concatenation operator: || Operator concate allows you to concatenate 2 or

more strings together.
Example:
Select string1 || string2 || string_n from dual;
Select ‘hello’||’welcome ‘||’to’||’sgm’ from dual;
 DATE AND TIME FUNCTIONS
Function Example Result Description

SYSDATE select sysdate from dual 09-OCT-20 Return the current system
date and time of the
operating system where the
Oracle Database resides.

ADD_MONTHS select add_months('1-jan-2003',2) 01-MAR-03 Add a number of months (n)

from dual to a date and return the
same day which is n of
months away.

LAST_DAY select last_day(sysdate) from dual 31-OCT-20 Gets the last day of the
month of a specified date.
NEXT_DAY select next_day('5-oct- 11-OCT-20 Get the first weekday that is
2020','sunday') from dual later than a specified date.

CURRENT_DATE select current_date from dual 09-OCT-20 Return the current date and
time in the session time
zone
 Function Example Result Description

CURRENT_TIMESTA select current_timestamp from dual 09-OCT-20 Return the current date and
MP 05.18.29.257000 AM time with time zone in the
+00:00 session time zone

MONTHS_BETWEEN select months_between(sysdate,'1-jan- 9.272613127240143369 Return the number of

2020') from dual 1756272401433691756 months between two dates.
3

SYSTIMESTAMP SELECT SYSTIMESTAMP FROM dual; 09-OCT-20 Return the system date and
10.52.49.761000 AM time that includes fractional
+05:30 seconds and time zone.

TO_CHAR select to_char(sysdate,'dy mon yy')from fri oct 20 Convert a DATE or an

dual INTERVAL value to a
character string in a
specified format.

TO_DATE TO_DATE( '01 Jan 2017', 'DD MON 01-JAN-17 Convert a date which is in
YYYY' ) the character string to
a DATE value.
Function Example Result Description

ROUND select round(sysdate,'day')from dual 11-OCT-20 Return a date rounded to a

specific unit of measure.
select round(to_date('2-oct- 04-OCT-20
2020'),'day')from dual

select round(sysdate,'month') from dual 01-OCT-20

select round(to_date('22-oct- 01-NOV-20

2020'),'month') from dual

TRUNC select trunc(to_date('25-oct- 01-JAN-20 Return a date truncated to a

2020'),'year') from dual specific unit of measure.

select trunc(to_date('25-oct- 01-OCT-20

2020'),'month') from dual

select trunc(to_date('7-oct-2020'),'day') 04-OCT-20

from dual
Oracle TO_CHAR Format Specifier
YYYY 4-digit year
YEAR Year in characters
YY 2-digit year
MON Abbreviated month (Jan - Dec)
MONTH Month name (January - December)
MM Month (1 - 12)
DY Abbreviated day (Sun - Sat)
D day of week in number(1 -7)
DD Day (1 - 31)
DDD Day of year in numbers(365)
HH24 Hour (0 - 23)
HH or HH12 Hour (1 - 12)
MI Minutes (0 - 59)
SS Seconds (0 - 59)
 Aggregate Functions
Function Example Result Description

Count(Column select count(rollno) from 7 The Oracle COUNT() function

name) student returns the number of items in
a group.
Count(*) select count(*) from 7 The COUNT(*) function returns
student the number of rows that
matches a specified criterion.
Min(column select min(per) from 78.9 Displays the minimum value for
name) student column

Max(column select max(per) from 98.9 Displays the maximum value for
name) student column

Sum(column select sum(per) from 640.5 Calculate sum of all values in

name) student given column

Avg(column select avg(per) from 91.5 Calculate average of all values

name) student in given column
 Clauses
Clause Example Result Description

Where select class from tyco 1. The WHERE clause is used

student where to filter records.
rollno=3
2. The WHERE clause is used to
extract only those records that
fulfill a specified condition.

Order by select rollno from Rollno displays 1. The ORDER BY keyword is

student order by in descending used to sort the result-set in
rollno desc order ascending or descending order.

2. By default it sort records in

ascending order.

3. To sort the records in

descending order, use the DESC
keyword.
 Clause Example Result Description

Group by select Address wise 1. The GROUP BY statement

count(*),address count will groups rows that have the
from student displays in same values into summary
group by address output rows, like "find the number of
customers in each country".

2. The GROUP BY statement is

often used with aggregate
functions (COUNT, MAX, MIN,
SUM, AVG) to group the result-set
by one or more columns.
Write SQL Queries for Following.
1.Display total salary spent for each department.

2. Display lowest paid salary under each department.

3. Display no of employee working in each department

4. Display details of employee with salary in decreasing order .

5. Display details of employee earning salary less than 60000 in

descending order
Answers
1. select sum(salary),dname from employee group by dname
2. select min(salary),dname from employee group by dname
3. select count(*),dname from employee group by dname
4. select salary from employee order by salary desc
5. select * from employee where salary<60000 order by salary desc
 JOINS
Joins - Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is
performed whenever two tables are joined in a SQL statement . For joining the tables we
need one column common in all tables

Types of Joins:
1. Simple join or Equi join
2. Non Equi Join
3. Inner join
4. Outer Join types
1. Left- Outer Join
2. Right Outer Join

3. Full Outer Join

Types of Joins:
1. Simple join or Equi join: -
Equi join returns records that have similar values in both tables. Equi join
use the equal (=)to operator to join two tables.

Syntax :
SELECT columns FROM table1 ,table2 Where table1.column = table2.column;

Example
select employee.deptno,employee.dname,ename,location from employee,dept where
employee.deptno=dept.deptno
Table Aliases :
To avoid Ambiguity in query we include table names in query. To make a
complicated query easy , we create table aliases which is alternate name given to table

Syntax :
SELECT columns FROM table1 t1,table2 t2Where t1.column = t2.column;

Example
select e.deptno,e.dname,ename,location from employee e ,dept d where
e.deptno=d.deptno
2. Non Equi join : - Non equi join use comparison operators instead of equal sign like
< , >, <=, >= ,!= and returns outputs.

Syntax :
SELECT columns FROM table1, table2 where table1.column != table2.column;

Example

select * from employee,dept where employee.deptno!=dept.deptno

3.Inner Join :- Inner Join Returns records that have matching values in both tables

Syntax :
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Example
select * from employee inner join dept on employee.deptno=dept.deptno
4. Outer Join :-
1. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records
from the right table

Syntax :
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column =
table2.column;
Example
select employee.deptno,dept.deptno from employee left outer join dept on
employee.deptno=dept.deptno
2. RIGHT (OUTER ) JOIN: Returns all records from the left table, and the matched
records from the right table

Syntax :
SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column =
table2.column;
Example
select employee.deptno,dept.deptno from employee right outer join dept on
employee.deptno=dept.deptno
3. FULL (OUTER ) JOIN: Returns all records when there is a match in either left or
right table

Syntax :
SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1.column =
table2.column;
Example
select employee.deptno,dept.deptno from employee Full outer join dept on
employee.deptno=dept.deptno
 View
In SQL, a view is a logical table created on physical base table. A view
contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database. You can add SQL functions, WHERE, and
JOIN statements to a view and present the data as if the data were coming from
one single table or from multiple tables. View is created using select command.
Syntax
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name
WHERE condition;
Examples
1. View created on single table
create view v12 as select deptno,dname from employee
2. View created on multiple tables
create view v1321 as select d.deptno,d.dname,empid from employee
e,dept d where d.deptno=e.deptno
 View and Joins
An Oracle VIEW, in essence, is a virtual table that does not physically exist
in the memory. It is used to display the only data to user.Rather, it is created by a
query joining one or more table.

Syntax
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table1 [join]
table2 on condition;

Example
1 create view v3 as select d.deptno,d.dname,empid from dept d inner join
employee e on d.deptno=e.deptno;

2.create view v31 as select d.deptno,d.dname,empid from dept d right outer join
employee e on d.deptno=e.deptno;
Displaying Views
We can display the content of View Using Select Command.

Syntax
Select * from viewname;

examples
Select * from v1;
Select * from V2;
Inserting Data in View:
1. Inserting data for view created on single table:
we can use insert command for view if it created on single table. It will insert
record in view as well as original table.
Syntax
Insert into viewname values(value1,value2……….);

Example
Insert into v1 values(1001,’electrical’,’mathura’);

2. Inserting data for view created on multiple table:

we can not use insert command for view if it created on multiple tables.
Updating Views:-
View can update the data from the table as well as view.
1. Updating View if created on single table:
we can use update command for view if it created on single table.It will
update view as well as table

Syntax
Update viewname set condition;
Example
update v1 set dname='ele' where deptno=111

2. Updating View if created on multiple tables:

we not can use update command for view if it created on multiple tables.
Deleting record from Views:-
we can delete record from view. It will delete record from view as well as
original table.
1. Deleting record from view created on single table:
we can delete record from view. It will delete record from view as well as
original table.
Syntax
Delete from viewname where condition;

Example
delete from v1 where deptno=111

2. Deleting record from view created on multiple table:

we can not delete record from view if view is created on more than one table
Dropping a view
We can delete the drop view using drop command.
Syntax:
Drop view viewname;

Example :
Drop view v1;
What View Can not Do?
If view is created on single base table then we can perform all DML
operations like insert,update,delete on view.
But if View is created using Joins or using multiple tables then we can not
perform all DML operations like insert,update,delete on view.
 Sequences
In Oracle, you can create an auto number field by using sequences. A sequence is
an object in Oracle that is used to generate a number sequence. This can be useful
when you need to create a unique number to act as a primary key.

Syntax
CREATE SEQUENCE sequence name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
Cycle| no cycle;
Explanation of Syntax

Min Value specifies the starting value for the Sequence.

Increment-value is the value by which sequence will be incremented.

Max value specifies the upper limit or the maximum value up to which sequence
will increment itself.

CYCLE specifies that if the maximum value exceeds the set limit, sequence will
restart its cycle from the beginning.

NO CYCLE specifies that if sequence exceeds MAXVALUE value, an error will be

thrown.

CACHE Specify how many values of the sequence the database preallocates and
keeps in memory for faster access
Example

CREATE SEQUENCE Sequence1

MINVALUE 1
START WITH 1
INCREMENT BY 1
No cycle
CACHE 20;
Accessing Sequence :
After creating sequence we can access ,its value with the help of currval and
nextval. We should use sequence in the table for numerical column.It will generate
auto number.

Example

insert into dept values(s1.nextval,'it','banglore')

Currval: - Currval Returns the current value to be inserted in the table through
sequence.
Example

select s1.currval from dual

Nextval- Nextval return next value to be inserted in the table through sequence.
Example

select s1.nextval from dual

Altering sequence:-

Sequence can be altered using alter sequence command.You can change values of
variables in sequence using this command.
Syntax:
Alter sequence <sequencename> variablevalues;

Example

alter sequence s1 maxvalue 20;

alter sequence s1 increment by 2

Dropping sequence:-
Sequence can be dropped using drop sequence command.

Syntax:

Drop sequence sequencename.

Example

Drop sequence s1;

 INDEX
Indexes are used to retrieve or access data from the tables more quickly. The users
cannot see the indexes, they are just used to speed up the activity of searching and
sorting the records. User may create multiple index on table which retrieve record
fast.
Types of index
1. Simple index
2. Composite index
3. Unique Index
1. Simple index:- Index created on single column is called simple index.
Syntax
CREATE INDEX index_name ON table_name (column1)
Example
create index idx1 on dept(deptno);
2. Composite Index: - Index created on multiple column is called composite index
Syntax:
CREATE INDEX index_name ON table_name (column1, column2, ...)
Example:
create index idx1 on dept(deptno,name);
3. Unique Index : Unique index do not allow duplicate values in indexed column.
Unique index created on single column and multiple column.
Syntax:
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
Example:
create unique index idx123 on dept(deptno)
Dropping index: Index can be deleted using Drop index.
Syntax: Drop index indexname;
Example : Drop index idx1;
 Synonyms
Synonyms is a name given to the table ,view sequence, stored
procedure ,function, packages for the users convenience to use it.

Creating Synonyms: Create synonyms command is used .

Syntax:
Create synonym <synonym name> for <object>;
Example : Create synonym k for dept;

Dropping Synonyms: Drop command is used to delete synonyms.

Syntax:
Drop synonyms <synonyms name>
Example :
drop synonym k ;