BUILT-IN FUNCTION

1. Group function or Aggregate function:

Sl.N Function Description

o
1 AVG Determines the Average of the specified values of column, Ignoring
null values.
2 COUNT Determines the total number of values of a specified column.
3 MAX Determines the maximum value of a specified column, ignoring null
values.
4 MIN Determines the minimum values of a specified column, ignoring null
values.
5 SUM Determines the sum of a specified column, ignoring null values.
6 ATDDEV Determines the standard derivation of a specified column , ignoring
null values.
7 VARIANCE Determines the variance of expression , ignoring null values.

2. Character Function:

Sl.N Function Description

o
1 LLOR Concatenates two string together changes the first letter of a word or
CONCAT series of words into upper case.
2 INIT CAP Find the location of character in a string otherwise return 0.
3 INSTR Find the Location of character in a string otherwise return 0.
4 LENGTH Returns the length of a string.
5 LOWER Converts every letter in a string to lower case.
6 UPPER Converts every letter in a string to upper case.
7 LPAD Makes a string a certain length by a adding a certain set of character to
the left.
8 RPAD Makes a string a certain length by a adding a certain set of character to
the Right.
9 LTRIM Trim all the occurrence of any one of a set of character of the Left side
of string.
10 RTRIM Trim all the occurrence of any one of a set of character of the Right
side of string.
11 SOUNDEX Finds words that sound like examples.
12 SUBSTR Clip out a piece of a string.
13 CHR Return the character of the specified ASCII values.
14 ASCII Return the ASCII values of the specified character.
15 TRANSLATE Replace the character by character.
16 REPLACE Replace the specified string or character by the existing string or
character if that string in found case sensitive.
3. Number Function:

Sl.N Function Description

o
1 ABS Return absolute values.
2 CELL Smallest integer large than or equal to value.
3 FLOOR Largest integer smaller than or equal to value.
4 SART Returns squares not of the specified by the number.
5 POWER Return power specified by number.
6 SIGN Return-1 of the number is negative;
Return 1 if the number is positive and return 0 if the number is 0.
7 TRUNC Truncates the number after the decimal depending on the specified
integer.
8 ROUND Round the expression to the specified number of decimals.
9 EXP Returns e raised to n power.
10 MOD Return module value.
11 LN Nateual Logarithm values.
12 LOL Base 10 Loguith value.
13 VSIZE Storade size of a value.
14 GREATEST Greatest value of a list.
15 LEAST Least value of a llist.

4. Date Function:

Sl.N Function Description

o
1 SYS DATE Returns system date
2 ADD_MONTHS Add or substracts months to or from a date, return date as result.
3 NEXT_DAY Returns the date of next specified day of the week after the date.
4 CASE_DAY Returns the date of the last day of the months specified.
5 MONTHS_BETWEE Returns number of months between dates.
N
6 ROUND Round the date d by the specified format, if format is not
specified it defaults to ‘DD’. Which date to the neatest day.
7 TRUNC Rounds the date d truncates to the unit specified by omitted is
defaults to ‘DP’ which truncates to the nearest day.

5. Convertion Function:

Sl.N Function Description

o
 1 TO_CHAR Convert the date‘d’ to character format ‘f’.
2 TO_DATE Convert the date‘d’ to date format ‘f’.
3 DECODE Records the specified date to another representation.
4 TO_NUMBER Convert the character to number.

CHARACTER FUNCTION:

SQL> select * from stud;

ROLLNO NAME DEPT MARKS

---------- --------------------------- -------------- ----------
1 mano cse 100
4 ezhil pradha cse 78
5 mahalakshmi cse 78
3 kalaivani cse 90
2 elanangai cse 80

SQL> select upper(name) from stud;

UPPER(NAME)
---------------
MANO
EZHIL PRADHA
MAHALAKSHMI
KALAIVANI
ELANANGAI

SQL> select lower(name) from stud;

LOWER(NAME)
---------------
mano
ezhil pradha
mahalakshmi
kalaivani
elanangai

