URDINESH: May 2014

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 4 (Views)

Simple View

select * from employee
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create view vwemployee as
select fname,salary from employee;
create view vwemployee as select fname,salary from employee
*
ERROR at line 1:
ORA-00942: table or view does not exist

create force view vwemployee as select fname,salary from employee;

SQL> create force view vwemployee as select fname,salary from employee;

Warning: View created with compilation errors.

SQL> create table employee as select * from employ;

Table created.

SQL> select * from vwemployee;

FNAME SALARY
--------------- ----------
Rahul 20000
Anil 24000
Samir 20000
Jaya 23000
Suresh 25000
Karthik 24000
Bhavana 20000
Steven 24000
Abdul 28000
Manoj 30000

10 rows selected.

SQL> create table temploy as select * from employ;

Table created.

SQL> select * from temploy;

EMPI FNAME LNAME SALARY DEPT
---- --------------- --------------- ---------- ----
E01 Rahul Raj 20000 D02
E02 Anil Babu 24000 D01
E03 Samir Thahir 20000 D03
E04 Jaya Krishnan 23000 D01
E05 Suresh Peters 20000 D01
E06 Karthik Kumar 24000 D02
E07 Bhavana Aravind 20000 D02
E08 Steven Devassy 24000 D03
E09 Abdul Rahim 28000
E10 Manoj Kumar 30000

10 rows selected.

SQL> create or replace view vwtemploy as
select empid,lname,salary from temploy;

View created.

select * from vwtemploy

EMPI FNAME SALARY
---- --------------- ----------
E01 Rahul 20000
E02 Anil 24000
E03 Samir 20000
E04 Jaya 23000
E05 Suresh 20000
E06 Karthik 24000
E07 Bhavana 20000
E08 Steven 24000
E09 Abdul 28000
E10 Manoj 30000

10 rows selected.

update vwtemploy
set salary= 25000
where empid='E05'

SQL> select * from temploy;

EMPI FNAME LNAME SALARY DEPT
---- --------------- --------------- ---------- ----
E01 Rahul Raj 20000 D02
E02 Anil Babu 24000 D01
E03 Samir Thahir 20000 D03
E04 Jaya Krishnan 23000 D01
E05 Suresh Peters 25000 D01
E06 Karthik Kumar 24000 D02
E07 Bhavana Aravind 20000 D02
E08 Steven Devassy 24000 D03
E09 Abdul Rahim 28000
E10 Manoj Kumar 30000

create or replace view vwtemploy
as select empid,fname,salary from temploy
with read only;

View created.

update vwtemploy
set salary= 25560
where empid='E05'

ERROR at line 2:
ORA-01733: virtual column not allowed here

insert into vwtemploy
values('E15','Jeevan',34567);

SQL> insert into vwtemploy
2 values('E15','Jeevan',34567);
insert into vwtemploy
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

SQL> select * from temploy;

EMPI FNAME LNAME SALARY DEPT
---- --------------- --------------- ---------- ----
E01 Rahul Raj 20000 D02
E02 Anil Babu 24000 D01
E03 Samir Thahir 20000 D03
E04 Jaya Krishnan 23000 D01
E05 Suresh Peters 20000 D01
E06 Karthik Kumar 24000 D02
E07 Bhavana Aravind 20000 D02
E08 Steven Devassy 24000 D03
E09 Abdul Rahim 28000
E10 Manoj Kumar 30000

10 rows selected.

Complex view

create view vwsummrytemply as
select deptid,sum(salary) "dept salary" from temploy
group by deptid

View created.

SQL> select * from vwsummrytemply;

DEPT dept salary
---- -----------
58000
D01 67000
D03 44000
D02 64000

SQL> select fname,deptname,depthead from employ e join department d on e.deptid=d.deptid;

FNAME DEPTNAME DEPTHEAD
--------------- --------------- ---------------
Rahul Sales Kavitha
Anil HR Anjan
Samir Finance Suresh
Jaya HR Anjan
Suresh HR Anjan
Karthik Sales Kavitha
Bhavana Sales Kavitha
Steven Finance Suresh

8 rows selected.

create view vwempdept as
select fname,deptname,depthead from employ e
join department d
on e.deptid=d.deptid;

SQL> select * from vwempdept;

FNAME DEPTNAME DEPTHEAD
--------------- --------------- --------------
Rahul Sales Kavitha
Anil HR Anjan
Samir Finance Suresh
Jaya HR Anjan
Suresh HR Anjan
Karthik Sales Kavitha
Bhavana Sales Kavitha
Steven Finance Suresh

8 rows selected.

Inline view

select fname,d.deptname,d.depthead
from employ e,
(select deptid,deptname,depthead from department ) d
where e.deptid=d.deptid;

FNAME DEPTNAME DEPTHEAD
--------------- --------------- ---------------
Rahul Sales Kavitha
Anil HR Anjan
Samir Finance Suresh
Jaya HR Anjan
Suresh HR Anjan
Karthik Sales Kavitha
Bhavana Sales Kavitha
Steven Finance Suresh

Materialized view

alter table temploy
add constraint pkempempid primary key(empid);

CREATE MATERIALIZED VIEW empmatview
REFRESH FORCE on commit
AS
select empid,fname,salary from temploy

