Question { 3i Infotech, 179606 }

What is the difference between join and union.

Answer

for SET operators the resultant column set should be same for the tables.
i.e.
select * from boy
union
select * from girl
will give an error if they have different column
but in join you can corellete the column to each other.

select * from boy,girl where boy.id_gender=girl.id_gender;

will give result

Question { Aspire, 59980 }

What are the types of triggers ?

Answer

There is
1)DML trigger: with before/after,for each row/for statement,and for 3 types of DML operation.
2)Instead of trigger.
3)Database level trigger: Triggers on DDL statement.
4)System level trigger.

Question { Cap Gemini, 16405 }

What is the difference between the Primary and Foreign key?

Answer

Primary key uniquely identified a record in a table and dont have null values. Also when we create primary key an index is automatically created for primary key.
On the other hand foreign key is used for referential integrity, foreign key can points to primary key in other table and moreover index is not created for foreign key.

Question { iGate, 7405 }

What is meant by Materialized view?

Answer

Materialized views are used to precompute a heavy joins instead of executing a joins again n again. Moreover it physically stores the data unlike views where it is conceptually. Materialized views can be refresh on timely manner.

Question { 5175 }

i want run a sql query query? which phases are run in a back
ground? pls tell me the answer

Answer

The phase of sql query execution is as follows.
1)Syntax Checking: Query will be checked if it is syntactically correct.
2)Semantic Checking:Query will be checked for user has rights on tables etc.
3)Cursor sharing:query will be checked against cursor sharing if soft parse can be done or hard parse required.
4)The creation of cost based decision tree for possible plans.
5)The execution of lowest cost execution plan.
6)Binding the execution plan.
7) Executing the query and fetching the rows.

Question { HCL, 19309 }

what is the difference between joins and set operators.i am
always confusing with two,can u pls kindly help me .

Answer

There is no such difference between JOINS and SET operators. You can get the same result by using JOIN or SET operator.

Question { Infosys, 19077 }

I have 2 packages A and B. Now package A references Package B
and Package B references Package A. How do you compile such
inter-dependent objects in PL/SQL

Answer

With the use of forward declaration we can call other package

Question { Infosys, 19077 }

I have 2 packages A and B. Now package A references Package B
and Package B references Package A. How do you compile such
inter-dependent objects in PL/SQL

Answer

Forward declaration can be implemented for procedure only.In one package you can create inter-dependency but for package it is not possible.

Question { HSBC, 7915 }

While inserting/updating million of records into a database table, how do I came to know how many records has been inserted or updated successfully so far?

Answer

use sql%rowcount after each statement