PL/SQL Interview Questions

Dear Friends,
                   I am going to share some important questions which are frequently asked in PL/SQL interview:-

1. Can we use rownum with between command ?
Ans:- Yes  e.g.  select * from tab_name where rownum between 1 and 5.

2. Can we use commit in Trigger ?
Ans:- Yes for that we have to declare a pragma.

3.What is difference between primary key and unique key not null ?

Ans:- A unique key by default will be created as a non-clustered index and a primary key will be created as a clustered index by default.

4. Which one is faster between Truncate and Delete and why ?

Ans:- Truncate is faster because it drops the table and recreates it. Truncate is a DDL command.

5. Do we need to write commit after using DDL commands ?

Ans:- No. DDL itself performs commit before and after execution, if any there is any pending DML then that will be automatically commited.

6. What will be the impact of following statement:-

 create table t2 as select * from t1 where 1=2;  ?

Ans:- It will create an empty table t2 identical to t1.

7. Why we use Foreign key ?

Ans:- for referential integrity of table.

8. Why indexes are used?

Ans:-For faster access to data.

9.What is NVL function?

Ans:- NVL- Null Value Function provides a default value to the column when it returns null.

e.g. select nvl(name,'Ram') from emp_mst;

This query will return Ram in place of null values.

10. What are mutating tables?

Ans:- Tables which are being modified by any DML are Mutating table.