10 must-know Oracle Interview questions

Interviewing a candidate for a particular job is a tedious task, there are many factors which effect your selection. Managers has to decide within short time span with minimum questions.So if we know the most possible or probable questions before interview we can be prepared. Here 10 must-know Interview questions ( there is no ranking )

1. What is the difference between Procedure and Function ?


1) Function must return a value and procedure need not.

2) Function can be used in SQL with some restrictions. Procedure cannot be called directly from SQL.

2. What is the difference between DELETE and TRUNCATE ?


1) DELETE is a DML command and TRUNCATE is a DDL command.

2) TRUNCATE re-set the memory blocks after execution and much faster than DELETE in most of the circumstances.

3. What is the difference between PRIMARY KEY and UNIQUE KEY constraints ?


1) UNIQUE KEY columns can have null values but PRIMARY KEY column cannot accept null values.

2) A table can have only one PRIMARY KEY column (composite primary key is ok) but many UNIQUE KEY columns allowed.

4. What is Cartesian Product ?


1) If two or more tables are joining without join condition will result into Cartesian products.

2) If table A has 2 rows and table B has 4 rows then Cartesian product between A and B will return 8 rows ( 2 multiply by 4 )

5. What is a Transaction ?


1) Transaction is a logical unit of work which will end in a consistent status

2) In Oracle we can control transactions using Save points, Commit, rollback etc

6. What is a Package, why do we go for packages ?


1) Package is a collection of one more program units having Procedure, Function etc

2) Depends on business logic, all related program units can be tied into a single package for better maintenance, readability etc

7. What is triggers, where we use triggers ?


1) Procedural code which automatically executes based on an event like insert,update, delete etc

2) Auditing, Logging etc

8. How to do sql performance tuning ?


1) Basic sql performance starts with EXPLAIN PLAN

2) Explain cost, index, access methods, plan etc

9. What is the most challenging/difficult feature in Oracle ?


1) This is the most tricky question, answer based on your experience.

2) Do not answer with a trivial problem, try something advanced feature

10. What is a materialized view ?


1) A materialized view, also called as snapshot,which contains the results of a query.

2) Generally used to create summary tables based on aggregations of a table data.

Please note that I have given the short description of the answers. One question answer may trigger another question. So be prepare the Oracle fundamentals deeply before the interview.

Also read Oracle Interview Questions and Answers