PLSQL interview Questions and answers

 1.                                                                                                                                              

input Output

1.5 2

2.6 2

3.2 4

8.9 8

5.9 6

select decode(mod(floor(abc),2),0,floor(abc),ceil(abc)) xyz from test1

2.

input

empid gender

1 male

2 female

3 male

empid gender

1 female

2 male

3 female

select t1.*, decode(gender,'FEMALE','MALE','FEMALE') from test1 t1

3.

How to print following output using dual.

2*1*5=10

2*2*4=16

2*3*3=18

2*4*2=16

2*5*1=10

select 2||'*'||level||'*'||(5-level+1)||'='||2*(level)*(5-level+1 ) val from dual connect by level<=5

4.  How to find out the non-numeric record only. 

1

xys

abc

select * from test1 where regexp_like(sex,'^[0-9]$')

5. 

Input

A,b,c,d

output

A

B

C

D

select regexp_substr('a,b,c,d','[^,]',1,level) outpu from dual connect by regexp_substr('a,b,c,d','[^,]',1,level) is not null

6 how to select, on which date of month it is Saturday or Sunday.

select * from (select add_months(trunc(sysdate,'MM'),-1)+level-1 dates,To_char(add_months(trunc(sysdate,'MM'),-1)+level-1,'DAY') days from dual connect by   add_months(trunc(sysdate,'MM'),-1)+level-1 <add_months(trunc(sysdate,'MM'),0)) where days like '%SAT%'  or days like '%SUNDAY%'

7.

How to get output like below

10 100

10 200

10 100

10 300

Sum 700

20 500

20 700

sum 1200

8. how to remove duplicate

10    Bill    2000

11    Bill    2000

12    Mark    3000

12    Mark    3000

12    Mark    3000

13    Tom    4000

14    Tom    5000

15    Susan    5000

delete from emp

where rowid not in

(select max(rowid) from emp group by empno);

9. how to find out the Nth max sal in oracle

select *

  from

  (

    select

        sal

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

    from   table

  )

  where ranking = 4

ELECT * 

FROM Employee Emp1

WHERE (N-1) = ( 

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary)

10. let us suppose we have following emp table

EMPNO ENAME MGRID

111 sateesh 1000

222 rajnikant 1000

1000 ajim  

 Then how can get output as  Emp_id, Emp_name, Mgr_id and Mgr_name.

select t1.EMPNO,t1.ENAME,t2.empNO mgrid,t2.ename MGR_NAME from emp t1

left outer join emp t2 on (t1.mgrid=t2.empno)

11. how to specify default value in lead & lag

input output  

EMPNO LEADS LAGS

111 222 800

222 1000 111

1000 500 222

12. how to found cumulative sum of recharge amount.

DATES CHARGING_AMOUNT CUMULATIVE_SUM

01-Jun-16 834.91 834.91

02-Jun-16 1945.64 2780.55

03-Jun-16 1298.99 4079.54

04-Jun-16 1575.81 5655.35

05-Jun-16 1746.85 7402.2

select  dates,charging_amount,sum(charging_amount) over(order by dates) cumulative_sum from testing_cumulative_sum order by 1

13. input data is like below

ID

1

2

2

3

3

3

 

 

 

 

 

What will be the output for :-

select count(id),count(*),count(1),count(distinct id) from count_testing

COUNT(ID) COUNT(*) COUNT(1) COUNT(DISTINCTID)

6 11 11 3

14. let us suppose there is record like below

Table with missing values 

ID_NO

1

2

4

5

8

10

How to find out which value is missing (in 1 to 10)

ID_NO

3

6

7

9

select lev from

(select level lev from dual connect by level<=10) t1

left join 

(select id_no from table_missing) t2

on (lev=id_no)

where id_no is null;

15.

What will be the output of following query.

Select 1 from dual

Union 

Select ‘1’ from dual;

Ans: it will through an error because data type mismatch.(union ,intersection, minus, union all ) all work on same data type.

16.

 Convert the string "SMILE" to Ascii values?

SELECT SUBSTR(DUMP('SMILE'),15) 

FROM DUAL;

17.

Find out which organization’s revenue increases every year

Eg

company year revenue

wipro 2010 1000

wipro 2011 2000

wipro 2012 3000

infy 2010 2500

infy 2011 3000

infy 2012 2800

select * from rev_test where company not in 

(select company from (select t.* ,case when revenue>=lag(revenue,1,0) over(partition by company order by year) then 'Y' else 'N' end flg from rev_test t)

where  flg='N');