Materialized view created.

SQL> select * from empmatview;

EMPI FNAME SALARY
---- --------------- ----------
E01 Rahul 20000
E02 Anil 24000
E03 Samir 20000
E04 Jaya 23000
E05 Suresh 20000
E06 Karthik 24000
E07 Bhavana 20000
E08 Steven 24000
E09 Abdul 28000
E10 Manoj 30000

drop materialized view empmatview

create view vwempdepartment as
select fname , deptname from employ e join department d
on e.deptid=d.deptid;

SQL> create view vwempdepartment as
2 select fname , deptname from employ e join department d
3 on e.deptid=d.deptid;

View created.

SQL> select * from vwempdepartment;

FNAME DEPTNAME
--------------- ---------------
Rahul Sales
Anil HR
Samir Finance
Jaya HR
Suresh HR
Karthik Sales
Bhavana Sales
Steven Finance

8 rows selected.

select text from user_views where view_name='VWTEMPLOY';

TEXT
-------------------------------------------------------------------------
select empid,fname,salary from temploy
with read only

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
MVIEW_WORKLOAD
MVIEW_FILTER
MVIEW_LOG
MVIEW_FILTERINSTANCE
MVIEW_RECOMMENDATIONS
MVIEW_EVALUATIONS
MVIEW_EXCEPTIONS
AQ$_DEF$_AQCALL_F
AQ$DEF$_AQCALL
AQ$_DEF$_AQERROR_F
AQ$DEF$_AQERROR

VIEW_NAME
------------------------------
PRODUCT_PRIVS
VWEMPLOYEE
VWTEMPLOY
VWSUMMRYTEMPLY
VWEMPDEPT
VWEMPDEPARTMENT

17 rows selected.

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 23, 2014

Objective Java Multiple Choice Questions Part 10

91. If there are too many entities with the EntityManager which of the following should be used?

a. EntityManager.clear()

b. EntityManager.delete()

c. EntityManager.discard()

d. EntityManager.save()

92. Purpose of Service Locator pattern

à The Service Locator hides the lookup process's implementation details and complexity from clients.

93. What exception is thrown by EntityManager.find()?.

Returns null

IllegalStateException

IllegalArgumentException

94. What is the XML tag used to set max pool size?

max-pool-size

95. Concept of MOM?

Ans: client---middleware---server

96. What semantics is used by application server to call a remote interface? -

RMI

97. Which bean is used to manage interaction between different entity beans?

Session Beans

98. Optimisation of SQL call is present in which EJB?

a. CMP

b. BMP

c. Stateful

d. Stateless

99. When does Unrepeatable problem occur?

a. When one transaction deletes a record and another reads it

b. When one transaction reads a record and another updates it

c. None

100. Why entity beans are called transactional resources? (Expln: Cos they access DB for storing data often)

a Cos they’re used in transactions

Objective Java Multiple Choice Questions Part 9

81. JBoss server capable of hot deploy of services keeping track of dependencies. T/F? –

82. Which is a consumer in Topic?

MessageSubscriber

83. Which is a producer in Topic?

MessagePublisher

84. Which of the following is correct?

a. EntityManager em=context.lookup();

b. EntityManager em=context.lookup(“persistence”);

c. EntityManager em=(EntityManager)context.lookup();

d. EntityManager em=(EntityManager)context.lookup(“persistence”);

e. EntityManager em=(EntityManager)context.lookup(“java:comp/ejb/entitymanager”);

85. Why do we not have any activation or passivation in MDB like in session beans?

as MDB receives messages at any time asynchronously

86.Match annotation with purpose:

@EntityListener- Used for Entity beans call back method.

@ExcludeSuperClassListener – In an inheritance hierarchy, the super class listener is ignored in the subclass

@ExcludeDefaultListener – DefaultListener will be ignored.

87. Which protocol is used for remote calls?

RMI-IIOP

88. Which declaration of local interface is correct?

There was only one option with @Local annotation.

89. If an object is not found during JNDI lookup what exception is thrown? NamingException

90. What has replaced entity beans?

a. Persitence API

b. Database API

Objective Java Multiple Choice Questions Part 8

71. Who is Consumer in Topic..?

 Topic Destruction.

 Topic subscriber

 Topic Messages.

 Topic publisher.

 Topic consumer.

72 @version is used in beans that change less frequently.

True

false

73. MDBs are like ______

Stateless beans

74. A large code regarding bean managed transaction (check for the comments)

The answer is the option “ut=sc.getTransaction(); // to get transaction from sessioncontext”

75. MDBs used which of the annotations

a. Postconstruct

b. Predestroy

@Resource @MessageDriven

76. Select one of the options dat best describes Entity beans(so many options with large codes will be given)

The answer is mostly “IT’S A POJO CLASS WITH ANNOTATIONS”

77. Diff between stateless and stateful bean:

Stateful can use return values of one method in another but stateless cant do it

78. Which is not a valid bean state?

a. Does Not Exist

b. Ready

c. Pooled

d. No State

79. Which is a client of a session bean implementing local interface?

a. Session,entity and MDB

b. Stateless bean

c. Stateful bean

d. Client class must have a jndi lookup

e. mdb(could be)

80. How to convert an MDB from stateless to stateful.

a. Cannot be done

URDINESH