URDINESH: ORACLE

Showing posts with label ORACLE. Show all posts

Tuesday, May 27, 2014

Oracle Queries With Example 5 SubQueries

select lname,salary ,commission_pct ,coalesce(commission_pct,salary,dailywages) income from edetails;

FNAME LNAME SALARY INCOME
--------------- -------------------- ---------- ----------
sai kiran 23000 23000
jai krishnan 27000 27000
ashok kumar 27000 .1
Geethu Mohan 27000 .3
Arafat Aboobacker 27000 .2
Seetha Raman 24000 .2
Urmilla Unni 28000 .2
Sajesh Kumar 29000 .2
Rajesh C 25000 .2
Manu Jayan 29000 .2
Santhosh Jayan 22000 .2

FNAME LNAME SALARY INCOME
--------------- -------------------- ---------- ----------
hari prasad 500

12 rows selected.

select lname,salary ,commission_pct ,dailywages,
coalesce(commission_pct,salary,dailywages) income from
edetails;

LNAME SALARY COMMISSION_PCT INCOME
-------------------- ---------- -------------- ----------
kiran 23000 23000
krishnan 27000 27000
kumar 27000 .1 .1
Mohan 27000 .3 .3
Aboobacker 27000 .2 .2
Raman 24000 .2 .2
Unni 28000 .2 .2
Kumar 29000 .2 .2
C 25000 .2 .2
Jayan 29000 .2 .2
Jayan 22000 .2 .2

LNAME SALARY COMMISSION_PCT INCOME
-------------------- ---------- -------------- ----------
prasad 500

12 rows selected.

SQL> select lname,salary ,commission_pct ,dailywages,
2 coalesce(commission_pct,salary,dailywages) income from
3 edetails;

LNAME SALARY COMMISSION_PCT DAILYWAGES INCOME
-------------------- ---------- -------------- ---------- ----------
kiran 23000 23000
krishnan 27000 27000
kumar 27000 .1 .1
Mohan 27000 .3 .3
Aboobacker 27000 .2 .2
Raman 24000 .2 .2
Unni 28000 .2 .2
Kumar 29000 .2 .2
C 25000 .2 .2
Jayan 29000 .2 .2
Jayan 22000 .2 .2

LNAME SALARY COMMISSION_PCT DAILYWAGES INCOME
-------------------- ---------- -------------- ---------- ----------
prasad 500 500

12 rows selected.

select fname ,salary from edetails e where salary >= (select avg(salary) from edetails ed where
ed.deptid=e.deptid);

FNAME SALARY
--------------- ----------
jai 27000
ashok 27000
Geethu 27000
Arafat 27000
Urmilla 28000
Sajesh 29000
Manu 29000

select fname , salary from edetails
where salary >any(25000,28000);

FNAME SALARY
--------------- ----------
jai 27000
ashok 27000
Geethu 27000
Arafat 27000
Urmilla 28000
Sajesh 29000
Manu 29000

select fname , salary from edetails
where salary >all(25000,28000);

FNAME SALARY
--------------- ----------
Sajesh 29000
Manu 29000

select fname , salary from edetails
where salary <all(25000,28000);

FNAME SALARY
--------------- ----------
sai 23000
Seetha 24000
Santhosh 22000

select fname , salary from edetails
where salary <any(25000,28000);

FNAME SALARY
--------------- ----------
sai 23000
jai 27000
ashok 27000
Geethu 27000
Arafat 27000
Seetha 24000
Rajesh 25000
Santhosh 22000

8 rows selected.

select fname , salary from edetails
where salary >some(25000,28000);

FNAME SALARY
--------------- ----------
jai 27000
ashok 27000
Geethu 27000
Arafat 27000
Urmilla 28000
Sajesh 29000
Manu 29000

select fname , salary from edetails
where salary in(25000,28000);

FNAME SALARY
--------------- ----------
Urmilla 28000
Rajesh 25000

Oracle Queries With Example 3

select round('16-may-07','mon') from dual;

select round(to_date('16-may-07'),'yy')) from dual;

select round(to_date('16-may-07'),'yy')
from dual;

