Top Most SQL Oracle Interview Questions

Q – 1 Explain Difference between an implicit and an explicit cursor?

Ans- 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.

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

Ans- the following data types are used to store graphics or binary data

1)long raw
2)binary large object(BLOB)
3)binary file(BFILE)

Q – 3 Explain Difference between procedure and function.?

Ans- 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.

Q – 4 Explain What is the purpose of a cluster?

Ans- 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.

Q – 5 Select the Nth lowest value from a table?

Ans- 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

Q – 6 Explain What should be the return type for a cursor variable. Can we use a scalar data type as return type?

Ans- 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

Q – 7 How to find out nth highest salary from emp table?

Ans- 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

Q – 8 Explain Which is more faster – IN or EXISTS?

Ans- EXISTS is faster than IN
Because exists returns Boolean value whereas IN returns only value.

Q – 9 Explain What is an UTL_FILE.What are different procedures and functions associated with it?

Ans- 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.

Q – 10 Explain What is difference between SUBSTR and INSTR?

Ans- 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 ‘-‘)

Q – 11 How to pass cursor variables in PL/SQL 2.2?

Ans- In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Q – 12 Explain me can the default values be assigned to actual parameters?

Ans- yes you can assign default value to an actual parameter.. If the actual parameter holds NULL value then the DEFAULT Value will be assigned.

Q – 13 Explain Difference between database triggers and form triggers?

Ans- –Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screen

-Can be row level or statement level No distinction between row level and statement level.
-Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.
-Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
-Can cause other database triggers to fire.Can cause other database triggers to fire, but not other form triggers.

Q – 14 Explain What are various joins used while writing SUBQUERIES?

Ans- 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.

Q – 15 What is is a cursor?

Ans- 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.

Cursor is a variable.
it is similar to a 2D array .
used for processing multiple rows.
used for storing data temporarily.

Q – 16 Explain What is difference between SQL and SQL*PLUS?

Ans- 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.