Essential Oracle SQL Interview Questions & 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.
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)
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.
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.
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
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR
RETURN students%ROWTYPE
7. How to find out nth highest salary from emp table?
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For Eg:-
Enter value for n: 2
SAL
---------
3700
8. Explain Which is more faster - IN or EXISTS?
EXISTS is faster than IN
Because exists returns Boolean value whereas IN returns only value.
9. Explain What is an UTL_FILE.What are different procedures and functions associated with it?
UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
10. Explain 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 (2nd occurence of '-')
https://InterviewQuestionsAnswers.ORG.