SQL Oracle Interview Questions and Answers

1 :: Explain Difference between an implicit and 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.

The implicit cursor is used to process INSERT, UPDATE,
DELETE, and SELECT INTO statements. During the processing of
an implicit cursor,Oracle automatically performs the OPEN,
FETCH, and CLOSE operations.

Where as in explicit cursors,the process of its working is
done in 4 steps namely DECLARE a cursor,OPEN a cursor,
FETCH from cursor and CLOSE a
cursor.

IMPLICT CURSOR:- Automatically porvide by oracle which
perform DML statements. queries returns only one row.

EXPLICT CURSOR:- Defined by user. queries returns more
than rows.

Explicit Cursor:-We are not able to Handle NO_DATA_FOUND
Exception.

Implicit Cursor:-We are able to Handle NO_DATA_FOUND
Exception.

0/5 Rating (0 vote)

Is This Answer Correct?    1 Yes 0 No

2 :: Explain Which datatype is used for storing graphics and images?

the following data types are used to store graphics or binary data
1)long raw
2)binary large object(BLOB)
3)binary file(BFILE)

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

3 :: Explain Difference between procedure and function.?

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

Function and Procedure both are PL/SQL blocks, main difference between function and procedure is -
Function has to return some value using return clause whereas procedure may or may not return any value( no out parameter).

We can use functions in SQL query but can't use procedure.

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

4 :: Explain 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.

it's a temporary and logical memory allocation where we can put several tables.

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

5 :: Select the Nth lowest value from a table?

select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') <
'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level

select max(sal)"nth min sal" from(select distinct sal from emp order by sal) where rownum<=&N

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No