SQL> select initcap(name) from stud;

INITCAP(NAME)
---------------
Mano
Ezhil pradha
Mahalakshmi
Kalaivani
Elanangai
SQL> select substr('manobharathi',1,4) from dual;

SUBS
----
mano

SQL> select ltrim('welcome','wel') from dual;

LTRI
----
come

SQL> select rtrim('welcome','come') from dual;

RTR
---
wel

SQL> select lpad('welcome',13,'$') from dual;

LPAD('WELCOME
-------------
$$$$$$welcome

SQL> select rpad('welcome',13,'$') from dual;

RPAD('WELCOME
-------------
welcome$$$$$$

SQL> select name from stud where name like 'm%';

NAME
---------------
Mano
Mahalakshmi

SQL> select replace('jack jill joy','j','b') from dual;

REPLACE('JACK
-------------
back bill boy

SQL> select translate('jack jall joy','ja','b') from dual;

TRANSLATE('
-----------
bck bll boy

DATE FUNCTION:
SQL> select add_months('25-dec-07',4) from dual;

ADD_MONTH
---------
25-APR-08

SQL> select last_day('25-dec-07') from dual;

LAST_DAY(
---------
31-DEC-07

SQL> select months_between('15-dec-07','13-aug-07') from dual;

MONTHS_BETWEEN('15-DEC-07','13-AUG-07')
---------------------------------------
4.06451613

SQL> select next_day('1-jul-07','friday') from dual;

NEXT_DAY(
---------
06-JUL-07

SQL> select greatest('22-may-07','22-dec-07') from dual;

GREATEST(
---------
22-may-07

AGGREGATE FUNCTION:

SQL> select avg(marks) from stud;

AVG(MARKS)
----------
75.2

SQL> select min(marks) from stud;

MIN(MARKS)
----------
50
SQL> select max(marks) from stud;

MAX(MARKS)
----------
90

SQL> select count(marks) from stud;

COUNT(MARKS)
------------
5

SQL> select sum(marks) from stud;

SUM(MARKS)
----------
376

SQL> select stddev(1755) from dual;

STDDEV(1755)
------------
0

SQL> select variance(1755) from dual;

VARIANCE(1755)
--------------
0

NUMERIC FUNCTIONS:

SQL> SELECT ABS(-15) FROM DUAL;

ABS(-15)
----------
15

SQL> select abs(-15) from dual;

ABS(-15)
----------
15

SQL> select power(2,3) from dual;

POWER(2,3)
----------
8

SQL> select round(189.9999,2) from dual;

ROUND(189.9999,2)
-----------------
 190
SQL> select trunc(189.9999,2) from dual;
TRUNC(189.9999,2)
-----------------
189.99

SQL> select mod(10,3) from dual;

MOD(10,3)
----------
1
SQL> select sign(16) from dual;
SIGN(16)
----------
1

SQL> select sign(-16) from dual;

SIGN(-16)
----------
-1
SQL> select sin(45) from dual;
SIN(45)
----------
.850903525

SQL> select cos(45) from dual;

COS(45)
----------
.525321989

SQL> select sinh(60) from dual;

SINH(60)
----------
5.7100E+25

SQL> select cosh(60) from dual;

COSH(60)
----------
5.7100E+25

SQL> select ceil(134.78) from dual;

CEIL(134.78)
------------
135
SQL> select floor(134.78) from dual;
FLOOR(134.78)
-------------
134
SQL> select ln(35) from dual;

LN(35)
----------
3.55534806

SQL> select log(10,1000) from dual;

LOG(10,1000)
------------
3

SQL> select sqrt(25) from dual;

SQRT(25)
----------
5

Operators

RELATIONAL/COMPERIS LOGICAL SPECIAL ARITHMETI SET

ON OPERATOR OPERATOR C OPERATOR
OPERATIONS S S OPERATOR S
S
= AND In + Union
> OR Between - Union all
< NOT Like * Intersect
>= Is / Minus
<= Any
<> All
!= The

Set Operators

1. Union
Description:
Multiple queries can be put together and their output combined using union clause with
duplication, the
union clause merges the output of 2 or more queries into single set of rows and columns.

SQL> select c_name from borrower union select c_name from depositor;

C_NAME
----------
Mano
Mahalakshmi
Ezhilpradha
Kalaivani
Elanangai

2. Union All
Description:
Multiple queries can be put together and their output combined using union all clause with
duplicate.

SQL> select c_name from borrower union all select c_name from depositor;

C_NAME
----------
Mano
Mahalakshmi
Ezhilpradha
Kalaivani
Elanangai
Mano
Mahalakshmi
15 rows selected.

3. Intersection
Description:
Multiple queries can be put together and their output combine using union clause intersect
clause output
Only rows produced by both the queries intersected.

SQL> select c_name from borrower intersect select c_name from depositor;

C_NAME
----------
Mano
Mahalakshmi

4. Minus
Description:
Multiple queries can be put together and their output can be combines using minus clauses.
The minus
Clause output the rows produced by both the first query, after filtering the rows retrieved by second
query.

SQL> select c_name from borrower minus select c_name from depositor;
C_NAME
----------
Mano

LOGICAL OPERATORS:
SQL> select * from student where name like('m%') and regno>10;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 27 cse mit
mahalak 26 cse mit

SQL> select * from student where name like('m%')or regno>25;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 27 cse mit
mahalak 26 cse mit

SQL> select * from student where not exists(select regno from student where dept='cse');

no rows selected

SPECIAL OPERATORS

SQL> select * from student where regno between 20 and 35;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 27 cse mit
mahalak 26 cse mit
kalavani 21 cse mit

SQL> select * from student where regno not between 20 and 35;

NAME REGNO DEPT COL

---------- ---------- ---- ----
ezhil 15 cse mit
ilanangai 16 cse mit

SQL> select regno,name from student where regno no in 26;

select regno,name from student where regno no in 26
*
ERROR at line 1:
ORA-00920: invalid relational operator

SQL> select regno,name from student where regno not in 26;

REGNO NAME
---------- ----------
27 mano
15 ezhil
21 kalavani
16 ilanangai

ARITHMETIC OPERATORS:

SQL> update student set regno=regno+10;

5 rows updated.

SQL> select * from student;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 37 cse mit
mahalak 36 cse mit
ezhil 25 cse mit
kalavani 31 cse mit
ilanangai 26 cse mit

SQL> update student set regno=regno-10;

5 rows updated.

SQL> select * from student;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 27 cse mit
mahalak 26 cse mit
ezhil 15 cse mit
kalavani 21 cse mit
ilanangai 16 cse mit

SQL> update student set regno=regno*3;

5 rows updated.

SQL> select * from student;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 81 cse mit
mahalak 78 cse mit
ezhil 45 cse mit
kalavani 63 cse mit
ilanangai 48 cse mit

SQL> update student set regno=regno/2;

5 rows updated.

SQL> select * from student;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 41 cse mit
mahalak 39 cse mit
ezhil 23 cse mit
kalavani 32 cse mit
ilanangai 24 cse mit

RELATIONAL OPERATORS

SQL> select * from student where regno>27

2 ;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 41 cse mit
mahalak 39 cse mit
kalavani 32 cse mit

SQL> select * from student where regno>=50;

no rows selected

SQL> select * from student where regno=27;

no rows selected

SQL> select * from student where regno=41;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mano 41 cse mit

SQL> select * from student where regno<>70;

NAME REGNO DEPT COL
---------- ---------- ---- ----
mano 41 cse mit
mahalak 39 cse mit
ezhil 23 cse mit
kalavani 32 cse mit
ilanangai 24 cse mit

SQL> select * from student where regno<40;

NAME REGNO DEPT COL

---------- ---------- ---- ----
mahalak 39 cse mit
ezhil 23 cse mit
kalavani 32 cse mit
ilanangai 24 cse mit

SQL> select * from student where regno>90;

no rows selected