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.

oracle 8

Questions and Answers :

What is a CO-RELATED SUBQUERY ?
A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in
the FROM clause of the outer query and the same correlation name as a qualifier of a search
condition in the WHERE clause of the subquery.
eg SELECT field1 from table1 X WHERE field2>(select avg(field2) from table1 Y
where field1=X.field1);
(The subquery in a correlated subquery is revaluated
for every row of the table or view named in the outer query.)
What are various joins used while writing SUBQUERIES ?
Self join-Its a join foreign key of a table references the same table.
Outer Join--Its a join condition used where One can query all the rows of one of the
tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one
or more columns in one table are equal to one or more columns in the second table.

What are various constraints used in SQL ?
NULL, NOT NULL, CHECK, DEFAULT

What are different Oracle database objects ?
TABLES, VIEWS, INDEXES, SYNONYMS, SEQUENCES, TABLESPACES etc

What is difference between Rename and Alias ?
Rename is a permanent name given to a table or column whereas Alias is a temporary
name given to a table or column which do not exist once the SQL statement is executed.

What is a view ?
A view is stored procedure based on one or more tables, it's a virtual table.

What are various privileges that a user can grant to another user ?
SELECT, CONNECT, RESOURCES

What is difference between UNIQUE and PRIMARY KEY constraints ?
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE
keys. The columns that compose PK are automatically define NOT NULL, whereas a column that
compose a UNIQUE is not automatically defined to be mandatory must also specify the column is
NOT NULL.

Can a primary key contain more than one columns ?
Yes

How you will avoid duplicating records in a query ?

By using DISTINCT

What is difference between SQL and SQL*PLUS ?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and
reporting tool. Its a command line tool that allows user to type SQL commands to be executed
directly against an Oracle database. SQL is a language used to query the relational
database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options,
Edit SQL commands and PL/SQL.

Which datatype is used for storing graphics and images ?
LONG RAW
data type is used for storing BLOB's (binary large objects).

How will you delete duplicating rows from a base table ?
DELETE FROM
table_name A WHERE rowid>(SELECT min(rowid) from table_name B where
B.table_no=A.table_no);
CREATE TABLE
new_table AS SELECT DISTINCT * FROM old_table;
DROP
old_table
RENAME
new_table TO old_table
DELETE FROM table_name A
WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name
GROUP BY column_name)

What is difference between SUBSTR and INSTR ?
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE
INSTR provides character position in which a pattern is found in a string.
eg INSTR('ABC-DC-F','-',2) output 7 (2
nd
occurence of '-')

There is a string '120000 12 0 .125' ,how you will find the position of the decimal place ?

INSTR('120000 12 0 .125',1,'.')
output 13

There is a '%' sign in one field of a column. What will be the query to find it ?
'\' Should be used before '%'.

When you use WHERE clause and when you use HAVING clause ?
HAVING
clause is used when you want to specify a condition for a group function and it is
written after GROUP BY clause
The WHERE clause is used when you want to specify a condition for columns, single row
functions except group functions and it is written before GROUP BY clause if it is used.