Oracle-9

Which is more faster - IN or EXISTS ?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN
returns a value.

What is a OUTER JOIN ?
Outer Join--Its a join condition used where you can query all the rows of one of the tables
in the join condition even though they don't satisfy the join condition.

What is a pseudo column. Give some examples ?
It is a column that is not an actual column in the table.
eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

Suppose customer table is there having different columns like customer no, payments.What
will be the query to select top three max payments ?
SELECT
customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from
customer C2 WHERE C1.payment <= C2.payment)

What is the purpose of a cluster ?
Oracle does not allow a user to specifically locate tables, since that is a part of the function
of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer
to create a CLUSTER. A CLUSTER provides a means for storing data from different tables
together for faster retrieval than if the table placement were left to the RDBMS.

What is a cursor ?
Oracle uses work area to execute SQL statements and store processing information PL/SQL
construct called a cursor lets you name a work area and access its stored information A cursor is a
mechanism used to fetch more than one row in a PL/SQL block.
Difference between an implicit & an explicit cursor ?
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including
quries that return only one row. However,queries that return more than one row you must declare
an explicit cursor or use a cursor FOR loop.Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT
statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements
Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements
An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT.
.INTO statements.