Interview Question

1)which statement is running fastly ie insert or delete?
Its definitely Delete.Becuase When Delete operation is being performed then Oracle
doesnot actualy permanently remove the data from data block
but rather marks that particular data block as unusable.
Whereas when concerned to Insert Oracle needs to insert the
new values into Datablocks.

2)Whats the main difference between Subquery and a Join?
Majorly subqueries  run independently and result of the
subquery used in the outer query(other than corelated subquery).
And in case of join a query only give the result when the
joining condition gets satisfied.


3)what is the difference between first normal form & second
normal form?
First Normal Form:
1.it doesnt contain the duplicate rows and null values.
2.those values can be moved into new table.
second normal form:
1.each columns are completely depend upon key

4)What does COMMIT do ?
Commit is a TCL  command which is  used to make  database
transaction parmanent.The data is commited but it can't be rollbacked.

5)difference between oracle8i and oracle9i?
merge is a command which is used to both insert and delete.
timestamp datatype is introduced.
max no of columns in 8i is 256 where as in 9i we can have
999.
on delete set null supports only in 9i.
by using this, when we delete the parent record the child
records are replaced with null values

6)What is the difference between a procedure and a function ?
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.

7)What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
8)What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.
9)What are the various types of queries ?
The types of queries are:
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries
10)What is a transaction ?
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
11)What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.