Database Interview Questions - 1

Q: What are the various types of Exceptions?
A: User defined and Predefined Exceptions.

Q: Can we define exceptions twice in same block?
A: No.

Q: What is the difference between a procedure and a function?
A: Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

Q: Can you have two functions with the same name in a PL/SQL block?
Yes.

Q: Can you have two stored functions with the same name?
Yes.

Q: Can you call a stored function in the constraint of a table?
No.

Q: What are the various types of parameter modes in a procedure?
A: IN, OUT AND INOUT.

Q: What is Over Loading and what are its restrictions?
A: OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.

Q: Can functions be overloaded?
A: Yes.

Q: Can 2 functions have same name & input parameters but differ only by return datatype?
A: No.

Q: What are the constructs of a procedure, function or a package?
A: The constructs of a procedure, function or a package are:

·         variables and constants

·         cursors

·         exceptions

Q: Why Create or Replace and not Drop and recreate procedures?
A: So that Grants are not dropped.

Q: Can you pass parameters in packages? How?
A: Yes. You can pass parameters to procedures or functions in a package.

Q: What are the parts of a database trigger?
A: The parts of a trigger are:

·         A triggering event or statement

·         A trigger restriction

·         A trigger action

Q: What are the various types of database triggers?
A: There are 12 types of triggers, they are combination of:

·         Insert, Delete and Update Triggers.

·         Before and After Triggers.

·         Row and Statement Triggers.

(3*2*2=12)

Q: What is the advantage of a stored procedure over a database trigger?
A: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

Q: What is the maximum no. of statements that can be specified in a trigger statement?
A: One.

Q: Can views be specified in a trigger statement?
A: No

Q: What are the values of :new and :old in Insert/Delete/Update Triggers?
A:       INSERT : new = new value, old = NULL

DELETE : new = NULL, old = old value

UPDATE : new = new value, old = old value

Q: What are cascading triggers? What is the maximum no of cascading triggers at a time?
A: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.