INTRODUCTION
SQL is divided into the following
- Data Definition
Language (DDL).
- Data Manipulation
Language (DML)
- Data Retrieval Language
(DRL)
- Transaction Control
Language (TCL)
- Data Control Language
(DCL)
DDL: - create, alter, drop, truncate,
rename
DML: - insert, update, delete
DRL: - select
TCL: - commit, rollback,
savepoint.
DCL: - grant, revoke.
Data
Definition Language
1. CREATE:
Create table <table_name> (col1
datatype1, col2 datatype2 …coln datatypen);
Ex:
SQL> create table student (no number (2), name varchar (10), marks
number (3));
Table created.
2. INSERT:
This will be
used to insert the records into table. We
have two methods to insert.
a)
By value method
b) By address method
I. USING VALUE METHOD:
syntax: insert into <table_name) values (value1,
value2, value3 …. Valuen);
Ex: SQL> insert
into student values (1, ’sudha’, 100);
1 row
created.
SQL> insert
into student values (2, ’saketh’, 200);
1 row created.
To insert a new
record again you have to type entire insert command, if there is lot of
records this
will be difficult. This will be avoided by using address method.
II. USING ADDRESS METHOD:
Syntax:
insert into <table_name> values (&col1, &col2, &col3
…. &coln);
This will prompt you for the values but for
every insert you have to use forward slash.
Ex: SQL>
insert into student values (&no, '&name', &marks);
Enter value for no: 1
Enter value for name: Jagan
Enter value for marks: 300
old 1:
insert into student values(&no, '&name', &marks)
new 1:
insert into student values(1, 'Jagan', 300)
1 row created.
SQL> /
Enter value for no: 2
Enter value for name: Naren
Enter value for marks: 400
old 1:
insert into student values(&no, '&name', &marks)
new 1:
insert into student values(2, 'Naren', 400)
1 row created.
III. INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE
METHOD:
Syntax: insert into <table_name)(col1, col2, col3 …
Coln) values
(value1,
value2, value3… value-n);
Ex: SQL> insert into student (no, name) values (3,
’Ramesh’);
1 row created.
SQL> insert
into student (no, name) values (4, ’Madhu’);
1 row created.
IV. INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS
METHOD:
Syntax: insert into <table_name)(col1, col2, col3 …
coln) values
(&col1,
&col2, &col3 …. &coln);
This will prompt you for the values but
for every insert you have to use forward slash.
Ex:
SQL> insert into student (no, name) values (&no, '&name');
Enter value for no: 5
Enter value for name: Visu
old 1: insert into student (no, name)
values(&no, '&name')
new 1: insert into student (no, name) values(5, 'Visu')
1 row created.
SQL>
/
Enter value for no: 6
Enter value for name: Rattu
old 1: insert into student (no, name)
values(&no, '&name')
new 1: insert into student (no, name) values(6, 'Rattu')
1 row created.
3. SELECT:
Syntax: Select * from <table_name>; //here * indicates all columns
(or)
Select col1, col2, coln
from <table_name>;
Ex: SQL>
select * from student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
2 rows selected.
SQL> select no, name,
marks from student;
NO
NAME MARKS
---
------ --------
1 Sudha 100
2 Saketh 200
2 rows selected.
SQL> select
no, name from student;
NO NAME
---
-------
1 Sudha
2 Saketh
1 Jagan
3
rows selected.
CONDITIONAL SELECTIONS AND
OPERATORS
We have two clauses used in this
1. Where
2. Order by
USING WHERE
Syntax:
select * from <table_name> where <condition>;
The
following are the different types of operators used in where clause.
v
Arithmetic operators
v
Comparison operators
v
Logical operators
a. Arithmetic operators:-
Highest precedence +, -,
*, /
b. Comparison operators:-
=, !=, >,
<, >=, <=, <>
·
between, not between
·
in, not in
·
null, not null
·
like
c.
Logical
operators:-
·
And
·
Or --
lowest precedence
·
not
a) USING =, >,
<, >=, <=, !=, <>
Ex: SQL> select * from student where no = 2;
NO NAME MARKS
---
------- ---------
2
Saketh 200
2
Naren 400
2 rows selected.
SQL>
select * from student where no < 2;
NO NAME MARKS
---
------- ----------
1
Sudha 100
1
Jagan 300
2 rows selected.
SQL>
select * from student where no > 2;
NO NAME
MARKS
--- ------- ----------
3
Ramesh
4
Madhu
5
Visu
6
Rattu
4 rows selected.
SQL>
select * from student where no <= 2;
NO NAME MARKS
--- ------- ----------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
4
rows selected.
SQL>
select * from student where no >= 2;
NO NAME MARKS
--- ------- ---------
2 Saketh 200
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
6 rows selected.
SQL> select * from student where no != 2;
NO NAME
MARKS
--- -------
----------
1 Sudha 100
1 Jagan
300
3 Ramesh
4 Madhu
5 Visu
6 Rattu
6 rows selected.
SQL> select * from student where no <> 2;
NO NAME MARKS
--- ------- ----------
1
Sudha 100
1
Jagan 300
3
Ramesh
4
Madhu
5
Visu
6 Rattu
6
rows selected.
b) USING AND
This will gives the
output when all the conditions become true.
syntax:
select * from
<table_name> where <condition1> and <condition2> and ..
<conditionn>;
Ex:
SQL> select * from student where no = 2 and marks >= 200;
NO NAME MARKS
--- ------- --------
2
Saketh 200
2
Naren 400
2 rows
selected.
c) USING OR
This will gives
the output when either of the conditions become true.
syntax: select * from
<table_name> where <condition1> and <condition2> or ..
<conditionn>;
Ex: SQL> select * from student where no = 2
or marks >= 200;
NO NAME
MARKS
--- ------- ---------
2
Saketh 200
1
Jagan 300
2
Naren 400
3 rows selected.
d) USING
BETWEEN
This will gives
the output based on the column and its lower bound, upperbound.
Syntax : select
* from <table_name> where <col> between <lower bound> and
<upper bound>;
Ex:
SQL> select * from student where marks between 200 and 400;
NO NAME MARKS
---
------- ---------
2
Saketh 200
1
Jagan 300
2
Naren 400
3 rows selected.
e) USING NOT
BETWEEN
This will gives
the output based on the column which values are not in its lower bound,
upperbound.
Syntax: select * from <table_name> where
<col> not between <lower bound> and
<upper bound>;
Ex: SQL> select * from student where
marks not between 200 and 400;
NO NAME MARKS
---
------- ---------
1 Sudha 100
1
row selected.
f) USING “IN”:
This will gives the output based on the column and its
list of values specified.
Syntax:
select * from
<table_name> where <col> in ( value1, value2, value3 … valuen);
Ex: SQL> select * from student where no
in (1, 2, 3);
NO NAME MARKS
--- ------- ---------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
5
rows selected.
g) USING “NOT IN”:- This will gives the
output based on the column which values
are
not in the list of values specified.
Syntax:
select * from <table_name> where <col> not in ( value1, value2,
value3 … valuen);
Ex: SQL> select * from student where no not in
(1, 2, 3);
NO NAME MARKS
--- -------
---------
4
Madhu
5
Visu
6
Rattu
3 rows selected.
h) USING “NULL”:-
This will gives the output based on the null values in the
specified column.
Syntax: select * from <table_name> where <col> is null;
Ex: SQL> select * from student where marks is null;
NO NAME
MARKS
--- ------- ---------
3
Ramesh
4
Madhu
5
Visu
6
Rattu
4 rows selected.
i) USING NOT NULL:-
This will gives the output based on the not null
values in the specified column.
syntax:
select * from <table_name>
where <col> is not null;
Ex:
SQL> select * from student where marks is not null;
NO NAME MARKS
---
------- ---------
1
Sudha 100
2
Saketh 200
1
Jagan 300
2
Naren 400
4 rows selected.
j) USING LIKE:-
This
will be used to search through the rows of database column based on the pattern
you specify.
syntax: select * from <table_name> where <col> like <pattern>;
Ex:
i) This will give the rows whose
marks are 100.
SQL> select * from student where marks like 100;
NO NAME MARKS
--- ------- ---------
1 Sudha 100
1
row selected.
ii)
This will give the rows whose name start with ‘S’.
SQL>
select * from student where name like 'S%';
NO NAME
MARKS
---
------- ---------
1
Sudha 100
2 Saketh 200
2
rows selected.
iii) This will give the rows whose name ends with ‘h’.
SQL> select * from student where name like '%h';
NO NAME MARKS
--- ------- ---------
2 Saketh 200
3 Ramesh
2 rows selected.
iV) This will give the rows
whose name’s second letter start with ‘a’.
SQL>
select * from student where name like '_a%';
NO NAME MARKS
--- ------- --------
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
6 Rattu
6
rows selected.
V) This will give the rows whose name’s third letter start with
‘d’.
SQL> select * from student where name like '__d%';
NO NAME MARKS
--- -------
---------
1 Sudha 100
4 Madhu
2
rows selected.
Vi) This will give the rows whose
name’s second letter start with ‘t’ from ending.
SQL> select * from student where name like '%t_';
NO NAME MARKS
--- ------- ---------
2 Saketh 200
6 Rattu
2
rows selected.
Vii) This will give the rows whose name’s third letter
start with ‘e’ from ending.
SQL> select *
from student where name like '%e__';
NO
NAME MARKS
--- ------- ---------
2 Saketh 200
3 Ramesh
2 rows selected.
Viii) This will give the rows whose name cotains 2 a’s.
SQL> select * from student where name
like '%a% a %';
NO NAME MARKS
--- ------- ----------
1 Jagan 300
1
row selected.
* You have to specify the patterns in like using underscore ( _ ).
USING ORDER BY
This will be used to
ordering the columns data (ascending or descending).
Syntax:
Select * from <table_name> order by <col>
desc;
By default
oracle will use ascending order.
If you want
output in descending order you have to use desc
keyword after the column.
Ex: SQL> select * from student order by no;
NO NAME MARKS
--- ------- ---------
1 Sudha 100
1 Jagan 300
2 Saketh 200
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
8 rows selected.
SQL> select * from student order by no desc;
NO NAME MARKS
---
------- ---------
6 Rattu
5 Visu
4 Madhu
3 Ramesh
2 Saketh 200
2 Naren 400
1 Sudha 100
1 Jagan 300
8 rows selected.
USING DML
USING UPDATE:-
This can be used to modify the table data.
Syntax:Update <table_name> set <col1>
= value1, <col2> = value2 where
<condition>;
Ex: SQL>
update student set marks = 500;
If you are not specifying any condition this will update entire table.
SQL> update student set marks = 500 where
no = 2;
SQL> update student set marks = 500, name
= 'Venu' where no = 1;
USING DELETE:-
This can be used
to delete the table data temporarily.
This can be used
to delete specific “row/s “from the table using the condition.
Syntax: Delete <table_name>
where <condition>;
Ex: SQL>
delete student;
If you are not specifying any condition this will delete entire table.
SQL> delete student where no = 2;
USING DDL
USING ALTER
This can be used to add or remove
columns and to modify the precision of the datatype.
a) ADDING COLUMN
syntax: alter table <table_name> add <col
datatype>;
Ex:
SQL> alter table student add
sdob date;
b) REMOVING COLUMN
syntax: alter table <table_name> drop column<col
datatype>;
Ex: SQL> alter table student drop column
sdob;
c) INCREASING OR DECREASING PRECISION OF A
COLUMN
syntax: alter table <table_name> modify <col datatype>;
Ex:
SQL> alter table student modify marks number (5);
* To decrease precision the column should
be empty.
* Or
we can decrease the precision if length of the datatype is greater than the
Column
value.
d) MAKING COLUMN UNUSED
syntax: alter table <table_name> set unused column <col>;
Ex: SQL> alter table student set unused
column marks;
Even though the column is unused still it
will occupy memory.
d) DROPPING UNUSED COLUMNS
syntax: alter table <table_name> drop unused columns;
Ex:
SQL> alter table student drop unused columns;
* You can not drop individual unused columns
of a table.
e) RENAMING COLUMN
syntax: alter table <table_name> rename column <old_col_name> to
<new_col_name>;
Ex:
SQL> alter table student rename column marks to smarks;
USING TRUNCATE
This can be used to delete
the entire table data permanently.But table structure is available in the
database. Here (after truncate ) we can
re enter the values into table.
Syntax: truncate
table <table_name>;
Ex: SQL> truncate table student;
USING DROP
This will be
used to drop the database object.Here entire data and structure of the table
will lose from the database.
Syntax: Drop table <table_name>;
Ex: SQL> drop table student;
USING RENAME
This will be used to rename the database object;
Syntax: rename
<old_table_name> to <new_table_name>;
Ex: SQL> rename student to
stud;
USING TCL
USING COMMIT
This will be used to save the work.
Commit is of two types.
a) Implicit
b) Explicit
a) IMPLICIT
This will be issued by oracle internally
in two situations.
When any DDL operation is performed.
When you are exiting from SQL * PLUS.
b) EXPLICIT
This will be issued by the user.
Syntax:
Commit or commit work;
* When ever you committed then the
transaction was completed.
USING ROLLBACK
This will undo the operation.
This will be applied in two
methods.
Upto previous commit
Upto previous rollback
Syntax: Roll or roll work; (or)
Rollback or rollback work;
* While process
is going on, if suddenly power goes then oracle will rollback the transaction.
USING SAVEPOINT
You can use
savepoints to rollback portions of your current set of transactions.
Syntax: Savepoint <savepoint_name>;
Ex: SQL> savepoint s1;
SQL>
insert into student values(1, ‘a’, 100);
SQL>
savepoint s2;
SQL>
insert into student values(2, ‘b’, 200);
SQL> savepoint s3;
SQL>
insert into student values(3, ‘c’, 300);
SQL>
savepoint s4;
SQL>
insert into student values(4, ‘d’, 400);
Before rollback
SQL> select * from student;
NO NAME MARKS
---
------- ----------
1 a
100
2 b
200
3 c
300
4 d
400
4 rows selected.
SQL> rollback to savepoint s3; (or)
SQL> rollback to s3;
This will rollback last two records.
SQL> select * from student;
NO NAME
MARKS
---
------- ----------
1 a 100
2 b 200
2 rows selected.
DCL commands
are used to granting and revoking the permissions.
USING GRANT
This is used to
grant the privileges to other users.
Syntax:
Grant <privileges> on
<object_name> to <user_name> [with grant option];
Ex:
SQL> grant select on student to sudha; -- you can give individual privilege
SQL>
grant select , insert on student to sudha; --
you can give set of privileges
SQL> grant all on student to sudha; -- you can give all privileges
The sudha user has to use dot method to
access the object.
SQL> select * from saketh.student;
The sudha user can not
grant permission on student table to other users. To get this type
of option use the following.
SQL> grant all on student to sudha with grant option;
Now sudha user also has the grant
permissions on student table.
USING REVOKE
This is used to revoke the privileges from
the users to which you granted the privileges.
Syntax: Revoke <privileges> on <object_name>
from <user_name>;
Ex:
SQL> revoke select on student form sudha;-- you can revoke individual
privilege
SQL>
revoke select, insert on student from sudha;
-- you can revoke set of privileges
SQL> revoke all on student from sudha; -- you can revoke all privileges
USING ALIASES
CREATE WITH SELECT
We can create a table using existing table [along with data].
Syntax:
Create table <new_table_name>
[col1, col2, col3 ... coln] as select
* from <old_table_name>;
Ex: SQL> create table
student1 as select * from student;
Creating table with your own column names.
SQL>
create table student2 (sno, sname, smarks) as select * from student;
Creating table with specified columns.
SQL>
create table student3 as select no, name from student;
Creating
table with out table data.
SQL>
create table student2 (sno, sname, smarks) as select * from student where 1 =
2;
In the above where clause give any
condition which does not satisfy.
INSERT WITH SELECT
Using this we
can insert existing table data to another table in a single trip. But the table
structure should be same.
Syntax: Insert into <table1> select * from <table2>;
Ex: SQL> insert into
student1 select * from student;
Inserting data into specified columns
SQL> insert into student1(no, name)
select no, name from student;
COLUMN ALIASES
Syntax:
Select <orginal_col>
<alias_name> from <table_name>;
Ex:
SQL> select no sno from student;
or SQL> select no “sno”
from student;
TABLE ALIASES
If you are
using table aliases you can use dot method to the columns.
Syntax:
Select <alias_name>.<col1>,
<alias_name>.<col2> … <alias_name>.<coln>
from <table_name> <alias_name>;
Ex:
SQL> select s.no, s.name from student s;
USING MERGE
MERGE
You can use
merge command to perform insert and update in a single command.
Ex:
SQL> merge into student1 s1 using (select * from student2) s2 on (s1.no=s2.no)
when matched then update set marks = s2.marks when not matched then insert
(s1.no, s1.name, s1.marks) values (s2.no, s2.name, s2.marks);
In the above the two tables are with the same structure but we can merge
different structured tables also but the datatype of the columns should match.
Assume that
student1 has columns like no, name, marks and student2 has columns like no, name,
hno, city.
SQL> Merge into student1 s1using (select
* from student2) s2 on (s1.no=s2.no) when matched then update set marks = s2.hno when not matched
then insert (s1.no,s1.name,s1.marks) values(s2.no,s2.name,s2.hno);
MULTIPLE INSERTS
We have table
called DEPT with the following columns and data
DEPTNO DNAME LOC
-------- -------- -------
10 accounting New
York
20 research Dallas
30 sales Chicago
40 operations boston
a) CREATE STUDENT TABLE
SQL> Create table student (no
number(2),name varchar(2),marks number(3));
b) MULTI INSERT WITH ALL FIELDS
SQL> Insert all into student
values(1,’a’,100) into student values(2,’b’,200) into student values(3,’c’,300)
select *from dept where deptno=10;
This
inserts 3 rows
c) MULTI INSERT WITH SPECIFIED
FIELDS
SQL>
insert all into student (no,name) values(4,’d’) into student(name,marks) values(’e’,400)
into student values(3,’c’,300) select *from dept where deptno=10;
--
This inserts 3 rows
d) MULTI INSERT WITH DUPLICATE ROWS
SQL>
insert all into student values(1,’a’,100) into student values(2,’b’,200) into
student values(3,’c’,300) select *from dept where deptno > 10;
-- This inserts 9 rows because in the
select statement retrieves 3 records (3 inserts for each row retrieved)
e) MULTI INSERT WITH CONDITIONS
BASED
SQL> Insert all when deptno
> 10 then into student1 values (1,’a’, 100) when dname= ‘SALES’ then into
student2 values (2,’b’, 200) when loc = ‘NEW YORK’ then into student3 values (3,’c’,
300) select *from dept where deptno>10;
This inserts 5 rows because the first condition satisfied 3 times, second
condition satisfied once and the last condition satisfied once.
f) MULTI INSERT WITH CONDITIONS
BASED AND ELSE
SQL> insert all when deptno
> 100 then into student1 values (1,’a’, 100) when dname = ‘S’ then into
student2 values (2,’b’, 200) when loc = ‘NEW YORK’ then into student3 values (3,’c’,
300) else into student values (4,’d’, 400) select *from dept where
deptno>10;
-- This
inserts 3 records because the else satisfied 3 times
g) MULTI INSERT WITH CONDITIONS
BASED AND FIRST
SQL> Insert first when deptno = 20 then
into student1 values (1,’a’, 100) when dname = ‘RESEARCH’ then into student2
values(2,’b’,200) when loc = ‘NEW YORK’ then into student3 values(3,’c’,300) select
*from dept where deptno=20;
-- This inserts 1 record because the “first” clause avoids checking
the remaining conditions once the condition is satisfied.
h) MULTI INSERT WITH CONDITIONS
BASED, FIRST AND ELSE
SQL> insert first when deptno = 30 then
into student1 values (1,’a’, 100) when dname = ‘R’ then into student2 values (2,’b’,
200) when loc = ‘NEW YORK’ then into student3 values (3,’c’,300) else into
student values(4,’d’,400) select *from dept where deptno=20;
-- This inserts 1 record because the else clause satisfied
once
i) MULTI INSERT WITH MULTIBLE
TABLES
SQL>
Insert all into student1 values(1,’a’,100) into student2 values(2,’b’,200) into
student3 values(3,’c’,300) select *from dept where deptno=10;
-- This
inserts 3 rows
** You can use multiple tables with specified fields,
with duplicate rows, with conditions,
with first and else clauses.
FUNCTIONS
Functions can be categorized as follows.
v Single row functions
v Group functions
SINGLE ROW FUNCTIONS
Single row functions can be
categorized into five. These will be applied for each row and produces
individual output for each row.
I.
Numeric/Arithmetic functions
II.
String functions
III.
Date functions
IV.
Miscellaneous functions
V.
Conversion functions
I.
NUMERIC/ARITHMETIC FUNCTIONS
a) Abs
b) Sign
c) Sqrt
d) Mod
e) Nvl
f) Power
g) Exp
h) Ln
i)
Log
j)
Ceil
k) Floor
l)
Round
m) Trunk
n) Bitand
o) Greatest
p) Least
q) Coalesce
a) Abs:- Absolute value is the measure
of the magnitude of value. Absolute value is always a positive number.
Syntax: abs (value)
Ex: SQL> select abs(5), abs(-5), abs(0),
abs(null) from dual;
ABS (5) ABS(-5)
ABS(0) ABS(NULL)
---------- ---------- ----------
-------------
5 5 0
b) SIGN:- Sign gives the sign of a
value.
Syntax: sign (value)
Ex: SQL> select sign(5),
sign(-5), sign(0), sign(null) from dual;
SIGN (5) SIGN (-5)
SIGN (0) SIGN (NULL)
----------
---------- ---------- --------------
1 -1 0
c) SQRT:- This will give the square root of the
given value.
Syntax:
sqrt (value) -- here value must be positive.
Ex: SQL> select sqrt(4),
sqrt(0), sqrt(null), sqrt(1) from dual;
SQRT (4) SQRT (0)
SQRT (NULL) SQRT (1)
---------- ---------- --------------- ----------
2 0 1
d) MOD This will give the remainder.
Syntax:
mod (value, divisor)
Ex: SQL> select mod(7,4),
mod(1,5), mod(null,null), mod(0,0), mod(-7,4) from dual;
MOD(7,4) MOD(1,5) MOD(NULL,NULL) MOD(0,0)
MOD(-7,4)
------------ ----------
--------------------- ----------- -------------
3 1
0 -3
e) NVL:- This will substitutes the specified value
in the place of null values.
Syntax: nvl (null_col, replacement_value)
Ex: SQL> select * from
student; -- here for 3rd row
marks value is null
NO NAME MARKS
--- ------- ---------
1
a 100
2 b
200
3 c
SQL> select no, name, nvl(marks,300)
from student;
NO NAME NVL(MARKS,300)
---
------- ---------------------
1 a
100
2 b
200
3 c
300
SQL> select nvl(1,2), nvl(2,3),
nvl(4,3), nvl(5,4) from dual;
NVL(1,2) NVL(2,3) NVL(4,3)
NVL(5,4)
----------
---------- ----------
----------
1 2 4 5
SQL> select nvl(0,0), nvl(1,1),
nvl(null,null), nvl(4,4) from dual;
NVL(0,0)
NVL(1,1) NVL(null,null) NVL(4,4)
---------- ---------- ----------------- ----------
0 1 4
f) POWER
Power is the ability to
raise a value to a given exponent.
Syntax:
power (value, exponent)
Ex: SQL> select power(2,5), power(0,0), power(1,1), power(null,null), power(2,-5)
from dual;
POWER(2,5) POWER(0,0) POWER(1,1) POWER(NULL,NULL) POWER(2,-5)
-------------- -------------- -------------- ----------------------- ---------------
32 1 1 .03125
g) EXP:-
This will raise ‘e’ value to the give power.
Syntax: exp (value)
Ex: SQL> select exp(1), exp(2), exp(0),
exp(null), exp(-2) from dual;
EXP(1) EXP(2) EXP(0) EXP(NULL)
EXP(-2)
-------- --------- -------- ------------- ----------
2.71828183 7.3890561 1 .135335283
h) LN
This is based on natural or base ‘e’ logarithm.
Syntax:
ln (value) -- here value must be greater than zero which is positive only.
Ex: SQL> select ln(1), ln(2), ln(null)
from dual;
LN(1) LN(2)
LN(NULL)
------- -------
------------
0 .693147181
Ln and Exp are reciprocal to each
other.
EXP (3) = 20.0855369
LN (20.0855369) = 3
i) LOG This is based on 10 based logarithm.
Syntax: log (10, value) --
here value must be greater than zero which is positive only.
Ex: SQL> select log(10,100), log(10,2), log(10,1), log(10,null) from
dual;
LOG(10,100)
LOG(10,2) LOG(10,1)
LOG(10,NULL)
--------------- ----------- ------------ -----------------
2 .301029996 0
LN (value) = LOG
(EXP(1), value)
SQL> select ln(3), log(exp(1),3) from dual;
LN(3) LOG(EXP(1),3)
------- -----------------
1.09861229 1.09861229
j) CEIL: - This will produce a whole number
that is greater than or equal to the specified value.
Syntax: ceil (value)
Ex: SQL> select ceil(5),
ceil(5.1), ceil(-5), ceil( -5.1), ceil(0), ceil(null) from dual;
CEIL(5) CEIL(5.1)
CEIL(-5) CEIL(-5.1) CEIL(0)
CEIL(NULL)
--------- -----------
---------- ------------
-------- --------------
5 6
-5 -5
0
k) FLOOR:- This will produce a whole number
that is less than or equal to the specified value.
Syntax: floor (value)
Ex: SQL> select floor(5), floor(5.1),
floor(-5), floor( -5.1), floor(0), floor(null) from dual;
FLOOR(5)
FLOOR(5.1) FLOOR(-5) FLOOR(-5.1) FLOOR(0) FLOOR(NULL)
----------- ------------- ------------
--------------
-----------
----------------
5 5
-5 -6 0
l) ROUND :- This will rounds numbers to a given
number of digits of precision.
Syntax: round (value, precision)
Ex: SQL> select round(123.2345), round(123.2345,2), round(123.2354,2)
from dual;
ROUND(123.2345) ROUND(123.2345,0) ROUND(123.2345,2)
ROUND(123.2354,2)
--------------------- ------------------------ ----------------------- -----------------------
123 123 123.23 123.24
SQL> select round(123.2345,-1),
round(123.2345,-2), round(123.2345,-3),
round(123.2345,-4) from dual;
ROUND(123.2345,-1) ROUND(123.2345,-2) ROUND(123.2345,-3)
ROUND(123.2345,-4)
---------------------- ------------------------- ------------------------ ------------------------
120 100 0 0
SQL> select
round(123,0), round(123,1), round(123,2) from dual;
ROUND(123,0) ROUND(123,1) ROUND(123,2)
----------------- ----------------- ----------------
123 123 123
SQL>
select round(-123,0), round(-123,1), round(-123,2) from dual;
ROUND(-123,0) ROUND(-123,1) ROUND(-123,2)
------------------ ----------------- -------------------
-123 -123
-123
SQL> select
round(123,-1), round(123,-2), round(123,-3), round(-123,-1), round (-123,2),
round(-123,-3) from dual;
ROUND(123,-1) ROUND(123,-2)
ROUND(123,-3) ROUND(-123,-1) ROUND(-123,-2) ROUND(-123,-3)
------------- ------------- ------------- -------------- -------------- --------------
120
100
0 -120
-100
0
SQL> select round(null,null), round(0,0),
round(1,1), round(-1,-1), round(-2,-2) from
dual;
ROUND(NULL,NULL) ROUND(0,0) ROUND(1,1) ROUND(-1,-1) ROUND(-2,-2)
----------------------- -------------- -------------- ---------------- ----------------
0 1 0 0
m) TRUNC :- This will truncates or chops off digits of precision from
a number.
Syntax: trunc (value, precision)
Ex: SQL>
select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual;
TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2)
--------------------- ----------------------- -----------------------
123 123.23 123.23
SQL> select trunc(123.2345,-1),
trunc(123.2345,-2), trunc(123.2345,-3),
trunc(123.2345,-4) from dual;
TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3)
TRUNC(123.2345,-4)
------------------------
------------------------ ----------------------- ------------------------
120 100 0 0
SQL> select
trunc(123,0), trunc(123,1), trunc(123,2) from dual;
TRUNC(123,0) TRUNC(123,1)
TRUNC(123,2)
---------------- ---------------- -----------------
123 123 123
SQL> select
trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual;
TRUNC(-123,0) TRUNC(-123,1)
TRUNC(-123,2)
----------------- ----------------- -----------------
-123 -123 -123
SQL> select
trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(-123,2),
trunc(-123,-3) from dual;
TRUNC(123,-1) TRUNC(123,-2)
TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2) TRUNC(-123,-3)
------------- ------------- ------------- -------------- ------------- --------------
120
100
0 -120 -123
0
SQL> select
trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from dual;
TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1)
TRUNC(-2,-2)
-----------------------
------------- ------------- --------------- ----------------
0
1 0 0
n) BITAND
This will perform bitwise and operation.
Syntax: bitand (value1,
value2)
Ex: SQL>
select bitand(2,3), bitand(0,0), bitand(1,1), bitand(null,null), bitand(-2,-3)
from dual;
BITAND(2,3) BITAND(0,0) BITAND(1,1) BITAND(NULL,NULL) BITAND(-2,-3)
-------------- --------------- -------------- ------------------------ -----------------
2 0 1 -4
o) GREATEST:- This
will give the greatest number.
Syntax: greatest (value1,
value2, value3 … valuen)
Ex: SQL> select greatest(1, 2, 3), greatest(-1, -2, -3) from
dual;
GREATEST(1,2,3) GREATEST(-1,-2,-3)
-------------------- -----------------------
3 -1
Ø If all the values are zeros then it will display zero.
Ø If all the parameters are NULL’s then it will display
nothing.
Ø If any of the parameters is NULL it will display nothing.
p) LEAST:- This will give
the least number.
Syntax: least (value1, value2, value3 … valuen)
Ex: SQL> select least(1, 2, 3), least(-1, -2, -3) from dual;
LEAST(1,2,3) LEAST(-1,-2,-3)
-------------------- -----------------------
1 -3
Ø If all the
values are zeros then it will display zero.
Ø If all the
parameters are NULL’s then it will display nothing.
Ø If any of the
parameters is NULL it will display nothing.
q) COALESCE:- This will return first non-null value.
Syntax: coalesce (value1,
value2, value3 … valuen)
Ex: SQL>
select coalesce(1,2,3), coalesce(null,2,null,5) from dual;
COALESCE(1,2,3) COALESCE(NULL,2,NULL,5)
------------------- -------------------------------
1 2
II.
STRING FUNCTIONS
a)
Initcap
b)
Upper
c)
Lower
d)
Length
e)
Rpad
f)
Lpad
g)
Ltrim
h)
Rtrim
i)
Trim
j)
Translate
k)
Replace
l)
Soundex
m)
Concat ( ‘ || ‘
Concatenation operator)
n)
Ascii
o)
Chr
p)
Substr
q)
Instr
r)
Decode
s)
Greatest
t)
Least
u)
Coalesce
a) INITCAP
This will
capitalize the initial letter of the string.
Syntax: initcap (string)
Ex: SQL> select initcap('computer') from dual;
INITCAP
-----------
Computer
b) UPPER
This will
convert the string into uppercase.
Syntax: upper (string)
Ex:
SQL> select upper('computer')
from dual;
UPPER
-----------
COMPUTER
c) LOWER
This will
convert the string into lowercase.
Syntax: lower (string)
Ex:
SQL> select lower('COMPUTER') from dual;
LOWER
-----------
computer
d) LENGTH
This will give
length of the string.
Syntax: length (string)
Ex: SQL> select length('computer') from dual;
LENGTH
-----------
8
e) RPAD
This will allows
you to pad the right side of a column with any set of characters.
Syntax: rpad (string, length [, padding_char])
Ex:
SQL> select
rpad('computer',15,'*'), rpad('computer',15,'*#') from dual;
RPAD('COMPUTER'
RPAD('COMPUTER'
----------------------
----------------------
computer*******
computer*#*#*#*
-- Default padding character was blank space.
f) LPAD
This will allows
you to pad the left side of a column with any set of characters.
Syntax: lpad (string, length [, padding_char])
Ex: SQL> select
lpad('computer',15,'*'), lpad('computer',15,'*#') from dual;
LPAD('COMPUTER'
LPAD('COMPUTER'
---------------------
---------------------
*******computer
*#*#*#*computer
-- Default padding character was blank space.
g) LTRIM
This will trim
off unwanted characters from the left end of string.
Syntax: ltrim (string [,unwanted_chars])
Ex: SQL> select
ltrim('computer','co'), ltrim('computer','com') from dual;
LTRIM( LTRIM
-------- ---------
mputer puter
SQL> select ltrim('computer','puter'),
ltrim('computer','omputer') from dual;
LTRIM('C LTRIM('C
----------
----------
computer computer
-- If you haven’t specified
any unwanted characters it will display entire string.
h) RTRIM
This will trim
off unwanted characters from the right end of string.
Syntax: rtrim (string [, unwanted_chars])
Ex: SQL> select
rtrim('computer','er'), rtrim('computer','ter') from dual;
RTRIM( RTRIM
-------- ---------
comput compu
SQL> select
rtrim('computer','comput’), rtrim('computer','compute') from dual;
RTRIM('C RTRIM('C
----------
----------
computer computer
-- If you
haven’t specify any unwanted characters it will display entire string.
i) TRIM
This will trim
off unwanted characters from the both sides of string.
Syntax: trim (unwanted_chars from string)
Ex: SQL> select trim( 'i' from 'indiani')
from dual;
TRIM (
-----
ndian
SQL> select trim( leading'i' from 'indiani') from dual; -- this will work as LTRIM
TRIM (L
------
ndiani
SQL> select trim( trailing'i'
from 'indiani') from dual; --
this will work as RTRIM
TRIM(T
------
indian
j) TRANSLATE
This will
replace the set of characters, character by character.
Syntax: translate (string, old_chars, new_chars)
Ex: SQL> select translate ('india
TRANS
--------
xydxa
k) REPLACE
This will
replace the set of characters, string by string.
Syntax: replace (string, old_chars [, new_chars])
Ex: SQL> select replace('india ','in','xy'), replace(‘india
REPLACE REPLACE
-----------
-----------
Xydia dia
l) SOUNDEX
This will be used
to find words that sound like other words, exclusively used in where clause.
Syntax: soundex (string)
Ex:
SQL> select * from emp where
soundex(ename) = soundex('SMIT');
EMPNO ENAME JOB MGR HIREDATE SAL
DEPTNO
-------- --------
----- ----- ------------ --------- ----------
7369 SMITH
CLERK 7902 17-DEC-80 500 20
m) CONCAT
This will be used
to combine two strings only.
Syntax: concat (string1, string2)
Ex: SQL> select concat('computer',' operator') from dual;
CONCAT('COMPUTER'
-------------------------
computeroperator
If you want to combine more than two strings
you have to use concatenation operator (||).
SQL> select 'how' || ' are'
|| ' you' from dual;
'HOW'||'ARE
---------------
how are you
n) ASCII
This will return
the decimal representation in the database character set of the first
character of the
string.
Syntax: ASCII (string)
Ex: SQL> select ascii('a'),
ascii('apple'),ascii(‘AS’) from dual;
ASCII('A')
ASCII('APPLE') ASCII(‘AS’)
------------
------------------ ------------
97 97 65
o) CHR
This will return
the character having the binary equivalent to the string in either the
database
character set or the national character set.
Syntax: chr (number)
Ex: SQL> select chr(97) from
dual;
CHR
-----
a
p) SUBSTR
This will be
used to extract substrings.
Syntax: substr (string, start_chr_count [, no_of_chars])
Ex: SQL> select
substr('computer',2), substr('computer',2,5), substr('computer',3,7) from dual;
SUBSTR( SUBST SUBSTR
----------
------- --------
omputer omput mputer
Ø
If no_of_chars parameter is negative then it will display nothing.
Ø
If both parameters
except string are null or zeros then
it will display nothing.
Ø
If no_of_chars parameter is greater than the length of the string then
it ignores and calculates based on the orginal string length.
Ø
If start_chr_count is negative then it will extract the substring from
right end.
1 2 3 4 5 6 7 8
C O M P U T E R
-8 -7 -6 -5 -4 -3 -2 -1
q) INSTR
This will allows
you for searching through a string for set of characters.
Syntax: instr (string, search_str [, start_chr_count [, occurrence]
])
Ex:
SQL> select
instr('information','o',4,1), instr('information','o',4,2) from dual;
INSTR('INFORMATION','O',4,1) INSTR('INFORMATION','O',4,2)
------------------------------------ -------------------------------------
4 10
Ø If you are not specifying start_chr_count and occurrence
then it will start search from the beginning and finds first occurrence only.
Ø If both parameters start_chr_count
and occurrence are null, it will
display nothing.
r) DECODE
Decode will act
as value by value substitution.
For every value
of field, it will checks for a match in a series of if/then tests.
Syntax: decode (value, if1, then1, if2, then2, ……. else);
Ex:
SQL> select sal,
decode(sal,500,'Low',5000,'High','Medium') from emp;
SAL DECODE
-----
---------
500 Low
2500 Medium
2000 Medium
3500 Medium
3000 Medium
5000 High
4000 Medium
5000 High
1800 Medium
1200 Medium
2000 Medium
2700 Medium
2200 Medium
3200 Medium
14 rows selected.
SQL> select decode(1,1,3),
decode(1,2,3,4,4,6) from dual;
DECODE(1,1,3) DECODE(1,2,3,4,4,6)
-----------------
------------------------
3 6
Ø If the number of parameters are odd and different then
decode will display nothing.
Ø If the number of parameters are even and different then
decode will display last value.
Ø If all the parameters are null then decode will display
nothing.
Ø If all the parameters are zeros then decode will display
zero.
s) GREATEST
This will give
the greatest string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex:
SQL> select greatest('a', 'b',
'c'), greatest('satish','srinu','saketh') from dual;
GREAT GREAT
------- -------
c srinu
Ø If all the parameters are nulls then it will display
nothing.
Ø If any of the parameters is null it will display nothing.
t) LEAST
This will give
the least string.
Syntax: least (strng1, string2, string3 … stringn)
Ex:
SQL> select least('a', 'b', 'c'),
least('satish','srinu','saketh') from dual;
LEAST LEAST
------- -------
a saketh
Ø If all the parameters are nulls then it will display
nothing.
Ø If any of the parameters is null it will display nothing.
u) COALESCE
This will gives
the first non-null string.
Syntax: coalesce (strng1, string2, string3 … stringn)
Ex:
SQL> select
coalesce('a','b','c'), coalesce(null,'a',null,'b') from dual;
COALESCE COALESCE
-----------
-----------
a a
DATE FUNCTIONS :-
Ø
Sysdate
Ø
Current_date
Ø
Current_timestamp
Ø
Systimestamp
Ø
Localtimestamp
Ø
Dbtimezone
Ø
Sessiontimezone
Ø
To_char
Ø
To_date
Ø
Add_months
Ø
Months_between
Ø
Next_day
Ø
Last_day
Ø
Extract
Ø
Greatest
Ø
Least
Ø
Round
Ø
Trunc
Ø
New_time
Ø
Coalesce
Oracle default date format is DD-MON-YY.
We can change the default format to our desired format by
using the following command.
SQL> alter session set
nls_date_format = ‘DD-MONTH-YYYY’;
But this will expire once the session was closed.
a) SYSDATE
This will give
the current date and time.
Ex: SQL> select sysdate from dual;
SYSDATE
-----------
24-DEC-06
b) CURRENT_DATE
This will
returns the current date in the session’s timezone.
Ex: SQL> select current_date from dual;
CURRENT_DATE
------------------
24-DEC-06
c) CURRENT_TIMESTAMP
This will
returns the current timestamp with the active time zone information.
Ex: SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
-----------------------------------------------------
24-DEC-06 03.42.41.383369 AM +05:30
d) SYSTIMESTAMP
This will
returns the system date, including fractional seconds and time zone of the
database.
Ex: SQL> select systimestamp from dual;
SYSTIMESTAMP
-----------------------------------------------------
24-DEC-06 03.49.31.830099 AM +05:30
e) LOCALTIMESTAMP
This will
returns local timestamp in the active time zone information, with no time zone
information shown.
Ex: SQL> select localtimestamp
from dual;
LOCALTIMESTAMP
------------------------------------------------------
24-DEC-06 03.44.18.502874 AM
f) DBTIMEZONE
This will returns
the current database time zone in UTC format. (Coordinated Universal Time)
Ex: SQL> select dbtimezone from
dual;
DBTIMEZONE
---------------
-07:00
g) SESSIONTIMEZONE
This will returns the value of the current
session’s time zone.
Ex: SQL> select sessiontimezone
from dual;
SESSIONTIMEZONE
----------------------------
+05:30
h) TO_CHAR
This will be used
to extract various date formats.
The available
date formats as follows.
Syntax: to_char (date, format)
DATE FORMATS
D -- No of days in week
DD -- No
of days in month
DDD -- No
of days in year
MM -- No
of month
MON -- Three
letter abbreviation of month
MONTH -- Fully
spelled out month
RM -- Roman
numeral month
DY -- Three
letter abbreviated day
DAY -- Fully
spelled out day
Y -- Last
one digit of the year
YY -- Last
two digits of the year
YYY -- Last
three digits of the year
YYYY -- Full
four digit year
SYYYY -- Signed year
I -- One
digit year from ISO standard
IY -- Two
digit year from ISO standard
IYY -- Three
digit year from ISO standard
IYYY -- Four
digit year from ISO standard
Y, YYY -- Year with comma
YEAR -- Fully
spelled out year
CC -- Century
Q -- No
of quarters
W -- No
of weeks in month
WW -- No
of weeks in year
IW -- No
of weeks in year from ISO standard
HH -- Hours
MI -- Minutes
SS -- Seconds
FF -- Fractional
seconds
AM or PM -- Displays
AM or PM depending upon time of day
A.M or P.M -- Displays
A.M or P.M depending upon time of day
AD or BC -- Displays
AD or BC depending upon the date
A.D or B.C -- Displays
A.D or B.C depending upon the date
FM -- Prefix
to month or day, suppresses padding of month or day
TH -- Suffix
to a number
SP -- suffix
to a number to be spelled out
SPTH -- Suffix combination of TH and SP to be both spelled out
THSP -- same
as SPTH
http://madhudhareoracleapps.blogspot.com/2016/12/sql-tutorial.html