ROUND(TO_
---------
01-JAN-07

select round(to_date('16-jun-07'),'yy')
from dual;

select round(to_date('16-jul-07'),'yy')
from dual;

select round(to_date('15-jul-07'),'mon')
from dual;

select trunc(to_date('16-jul-09'),'yy')
from dual;

SQL> select sysdate ,
to_char(sysdate,'dd month year') from dual;

SYSDATE TO_CHAR(SYSDATE,'DDMONTHYEAR')
--------- -------------------------------------------------------
29-OCT-09 29 october two thousand nine

select sysdate ,
to_char(sysdate,'day') from dual;

SQL> select sysdate ,
2 to_char(sysdate,'day') from dual;

SYSDATE TO
--------- --
29-OCT-09 44

select to_char(to_date('05-may-05'),'Q') from dual;

select to_char(sysdate,'dd-mon-yy HH24:MI:SS ')
from dual;

T
-
2

select to_char(sysdate,'dd-mon-yy HH:MI:SS AM')
from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
29-oct-09 03:01:44 PM

select to_char(sysdate,'dd-mon-yy HH24:MI:SS ')
from dual;

TO_CHAR(SYSDATE,'DD
-------------------
29-oct-09 15:02:20

select lname,salary ,commission_pct ,coalesce(commission_pct,salary,dailywages) income from edetails;

FNAME LNAME SALARY INCOME
--------------- -------------------- ---------- ----------
sai kiran 23000 23000
jai krishnan 27000 27000
ashok kumar 27000 .1
Geethu Mohan 27000 .3
Arafat Aboobacker 27000 .2
Seetha Raman 24000 .2
Urmilla Unni 28000 .2
Sajesh Kumar 29000 .2
Rajesh C 25000 .2
Manu Jayan 29000 .2
Santhosh Jayan 22000 .2

FNAME LNAME SALARY INCOME
--------------- -------------------- ---------- ----------
hari prasad 500

12 rows selected.

select lname,salary ,commission_pct ,dailywages,
coalesce(commission_pct,salary,dailywages) income from
edetails;

LNAME SALARY COMMISSION_PCT INCOME
-------------------- ---------- -------------- ----------
kiran 23000 23000
krishnan 27000 27000
kumar 27000 .1 .1
Mohan 27000 .3 .3
Aboobacker 27000 .2 .2
Raman 24000 .2 .2
Unni 28000 .2 .2
Kumar 29000 .2 .2
C 25000 .2 .2
Jayan 29000 .2 .2
Jayan 22000 .2 .2

LNAME SALARY COMMISSION_PCT INCOME
-------------------- ---------- -------------- ----------
prasad 500

12 rows selected.

SQL> select lname,salary ,commission_pct ,dailywages,
2 coalesce(commission_pct,salary,dailywages) income from
3 edetails;

LNAME SALARY COMMISSION_PCT DAILYWAGES INCOME
-------------------- ---------- -------------- ---------- ----------
kiran 23000 23000
krishnan 27000 27000
kumar 27000 .1 .1
Mohan 27000 .3 .3
Aboobacker 27000 .2 .2
Raman 24000 .2 .2
Unni 28000 .2 .2
Kumar 29000 .2 .2
C 25000 .2 .2
Jayan 29000 .2 .2
Jayan 22000 .2 .2

LNAME SALARY COMMISSION_PCT DAILYWAGES INCOME
-------------------- ---------- -------------- ---------- ----------
prasad 500 500

12 rows selected.

select deptid,salary from edetails;

DEPT SALARY
---- ----------
D01 23000
D01 27000
D02 27000
D02 27000
D03 27000
D01 24000
D01 28000
D02 29000
D02 25000
D03 29000
D03 22000

select deptid,sum(salary) "departwise total" from edetails group by deptid;

select deptid,sum(salary) "departwise total" from edetails
group by deptid;

DEPT departwise total
---- ----------------

D01 102000
D03 78000
D02 108000

select deptid,sum(salary) "departwise total" from edetails
group by deptid
having sum(salary) > 100000;

SQL> select rownum,eid, fname from edetails;

ROWNUM EID FNAME
---------- ---- ---------------
1 e1 sai
2 e2 jai
3 e3 ashok
4 e4 Geethu
5 e5 Arafat
6 e6 Seetha
7 e7 Urmilla
8 e9 Sajesh
9 e8 Rajesh
10 e10 Manu
11 e11 Santhosh

ROWNUM EID FNAME
---------- ---- ---------------
12 e12 hari

12 rows selected.

SQL> select * from edetails where rownum < 8;

EID FNAME LNAME HIREDATE SALARY JOB_ID DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1 sai kiran 23-FEB-09 23000 IT_PROG D01
e2 jai krishnan 24-APR-09 27000 ST_CLERK D01
e3 ashok kumar 21-APR-09 27000 SAEXE D02 .1
e4 Geethu Mohan 14-APR-09 27000 SA_MAN D02 .3
e5 Arafat Aboobacker 21-APR-09 27000 SA_REP D03 .2
e6 Seetha Raman 21-APR-09 24000 SA_REP D01 .2
e7 Urmilla Unni 21-APR-09 28000 SA_REP D01 .2

select rowid,rownum,eid,fname from edetails;

ROWID ROWNUM EID FNAME
------------------ ---------- ---- ---------------
AAAM5RAABAAAPCaAAA 1 e1 sai
AAAM5RAABAAAPCaAAB 2 e2 jai
AAAM5RAABAAAPCaAAC 3 e3 ashok
AAAM5RAABAAAPCaAAD 4 e4 Geethu
AAAM5RAABAAAPCaAAE 5 e5 Arafat
AAAM5RAABAAAPCaAAF 6 e6 Seetha
AAAM5RAABAAAPCaAAG 7 e7 Urmilla
AAAM5RAABAAAPCaAAH 8 e9 Sajesh
AAAM5RAABAAAPCaAAI 9 e8 Rajesh
AAAM5RAABAAAPCaAAJ 10 e10 Manu
AAAM5RAABAAAPCaAAK 11 e11 Santhosh

ROWID ROWNUM EID FNAME
------------------ ---------- ---- ---------------
AAAM5RAABAAAPCaAAL 12 e12 hari

select sysdate , sysdate +4,sysdate -10 from dual;

SYSDATE SYSDATE+4 SYSDATE-1
--------- --------- ---------
29-OCT-09 02-NOV-09 19-OCT-09

Oracle Queries With Example 2

create table transactiondemo
(
col1 number(2),
col2 char(4)
);

insert into transactiondemo values
(1,'a');

insert into transactiondemo values
(2,'b');

select * from transactiondemo;

COL1 COL2
---------- ----
1 a
2 b

rollback;

Rollback complete.

select * from transactiondemo;
no rows selected

insert into transactiondemo values
(1,'a');

SQL> savepoint save1;

Savepoint created.

insert into transactiondemo values
(2,'b');

SQL> select * from transactiondemo;

COL1 COL2
---------- ----
1 a
2 b

rollback to save1;

select * from transactiondemo;

COL1 COL2
---------- ----
1 a

SQL> rename transactiondemo to tdemo;

Table renamed.

SQL> select * from tdemo;

COL1 COL2
---------- ----
1 a

SQL> select * from transactiondemo;
select * from transactiondemo
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from cedetails;

EID FNAME LNAME HIREDATE SALARY JOB_ID DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1 sai kiran 23-FEB-09 23000 IT_PROG D01
e2 jai krishnan 24-APR-09 27000 ST_CLERK D01
e3 ashok kumar 21-APR-09 27000 SAEXE D02 .1
e4 Geethu Mohan 14-APR-09 27000 SA_MAN D02 .3
e5 Arafat Aboobacker 21-APR-09 27000 SA_REP D03 .2

SQL> alter table cedetails set unused (hiredate);

Table altered.

SQL> select * from cedetails;

EID FNAME LNAME SALARY JOB_ID DEPT COMMISSION_PCT DAILYWAGES
---- --------------- -------------------- ---------- -------- ---- -------------- ----------
e1 sai kiran 23000 IT_PROG D01
e2 jai krishnan 27000 ST_CLERK D01
e3 ashok kumar 27000 SAEXE D02 .1
e4 Geethu Mohan 27000 SA_MAN D02 .3
e5 Arafat Aboobacker 27000 SA_REP D03 .2

SQL> alter table cedetails set unused (job_id);

Table altered.

SQL> select * from cedetails;

EID FNAME LNAME SALARY DEPT COMMISSION_PCT DAILYWAGES
---- --------------- -------------------- ---------- ---- -------------- ----------
e1 sai kiran 23000 D01
e2 jai krishnan 27000 D01
e3 ashok kumar 27000 D02 .1
e4 Geethu Mohan 27000 D02 .3
e5 Arafat Aboobacker 27000 D03 .2

SQL> describe cedetails;
Name
---------------------------------------------------------------------------------------------------
EID
FNAME
LNAME
SALARY
DEPTID
COMMISSION_PCT
DAILYWAGES

SQL>

SQL> create table copyedetails as
select * from edetails where 0 >1;

Table created.

SQL> select * from copyedetails;

no rows selected

insert into copyedetails select * from edetails;

SQL> insert into copyedetails select * from edetails;

5 rows created.

SQL> select * from copyedetails;

EID FNAME LNAME HIREDATE SALARY JOB_ID DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1 sai kiran 23-FEB-09 23000 IT_PROG D01
e2 jai krishnan 24-APR-09 27000 ST_CLERK D01
e3 ashok kumar 21-APR-09 27000 SAEXE D02 .1
e4 Geethu Mohan 14-APR-09 27000 SA_MAN D02 .3
e5 Arafat Aboobacker 21-APR-09 27000 SA_REP D03 .2

SQL>

SQL> select fname ,salary from edetails e where salary >= (select avg(salary) from edetails ed where
ed.deptid=e.deptid);

FNAME SALARY
--------------- ----------
jai 27000
ashok 27000
Geethu 27000
Arafat 27000
Urmilla 28000
Sajesh 29000
Manu 29000

Oracle Queries with Example

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

Friday, May 9, 2014

ORACLE SQL Interview Practical Questions with Queries With Answers Part 4

51) What is SET?

SET command changes the system variables affecting the report environment.

52) What is SPOOL?

