Interview Questions III

Difference between decode and case.
In which case we are using case and in which case we are using decode?
With an example?

Ans:- decode is a function where case is expression .
decode gives result different when using null see below

SQL> select decode( null,null,1,0) from dual;

DECODE(NULL,NULL,1,0)
---------------------
1

SQL>select case null when null then 1 else 0 end from dual
SQL> /

CASENULLWHENNULLTHEN1ELSE0END
-----------------------------
0
but see here

SQL> select case when null is null then 1 else 0 end from dual;

CASEWHENNULLISNULLTHEN1ELSE0END
-------------------------------
1

The Main Difference is that

1) Decode cannot be used in Where clause but Case can.

2) In Decode Else can be specifed in the statement it self but in Case a seperate statement has to be written.

What are the advantages and disadvantages of View?...

---

advantages

1. hiding the data.

2. you can use two tables data in view.

3. security will be there.

disadvantages

1.when table is not there view will not work.

2. dml is not possible if that is more than one table.

3. it is also database object so it will occupy the space.

Explain what is mutation and what is mutating table and how this mutation problem is solved in a table?

Mutation happens in case of triggers. A 'Mutating table’ is a table which is being updated by Insert, update or delete triggers. It can also be a table which is being updated when delete cascade is run.

Mutation occurs when a trigger is trying to update a row which it is using currently. To solve this either we have to use intermediate table or a view so that it can choose from one while updating the other.

What is the difference between single quote (') and double quote(") in relates to using in SQL. When do you use 'xxx' and "xxx"? what is the difference both of them?

Single quote is used to write a character string or character in sql query.

but,double quotes are used to print the name in sql screen.

for eg:-

select sysdate "current date" from dual;

current date

-----------------

24-mar-06.

How to get first 5 Records then next 5 records till end of row count in SQL –Oracle ?

elect * from emp where rownum<6

Then we can get the next 5 records by the following sql:

select * from emp where rownum<6>

and so on..

Find the two minimum salaries among table

Ans: select sal from (select * from order by sal asc) where rownum <>

How to get the prime number rows from table ie like1,3,5,7,11

Ans :

I tried in sql but sql is not sufficient. U may create a function like this

create or replace function fn_chk_pm(v_num in number) return number
is
v_flag number:=0;
v_j number:=round(v_num/2);
begin
for v_cnt in 2..v_j
loop
if mod(v_num,v_cnt)=0 then
v_flag:=1;
exit when v_flag=1;
end if;
end loop;
if v_flag=0 then
return 1;
end if;
if v_flag=1 then
return 0;
end if;
end;

Assumuming the table as follows

SQL> select * from dummyag;

COL1 COL2
---------- -
2 b
3 c
4 d
1 a

I can call it from sql statement as follows:

SQL>

1 select a.rn,a.col1,a.col2 from (select rownum rn,col1,col2 from dummyag) a
2 where fn_chk_pm(a.rn)=1
3* and a.rn!=1

With the result,

RN COL1 C
---------- ---------- -
2 3 c
3 4 d

Ans 2 : ((select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp)) Minus
(select * from emp where (rowid,0) in (select rowid,mod(rownum,3) from emp))) union
(select * from emp where (rowid,3) in (select rowid,rownum from emp));