Monday, June 5, 2017

Interview plsql queries

Q. Write a query to get last Tuesday of current month ?

A. select next_day(last_day(sysdate)-7,'TUESDAY') from dual


Q. Write output of below query:
      select Case when null is null then 'Hello' ELSE 'HI' END from dual;

A. Hello


Q. Write a query to print occurrence of "A" in "AMERICAN DREAMS".

A.     select count(*) from
             (select substr('AMERICAN DREAMS',Level,1) Col from dual
              connect by Level<=Length('AMERICAN DREAMS')) T1
         where T1.col='A';


Q. In one session of sql, following query executed in sequence:

create table T1(a number(10));
insert into T1 values(1);
insert into T1 values(2);
insert into T1 values(3);
create table T2(a number(10));

And in other session, what will be the output of below query:

select * from T1

A.
         A
        ---
         1
         2
         3


Q. In below script, how many times loop run:

declare
i number;
begin
for i IN 1 .. 10
Loop
null;
End Loop;
End;

a) 10
b) 11
c) 1
d) Throw error

A. a