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 avoid to check 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
multi tables with specified fields, with duplicate rows, with conditions,
with first
and else clauses.
Functions can be categorized as
follows.
1 Single row functions
2 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.
1 Numeric functions
2 String functions
3 Date functions
4 Miscellaneous functions
5 Conversion functions
NUMERIC
FUNCTIONS
1 Abs
2 Sign
3 Sqrt
4 Mod
5 Nvl
6 Power
7 Exp
8 Ln
9 Log
10 Ceil
11 Floor
12 Round
13 Trunk
14 Bitand
15 Greatest
16 Least
17 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)
--------------- -----------
------------ -----------------
No comments:
Post a Comment