Sunday, September 25, 2016

Dell Oracle SQL PL/SQL Interview Questions


Select * from a where hiredate+60 < sysdate; (here hiredate is non-unique index, so query scanning index range or full table scanning.
Have you worked on explain plan, how to tune sql query
Can you write pivot query?                                                                                 
Which exception rise in bulk collect and forall processing
What are analytical functions?             
What is dbms_profiler do?
Difference between sql *loader and external table, explain requirements for that
Have you ever worked on partitions, explain Virtual column based partitioning
what is nvl and nvl2 function and examples
Explain about TK-proof , syntax of TK-proof 
Merge statement syntax
Partition Exchange(One table have 5 partitions like P1...P5, I want to move P5 partition insert into another Table)? 
Difference between 10g and 11g
COLN
---------
A
A
A
B
B
B
B

Write  a query  output like as
coln
------
3
4
coln
-----
A
B
C
D
E
I want output like this
TEXT
--------
A,B,C,D,E
EMPNO     ENAME  
-----------    -----------
12345         REDDY
I want to count of empno and ename
coln
------
-1
-2
-3
-10
1
8
7
I want to count of positive and negative
select sum(decode(sign(value),1,value)) pos, sum(decode(sign(value),-1,value)) neg from pn
select ( select sum(value) from pn where 0<value) as positive, (select sum(value) from pn where 0>value) as negative from dual
select unique ( select sum(value) from pn where 0<value) as positive, (select sum(value) from pn where 0>value) as negative from pn

No comments :

Post a Comment