Oracle Questions

Q-1: Write a query to get nth highest salary in EMP table?

Answer:

select * from

(

select ename,sal,dense_rank() over(order by sal desc) ranking

from emp

)

where ranking=&rank;

Q-2: Write a query which shows the next 12 days date.

Answer :

Method-1:

with day as

(

select trunc(sysdate)+rownum d from all_objects where rownum <13

)

select day.d  days from day;

Method-2:

select sysdate+rownum days from dual connect by rownum<13;

Method-3:

select sysdate+level days from dual connect by level<13;

Method-4:

select sysdate+rownum days from user_objects where rownum<13;

Q-3: Enter a Number and show the minimum digit in that number.

Answer :

select min(substr(9853558706,length(9853558706)-(rownum-1),1)) from dual connect by rownum <= length(9853558706);

Q-4: Get the job wise total investments along with the total investments  on emp table of Scott schema.

Answer :

Method-1:

select job,sum(sal) from emp group by job

union

select null,sum(sal) from emp;

Method-2:

select job,sum(sal) from emp

group by rollup(job);

Method-3:

select job,sum(sal) from emp

group by cube(job);

Q-5: Write a query to see if somebody  modified any code.

Answer :

SELECT OBJECT_NAME,

      TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,

       TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MODIFY_TIME,

       STATUS

FROM   USER_OBJECTS

WHERE  LAST_DDL_TIME > '&DATE';
Q-6: Write a query to make pyramid like structure.
Answer :
select lpad(' ',9-(2*rownum-1)/2)||rpad('*',rownum*2,'*') p from dual connect by rownum<9;
Q-7: Find the hour difference between two dates.
Answer :

select 24 * (to_date('2011-10-11 22:00', 'YYYY-MM-DD hh24:mi')
             - to_date('2011-10-11 18:30', 'YYYY-MM-DD hh24:mi')) hours_difference
       from dual;

Q-6: Delete the duplicate records in a table.

ANS: 

DELETE FROM [TABLE_NAME] A WHERE ROWID > (
   SELECT min(rowid) FROM [TABLE_NAME] B
    WHERE A.KEY = B.KEY);  -- (SELF JOIN)