ORACLE SQL AND PL/SQL INTERVIEW
QUESTION
1. What is the difference between “Delete” & “Truncate”?
2. What us the difference between “Procedure” & Function”?
3. What is the difference between NVL, NVL2, NULLIF, COALESCE?
4. What is the difference between RANK & DENSE RANK?
5. What are the different types of SQL Statement?
6. How to get unique records without using “DISTINCT” in Oracle?
7. What is DUAL table in oracle, Explain with examples?
8. What is trigger?
9. What are the types of triggers?
[Link] is the purpose of triggers in database?
[Link] is the difference between DML & DDL?
[Link] & Difference DECODE & CASE?
[Link] between REPLACE & TRANSLATE?
14. What is view?
[Link] is Simple view?
[Link] is Complex View?
[Link] DML’s allowed on view?
18. Difference between “Simple” & “Complex” view?
[Link] we use “DML” & “DDL” Statements inside function?
[Link] we use “OUT” and “INOUT” parameter in function?
[Link] function return more than one value?
[Link] between VIEW AND MATERIALIZED VIEW?
23. What is set operator? Difference between “UNION” &
“UNIONALL”, “INSERT” & “MINUS”?
24. What is %ROWTYPE and % TYPE? What is the advantage of using
“%rowtype” & “%type”?
25. What is index? How to create index?
[Link] are the types of index?
[Link] to find the list of indexes for a given table?
[Link] B-tree index?
a. What is B-tree index? How index data is stored?
b. Where to find the metadata information?
c. How to find whether index is used by the query?
d. When to create B-tree index?
e. What types of scan operations B-tree index will do?
f. Can we create index for all columns?
g. What are advantages/dis adv. Of B-tree index?
[Link] Bitmap index
[Link] “Function based” index?
[Link] “Reverse Key” index?
[Link] to choose what type of index?
[Link] to know index is being used?
[Link] to monitor index usage?
[Link] are the Benefits/drawbacks of index?
[Link] are the types of DML triggers?
[Link] is the order of execution if there is more than one trigger
exits?
[Link] is SQL Loader?
[Link] is the difference between VARRAY, NESTED TABLE,
ASSOCIATIVE ARRAY.
[Link] is “INSTEAD OF ” Trigger?
[Link] is Error/Exception in PLSQL? How to handle Exception on
PLSQL?
[Link] are the types of Exception?
[Link] is “PRAGMA exception_init”?
[Link] is “Raise_application_error”?
[Link] is “SQLERRM” & “SQLCODE”?
[Link] is “SUBQUERY”? What are the types of “SUBQUERY”?
[Link] “Simple Subquery” and its types with an example?
[Link] “Correlated Subquery” with an example?
49.“Inline View” Vs “SUBQUERY”?
50.“NULL” related practical question?
[Link] & Order by?
[Link] between “IN” & “ANY”?
[Link] Loader & External Table?
[Link] & DENSE_RANK AS “Aggregate” & “Analytical” Function
[Link] was running fine till yesterday, but suddenly its very slow
form today, how would you start your analysis?
[Link] is Cursor? What are the types of cursors?
[Link] is explicit cursor? How to access the value using explicit
Cursor?
[Link] are the cursor attributes?
[Link] is parameterized cursor? For cursor?
[Link] is ref cursor? Strongly typed ref cursor vs weakly typed?
[Link] is “for update” & “where current of” clause in cursor?
62. Cursor vs collection? When to use cursor & when to use
collection?
[Link] Cursor vs ref cursor?
Compound Trigger
[Link] is “Compound trigger”?
[Link] of “Compound trigger”?
[Link] is mutating trigger?
[Link] to avoid mutating trigger error?
[Link] to avoid “mutating trigger error” using compound trigger?
[Link] to write SQL to delete duplicate records?
[Link]’s maximum number of trigger (of different type) that can be
defined on same table?
[Link]’s the “Order of Execution “ if there is more than one trigger
of different type on same table?
[Link] we define more than one trigger of “same type” on same
table?
73.“IF YES”, then what’s the order of execution ? and how to control
the order of execution?
[Link] is PRAGMA autonomous_transaction?
[Link] time use case for “autonomous_transacton”?
[Link] are the Advantage of packages in oracle?
77.I have a table with duplicates entries in production, I cannot
delete existing rows, However I want to prevent further duplicates
rows to get inserted. How can I do?
[Link] “creating a primary key constraint” always an index
automatically?
[Link] “Droping constraint”, will drop underlying index?
[Link] Dropping “TABLE”, will drop underlying index?
[Link] a “primary key” creates NONUNIQUE index?
82. We use “UNIQUE INDEX” or “UNIQUE CONSTRAINT” to enforce
uniqueness of a column. What’s the difference? And when to use
what?
[Link] I use? IGNORE_ROW_ON_DUPKEY_INDEX?
[Link] I use “Oracle error log table?”
[Link] executing the insert statement INSERT INTO emp_target
SELECT * FROM emp_source;
I want only the “non-duplicate” record to get inserted, duplicate
records should get excluded during insertion How can I do?
[Link] I use MERGE Statement?
[Link] is no primary key in TARGET table, then how to do this?
[Link] executing the insert statement
INSERT INTO emp_target SELECT * FROM emp_source;
I want only the “non-duplicate records should get excluded
During insertion. How can I do?
[Link] “FORCE” view?
[Link] “WITH READ ONLY” Constraint?
91. View “WITH CHECK OPTION” Constraint?
92. Write a procedure to create a view for all the tables in the
Given Schema. The view name should be same as table name
Suffixed with “_V”?
[Link] between “FOR” VS “FORALL” STATEMENT?
[Link] of “FORALL”?
[Link] of “FORALL”?
96. What is “MERGE” Statement?
[Link] to handle the exception raised from MERGE statement?
98.A view was created by selecting all the columns of tables eg:
CREATE OR REPLACE VIEW emp_view AS
SELECT *
FROM emp;
If we add a NEW column to the base table, will that column
Get added to view?
If we drop an existing column from the base table, what will
Happen to view?
How to check the list of object used in a view?
99. How to compile view? Why to recompile view?
100. If we create a view before creating the base table using “FORCE VIEW”
How the columns get defined in view?
101. When we will use “FORCE” view in real-time?
102. ALL CONSOLIDATED VIEW RELATED QUESTION?
103. Oracle “TABLE FUNCTION”
104. Oracle “PIPELINED FUNCTION”?
105. Difference between “TABLE FUNCTION” & PIPELINED FUNCTION”?
“CONSTRAINTS” RELATED
106. What Is “Data integrity”?
107. What is “Constraint”?
108. Types Of “Constraint”?
109. “NOT NULL” Constraint?
110. “UNIQUE” Constraint?
a. What is “UNIQUE”Constraint?
[Link] to define “UNIQUE” Constraint?
c. Where to check the metadata information?
d. How to enable / disable constraint?
e. How to drop constraint?
f. Other questions related to “UNIQUE” Constraint?
111. “PRIMARY KEY” CONSTRAINT?
a. What is “PRIMARY KEY” CONSTRAINT?
[Link] to define ”PRIMARY KEY” Constraint?
c. Where to check the metadata information?
d. How to enable/ disable constraint?
e. How to drop Constraint?
f. Other questions related to “PRIMARY” constraint?