SPOOL command creates a print file of the report.

53) What is a JOIN?

JOIN is the form of SELECT command that combines info from two or more tables.

Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.

Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.

Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.

Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.

Self Join joins a table to itself as though it were two separate tables.

54) What is Union?

Union is the product of two or more tables.

55) What is Intersect?

Intersect is the product of two tables listing only the matching rows.

56) What is Minus?

Minus is the product of two tables listing only the non-matching rows.

57)What is Correlated Subquery?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

58) What is Multiple columns?

Multiple columns can be returned from a Nested Subquery.

59) What is Sequences?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

Sequence is a database object used to generate unique sequential integer values.

60) What is Synonyms?

Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

61) What is Data types?

Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.

Cannot Query on a long column.

Char, Varchar2 Max. size is 2000 & default is 1 byte.

Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.

Long Character data of variable length upto 2GB.

Date Range from Jan 4712 BC to Dec 4712 AD.

Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.

Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.

62) What is Transaction?

Transaction is defined as all changes made to the database between successive commits.

63) What is Commit?

Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.

64) What is Posting?

Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.

65) What is Savepoint?

Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

66) What is Set Transaction?

Set Transaction is to establish properties for the current transaction.

67) What is Locking?

Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

68) What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks

69) Give the structure of the procedure ?

PROCEDURE name (parameter list.....)

