SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> select ename || ' 1 month salary = ' || salary from employdet;
ENAME||'1MONTHSALARY='||SALARY
--------------------------------------------------------------------
sam 1 month salary = 35000
hari 1 month salary = 33000
kiran 1 month salary = 45000
karthick 1 month salary = 44444
SQL> select ename || ' 1 month salary = ' || salary as "Monthly salary " from employdet;
Monthly salary
--------------------------------------------------------------------
sam 1 month salary = 35000
hari 1 month salary = 33000
kiran 1 month salary = 45000
karthick 1 month salary = 44444
SQL>
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> select * from employdet where ename like 'k%';
EID ENAME SALARY
---- ---------- ----------
e3 kiran 45000
e4 karthick 44444
SQL> select * from employdet where ename like '_a%';
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e4 karthick 44444
SQL> select * from employdet where ename like '__r%';
EID ENAME SALARY
---- ---------- ----------
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
select 3+5*2 from dual;
3+5*2
-----
13
SQL> select ename,salary,salary *1.4 "bonus" from employdet;
ENAME SALARY bonus
---------- ---------- ----------
sam 35000 49000
hari 33000 46200
kiran 45000 63000
karthick 44444 62221.6
select concat('Hello','World') combinestring from dual;
COMBINESTR
----------
HelloWorld
select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
create table cemp as select * from employdet where 0>1
MERGE INTO cemp c
USING employdet e
ON (c.eId = e.eId)
WHEN MATCHED THEN
UPDATE SET c.salary = e.salary,c.ename=e.ename
WHEN NOT MATCHED THEN
INSERT (c.eId,c.ename,c.salary)
VALUES(e.eId,e.ename,e.salary);
SQL> MERGE INTO cemp c
2 USING employdet e
3 ON (c.eId = e.eId)
4 WHEN MATCHED THEN
5 UPDATE SET c.salary = e.salary,c.ename
6 WHEN NOT MATCHED THEN
7 INSERT (c.eId,c.ename,c.salary)
8 VALUES(e.eId,e.ename,e.salary);
4 rows merged.
SQL> select * from cemp;
no rows selected
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> update employdet set salary = 55555 where eid='e2';
1 row updated.
SQL> update employdet set ename='praksh' where eid='e3';
1 row updated.
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 55555
e3 praksh 45000
e4 karthick 44444
SQL> SQL> select * from cemp;
EID ENAME SALARY
---- ---------- ----------
e2 hari 33000
e4 karthick 44444
e3 kiran 45000
e1 sam 35000
SQL> select * from cemp;
EID ENAME SALARY
---- ---------- ----------
e2 hari 55555
e4 karthick 44444
e3 praksh 45000
e1 sam 35000
SQL> select next_day('26-oct-09','wednesday') from dual;
NEXT_DAY(
---------
28-OCT-09
SQL> select next_day('26-oct-09','friday') from dual;
NEXT_DAY(
---------
30-OCT-09
create table edetails
(
eid char(4),
fname varchar(15),
lname varchar2(20),
hiredate date,
salary number(6),
job_id char(8),
deptid char(4),
commission_pct number(4,2),
dailywages number (4)
);
insert into edetails values('e1','sai','kiran','23-feb-09',
23000,'IT_PROG','D01',null,null);
insert into edetails values('e2','jai','krishnan','24-apr-09'
,27000,'ST_CLERK','D01',null,null);
insert into edetails values('e3','ashok','kumar','21-apr-09'
,27000,'SAEXE','D02',.10,null);
insert into edetails values('e4','Geethu','Mohan','14-apr-09',
27000,'SA_MAN','D02',.30,null);
insert into edetails values('e5','Arafat','Aboobacker',
'21-apr-09',27000,'SA_REP','D03',.20,null);
insert into edetails values('e6','Seetha','Raman',
'21-apr-09',24000,'SA_REP','D01',.20,null);
insert into edetails values('e7','Urmilla','Unni',
'21-apr-09',28000,'SA_REP','D01',.20,null);
insert into edetails values('e8','Rajesh','C',
'21-apr-09',25000,'SA_REP','D02',.20,null);
insert into edetails values('e9','Sajesh','Kumar',
'21-apr-09',29000,'SA_REP','D02',.20,null);
insert into edetails values('e10','Manu','Jayan',
'21-apr-09',29000,'SA_REP','D03',.20,null);
insert into edetails values('e11','Santhosh','Jayan',
'21-apr-09',22000,'SA_REP','D03',.20,null);
SQL> select * from edetails;
EID FNAME LNAME HIREDATE SALARY JOB_ID DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1 sai kiran 23-FEB-09 23000 IT_PROG D01
e2 jai krishnan 14-APR-09 27000 SY_ANA D01
e3 ashok kumar 14-APR-09 27000 SAEXE D02 .1
e4 Geethu Mohan 14-APR-09 27000 SA_MAN D02 .3
e5 Arafat Aboobacker 14-APR-09 27000 SA_REP D03 .2
SQL> select fname,salary , commission_pct from edetails;
FNAME SALARY COMMISSION_PCT
--------------- ---------- --------------
sai 23000
jai 27000
ashok 27000 .1
Geethu 27000 .3
Arafat 27000 .2
SQL> select fname ,salary ,
nvl(to_char(commission_pct),' no commisn')
commsiondet from edetails;
FNAME SALARY COMMSIONDET
--------------- ---------- ----------------------------------------
sai 23000 no commisn
jai 27000 no commisn
ashok 27000 .1
Geethu 27000 .3
Arafat 27000 .2
select fname ,salary ,
nvl(commission_pct,0) commsiondet from edetails;
FNAME SALARY COMMSIONDET
--------------- ---------- -----------
sai 23000 0
jai 27000 0
ashok 27000 .1
Geethu 27000 .3
Arafat 27000 .2
SQL> select lname,salary,commission_pct,
nvl2(commission_pct,salary+salary*commission_pct,salary)
income from edetails;
LNAME SALARY COMMISSION_PCT INCOME
-------------------- ---------- -------------- ----------
kiran 23000 23000
krishnan 27000 27000
kumar 27000 .1 29700
Mohan 27000 .3 35100
Aboobacker 27000 .2 32400
SQL> select lname ,job_id from edetails
2 ;
LNAME JOB_ID
-------------------- --------
kiran IT_PROG
krishnan SY_ANA
kumar SAEXE
Mohan SA_MAN
Aboobacker SA_REP
SQL> select lname , job_id from edetails
where job_id like 'SA_%';
LNAME JOB_ID
-------------------- --------
kumar SAEXE
Mohan SA_MAN
Aboobacker SA_REP
SQL> select lname , job_id from edetails
where job_id like 'SA\_%' escape '\';
LNAME JOB_ID
-------------------- --------
Mohan SA_MAN
Aboobacker SA_REP
SQL> select lname , job_id from edetails
where job_id like 'SA$_%' escape '$';
LNAME JOB_ID
-------------------- --------
Mohan SA_MAN
Aboobacker SA_REP
select fname,lname , length(fname) "exp1" ,
length(lname) "exp2" from edetails;
FNAME LNAME exp1 exp2
--------------- -------------------- ---------- ----------
sai kiran 3 5
jai krishnan 3 8
ashok kumar 5 5
Geethu Mohan 6 5
Arafat Aboobacker 6 10
// nullif compares two expressions and returns null
if both expressions are equal other wise returns
first expression
select fname,lname , length(fname) "exp1" ,
length(lname) "exp2",
nullif(length(fname),length(lname)) "result" from edetails;
FNAME LNAME exp1 exp2 result
--------------- -------------------- ---------- ---------- ----------
sai kiran 3 5 3
jai krishnan 3 8 3
ashok kumar 5 5
Geethu Mohan 6 5 6
Arafat Aboobacker 6 10 6
case expression
select lname,job_id,salary,
case job_id when 'IT_PROG' then 1.20*salary
when 'ST_CLERK' then 1.15*salary
when 'SA_REP' then 1.10*salary
else
salary
end
revisedsalary
from edetails;
LNAME JOB_ID SALARY REVISEDSALARY
-------------------- -------- ---------- -------------
kiran IT_PROG 23000 27600
krishnan ST_CLERK 27000 31050
kumar SAEXE 27000 27000
Mohan SA_MAN 27000 27000
Aboobacker SA_REP 27000 29700
SQL> select fname,hiredate,
to_char(hiredate,'mm/yy') mmyy from edetails;
FNAME HIREDATE MMYY
--------------- --------- -----
sai 23-FEB-09 02/09
jai 14-APR-09 04/09
ashok 14-APR-09 04/09
Geethu 14-APR-09 04/09
Arafat 14-APR-09 04/09
SQL> select fname,hiredate,
to_char(hiredate,'mon/yyyy') monthhired
from edetails;
FNAME HIREDATE MONTHHIR
--------------- --------- --------
sai 23-FEB-09 feb/2009
jai 14-APR-09 apr/2009
ashok 14-APR-09 apr/2009
Geethu 14-APR-09 apr/2009
Arafat 14-APR-09 apr/2009
select fname,hiredate,
to_char(hiredate,'mon/year') year from edetails;
FNAME HIREDATE YEAR
--------------- --------- ----------------------------------------------
sai 23-FEB-09 feb/two thousand nine
jai 14-APR-09 apr/two thousand nine
ashok 14-APR-09 apr/two thousand nine
Geethu 14-APR-09 apr/two thousand nine
Arafat 14-APR-09 apr/two thousand nine
select fname,hiredate,
to_char(hiredate,'DD Month YYYY')
as hireddate from edetails;
FNAME HIREDATE HIREDDATE
--------------- --------- -----------------
sai 23-FEB-09 23 February 2009
jai 14-APR-09 14 April 2009
ashok 14-APR-09 14 April 2009
Geethu 14-APR-09 14 April 2009
Arafat 14-APR-09 14 April 2009
select fname,hiredate,
to_char(hiredate,'fmDD Month YYYY')
as hireddate from edetails;
FNAME HIREDATE HIREDDATE
--------------- --------- -----------------
sai 23-FEB-09 23 February 2009
jai 14-APR-09 14 April 2009
ashok 14-APR-09 14 April 2009
Geethu 14-APR-09 14 April 2009
Arafat 14-APR-09 14 April 2009
select lname,hiredate,
to_char(hiredate,'fmDdspth "of" Month yyyy') hiredate from
edetails;
LNAME HIREDATE HIREDATE
-------------------- --------- -------------------------------
kiran 23-FEB-09 Twenty-Third of February 2009
krishnan 14-APR-09 Fourteenth of April 2009
kumar 14-APR-09 Fourteenth of April 2009
Mohan 14-APR-09 Fourteenth of April 2009
Aboobacker 14-APR-09 Fourteenth of April 2009
select lname,hiredate,
to_char(hiredate,'fmDdth "of" Month yyyy') hiredate from
edetails;
LNAME HIREDATE HIREDATE
-------------------- --------- ----------------------
kiran 23-FEB-09 23rd of February 2009
krishnan 14-APR-09 14th of April 2009
kumar 14-APR-09 14th of April 2009
Mohan 14-APR-09 14th of April 2009
Aboobacker 14-APR-09 14th of April 2009
create table timeexample1
(loanduration interval year(3) to month);
insert into timeexample1 values(interval '120' month (3));
1 row created.
SQL> select * from timeexample1;
LOANDURATION
-------------------------------------------------------------------------
+010-00
SQL> insert into timeexample1 values(interval '155' month(3));
1 row created.
SQL> select * from timeexample1;
LOANDURATION
-------------------------------------------------------------------------
+010-00
+012-11
insert into timeexample1 values(interval '5-6' year to month);
insert into timeexample1 values(interval '13-11' year to month);
SQL> select * from timeexample1;
LOANDURATION
---------------------------------------------------------------------------
+010-00
+012-11
+005-06
+013-11
7 rows selected.
SQL> select to_char(sysdate+loanduration,'dd-mon-yyyy') from timeexample1;
TO_CHAR(SYS
-----------
26-oct-2019
26-sep-2022
26-apr-2015
26-sep-2023
7 rows selected.
SQL> create table timeexample2
2 (
3 dayduration interval day(3) to second);
Table created.
SQL> insert into timeexample2 values(interval '184' day(3));
1 row created.
SQL> select * from timeexample2;
DAYDURATION
----------------------------------------------------------------------
+184 00:00:00.000000
SQL> select sysdate +dayduration "Half year" from timeexample2;
Half year
---------
28-APR-10
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> select ename || ' 1 month salary = ' || salary from employdet;
ENAME||'1MONTHSALARY='||SALARY
--------------------------------------------------------------------
sam 1 month salary = 35000
hari 1 month salary = 33000
kiran 1 month salary = 45000
karthick 1 month salary = 44444
SQL> select ename || ' 1 month salary = ' || salary as "Monthly salary " from employdet;
Monthly salary
--------------------------------------------------------------------
sam 1 month salary = 35000
hari 1 month salary = 33000
kiran 1 month salary = 45000
karthick 1 month salary = 44444
SQL>
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> select * from employdet where ename like 'k%';
EID ENAME SALARY
---- ---------- ----------
e3 kiran 45000
e4 karthick 44444
SQL> select * from employdet where ename like '_a%';
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e4 karthick 44444
SQL> select * from employdet where ename like '__r%';
EID ENAME SALARY
---- ---------- ----------
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
select 3+5*2 from dual;
3+5*2
-----
13
SQL> select ename,salary,salary *1.4 "bonus" from employdet;
ENAME SALARY bonus
---------- ---------- ----------
sam 35000 49000
hari 33000 46200
kiran 45000 63000
karthick 44444 62221.6
select concat('Hello','World') combinestring from dual;
COMBINESTR
----------
HelloWorld
select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
create table cemp as select * from employdet where 0>1
MERGE INTO cemp c
USING employdet e
ON (c.eId = e.eId)
WHEN MATCHED THEN
UPDATE SET c.salary = e.salary,c.ename=e.ename
WHEN NOT MATCHED THEN
INSERT (c.eId,c.ename,c.salary)
VALUES(e.eId,e.ename,e.salary);
SQL> MERGE INTO cemp c
2 USING employdet e
3 ON (c.eId = e.eId)
4 WHEN MATCHED THEN
5 UPDATE SET c.salary = e.salary,c.ename
6 WHEN NOT MATCHED THEN
7 INSERT (c.eId,c.ename,c.salary)
8 VALUES(e.eId,e.ename,e.salary);
4 rows merged.
SQL> select * from cemp;
no rows selected
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 33000
e3 kiran 45000
e4 karthick 44444
SQL> update employdet set salary = 55555 where eid='e2';
1 row updated.
SQL> update employdet set ename='praksh' where eid='e3';
1 row updated.
SQL> select * from employdet;
EID ENAME SALARY
---- ---------- ----------
e1 sam 35000
e2 hari 55555
e3 praksh 45000
e4 karthick 44444
SQL> SQL> select * from cemp;
EID ENAME SALARY
---- ---------- ----------
e2 hari 33000
e4 karthick 44444
e3 kiran 45000
e1 sam 35000
SQL> select * from cemp;
EID ENAME SALARY
---- ---------- ----------
e2 hari 55555
e4 karthick 44444
e3 praksh 45000
e1 sam 35000
SQL> select next_day('26-oct-09','wednesday') from dual;
NEXT_DAY(
---------
28-OCT-09
SQL> select next_day('26-oct-09','friday') from dual;
NEXT_DAY(
---------
30-OCT-09
create table edetails
(
eid char(4),
fname varchar(15),
lname varchar2(20),
hiredate date,
salary number(6),
job_id char(8),
deptid char(4),
commission_pct number(4,2),
dailywages number (4)
);
insert into edetails values('e1','sai','kiran','23-feb-09',
23000,'IT_PROG','D01',null,null);
insert into edetails values('e2','jai','krishnan','24-apr-09'
,27000,'ST_CLERK','D01',null,null);
insert into edetails values('e3','ashok','kumar','21-apr-09'
,27000,'SAEXE','D02',.10,null);
insert into edetails values('e4','Geethu','Mohan','14-apr-09',
27000,'SA_MAN','D02',.30,null);
insert into edetails values('e5','Arafat','Aboobacker',
'21-apr-09',27000,'SA_REP','D03',.20,null);
insert into edetails values('e6','Seetha','Raman',
'21-apr-09',24000,'SA_REP','D01',.20,null);
insert into edetails values('e7','Urmilla','Unni',
'21-apr-09',28000,'SA_REP','D01',.20,null);
insert into edetails values('e8','Rajesh','C',
'21-apr-09',25000,'SA_REP','D02',.20,null);
insert into edetails values('e9','Sajesh','Kumar',
'21-apr-09',29000,'SA_REP','D02',.20,null);
insert into edetails values('e10','Manu','Jayan',
'21-apr-09',29000,'SA_REP','D03',.20,null);
insert into edetails values('e11','Santhosh','Jayan',
'21-apr-09',22000,'SA_REP','D03',.20,null);
SQL> select * from edetails;
EID FNAME LNAME HIREDATE SALARY JOB_ID DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1 sai kiran 23-FEB-09 23000 IT_PROG D01
e2 jai krishnan 14-APR-09 27000 SY_ANA D01
e3 ashok kumar 14-APR-09 27000 SAEXE D02 .1
e4 Geethu Mohan 14-APR-09 27000 SA_MAN D02 .3
e5 Arafat Aboobacker 14-APR-09 27000 SA_REP D03 .2
SQL> select fname,salary , commission_pct from edetails;
FNAME SALARY COMMISSION_PCT
--------------- ---------- --------------
sai 23000
jai 27000
ashok 27000 .1
Geethu 27000 .3
Arafat 27000 .2
SQL> select fname ,salary ,
nvl(to_char(commission_pct),' no commisn')
commsiondet from edetails;
FNAME SALARY COMMSIONDET
--------------- ---------- ----------------------------------------
sai 23000 no commisn
jai 27000 no commisn
ashok 27000 .1
Geethu 27000 .3
Arafat 27000 .2
select fname ,salary ,
nvl(commission_pct,0) commsiondet from edetails;
FNAME SALARY COMMSIONDET
--------------- ---------- -----------
sai 23000 0
jai 27000 0
ashok 27000 .1
Geethu 27000 .3
Arafat 27000 .2
SQL> select lname,salary,commission_pct,
nvl2(commission_pct,salary+salary*commission_pct,salary)
income from edetails;
LNAME SALARY COMMISSION_PCT INCOME
-------------------- ---------- -------------- ----------
kiran 23000 23000
krishnan 27000 27000
kumar 27000 .1 29700
Mohan 27000 .3 35100
Aboobacker 27000 .2 32400
SQL> select lname ,job_id from edetails
2 ;
LNAME JOB_ID
-------------------- --------
kiran IT_PROG
krishnan SY_ANA
kumar SAEXE
Mohan SA_MAN
Aboobacker SA_REP
SQL> select lname , job_id from edetails
where job_id like 'SA_%';
LNAME JOB_ID
-------------------- --------
kumar SAEXE
Mohan SA_MAN
Aboobacker SA_REP
SQL> select lname , job_id from edetails
where job_id like 'SA\_%' escape '\';
LNAME JOB_ID
-------------------- --------
Mohan SA_MAN
Aboobacker SA_REP
SQL> select lname , job_id from edetails
where job_id like 'SA$_%' escape '$';
LNAME JOB_ID
-------------------- --------
Mohan SA_MAN
Aboobacker SA_REP
select fname,lname , length(fname) "exp1" ,
length(lname) "exp2" from edetails;
FNAME LNAME exp1 exp2
--------------- -------------------- ---------- ----------
sai kiran 3 5
jai krishnan 3 8
ashok kumar 5 5
Geethu Mohan 6 5
Arafat Aboobacker 6 10
// nullif compares two expressions and returns null
if both expressions are equal other wise returns
first expression
select fname,lname , length(fname) "exp1" ,
length(lname) "exp2",
nullif(length(fname),length(lname)) "result" from edetails;
FNAME LNAME exp1 exp2 result
--------------- -------------------- ---------- ---------- ----------
sai kiran 3 5 3
jai krishnan 3 8 3
ashok kumar 5 5
Geethu Mohan 6 5 6
Arafat Aboobacker 6 10 6
case expression
select lname,job_id,salary,
case job_id when 'IT_PROG' then 1.20*salary
when 'ST_CLERK' then 1.15*salary
when 'SA_REP' then 1.10*salary
else
salary
end
revisedsalary
from edetails;
LNAME JOB_ID SALARY REVISEDSALARY
-------------------- -------- ---------- -------------
kiran IT_PROG 23000 27600
krishnan ST_CLERK 27000 31050
kumar SAEXE 27000 27000
Mohan SA_MAN 27000 27000
Aboobacker SA_REP 27000 29700
SQL> select fname,hiredate,
to_char(hiredate,'mm/yy') mmyy from edetails;
FNAME HIREDATE MMYY
--------------- --------- -----
sai 23-FEB-09 02/09
jai 14-APR-09 04/09
ashok 14-APR-09 04/09
Geethu 14-APR-09 04/09
Arafat 14-APR-09 04/09
SQL> select fname,hiredate,
to_char(hiredate,'mon/yyyy') monthhired
from edetails;
FNAME HIREDATE MONTHHIR
--------------- --------- --------
sai 23-FEB-09 feb/2009
jai 14-APR-09 apr/2009
ashok 14-APR-09 apr/2009
Geethu 14-APR-09 apr/2009
Arafat 14-APR-09 apr/2009
select fname,hiredate,
to_char(hiredate,'mon/year') year from edetails;
FNAME HIREDATE YEAR
--------------- --------- ----------------------------------------------
sai 23-FEB-09 feb/two thousand nine
jai 14-APR-09 apr/two thousand nine
ashok 14-APR-09 apr/two thousand nine
Geethu 14-APR-09 apr/two thousand nine
Arafat 14-APR-09 apr/two thousand nine
select fname,hiredate,
to_char(hiredate,'DD Month YYYY')
as hireddate from edetails;
FNAME HIREDATE HIREDDATE
--------------- --------- -----------------
sai 23-FEB-09 23 February 2009
jai 14-APR-09 14 April 2009
ashok 14-APR-09 14 April 2009
Geethu 14-APR-09 14 April 2009
Arafat 14-APR-09 14 April 2009
select fname,hiredate,
to_char(hiredate,'fmDD Month YYYY')
as hireddate from edetails;
FNAME HIREDATE HIREDDATE
--------------- --------- -----------------
sai 23-FEB-09 23 February 2009
jai 14-APR-09 14 April 2009
ashok 14-APR-09 14 April 2009
Geethu 14-APR-09 14 April 2009
Arafat 14-APR-09 14 April 2009
select lname,hiredate,
to_char(hiredate,'fmDdspth "of" Month yyyy') hiredate from
edetails;
LNAME HIREDATE HIREDATE
-------------------- --------- -------------------------------
kiran 23-FEB-09 Twenty-Third of February 2009
krishnan 14-APR-09 Fourteenth of April 2009
kumar 14-APR-09 Fourteenth of April 2009
Mohan 14-APR-09 Fourteenth of April 2009
Aboobacker 14-APR-09 Fourteenth of April 2009
select lname,hiredate,
to_char(hiredate,'fmDdth "of" Month yyyy') hiredate from
edetails;
LNAME HIREDATE HIREDATE
-------------------- --------- ----------------------
kiran 23-FEB-09 23rd of February 2009
krishnan 14-APR-09 14th of April 2009
kumar 14-APR-09 14th of April 2009
Mohan 14-APR-09 14th of April 2009
Aboobacker 14-APR-09 14th of April 2009
create table timeexample1
(loanduration interval year(3) to month);
insert into timeexample1 values(interval '120' month (3));
1 row created.
SQL> select * from timeexample1;
LOANDURATION
-------------------------------------------------------------------------
+010-00
SQL> insert into timeexample1 values(interval '155' month(3));
1 row created.
SQL> select * from timeexample1;
LOANDURATION
-------------------------------------------------------------------------
+010-00
+012-11
insert into timeexample1 values(interval '5-6' year to month);
insert into timeexample1 values(interval '13-11' year to month);
SQL> select * from timeexample1;
LOANDURATION
---------------------------------------------------------------------------
+010-00
+012-11
+005-06
+013-11
7 rows selected.
SQL> select to_char(sysdate+loanduration,'dd-mon-yyyy') from timeexample1;
TO_CHAR(SYS
-----------
26-oct-2019
26-sep-2022
26-apr-2015
26-sep-2023
7 rows selected.
SQL> create table timeexample2
2 (
3 dayduration interval day(3) to second);
Table created.
SQL> insert into timeexample2 values(interval '184' day(3));
1 row created.
SQL> select * from timeexample2;
DAYDURATION
----------------------------------------------------------------------
+184 00:00:00.000000
SQL> select sysdate +dayduration "Half year" from timeexample2;
Half year
---------
28-APR-10
No comments:
Post a Comment
Thanks for your valuable comments