local variable declarations

BEGIN

Executable statements.

Exception.

exception handlers

end;

basically procedure has three

parts

1.variable declaretion(optional)

2.body(mandetory)

3.Exception(optional)

suppose ex

CREATE OR REPLACEPROCEDURE emp_pro( p_id IN employees.employee_id%TYPE)

v_name employees.last_name%TYPE;

v_mail employees.email%TYPE;

BEGIN

SELECT last_name,email INTO v_name,v_mail FROM employees

WHERE employee_id:=p_id;

DBMS_OUTPUT.PUT_LINE('NAME:'||v_name ||'MAILID:'||v_mail);

END;

70) What is Mutating SQL Table?

Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.

71) What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

72) What are the return values of functions SQLCODE and SQLERRM ?

SQLCODE returns the latest code of the error that has occurred.

SQLERRM returns the relevant error message of the SQLCODE.

SQLCODE returns the latest code of the error that has occured.

SQLERRM returns relevant massege

of the error code

73) The most important DDL statements in SQL are?

CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table

DROP TABLE - deletes a database table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

74) Operators used in SELECT statements are?

= Equal

<> or != Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

75) SELECT statements in SQL?

SELECT column_name(s) FROM table_name

SELECT DISTINCT column_name(s) FROM table_name

SELECT column FROM table WHERE column operator value

SELECT column FROM table WHERE column LIKE pattern

SELECT column,SUM(column) FROM table GROUP BY column

SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.

76) What are the PL/SQL Statements used in cursor processing ?

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

77) The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records?

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value.

78) The INSERT INTO Statements in SQL?

INSERT INTO table_name VALUES (value1, value2,....)

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

79) The Update Statement in SQL.

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

80) What is the Delete Statements in SQL?

Delete statement in SQL is used to delete partial/all data.

Especially delete statement is useful in case of partial delete depending upon our criterias otherwise use TRUNCATE to delete whole data from table.

When delete command fires then:

1) Triggers will fire (If created on that table)

2) This will not auto commit changes made So there is one chance to rollback.

3) If u delete whole data then HWM (Highest Water Mark) will not change which gets changed in case of Truncate.

81) How to sort the rows in SQL?

SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC

82) The IN operator may be used if you know the exact value you want to return for at least one of the columns.

SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

*******Thank you for reading and All the best**************

Your comments will help me a lot...please give your comments which will encourage me to post more.....

Saturday, July 11, 2015

ORACLE & SQL