SQL Oracle

  Home  Oracle  SQL Oracle


“Oracle SQL Frequently Asked Questions in various SQL Oracle Interviews asked by the interviewer. So learn Oracle SQL with the help of this SQL Oracle Interview questions and answers guide and feel free to comment as your suggestions, questions and answers on any SQL Oracle Interview Question or answer by the comment feature available on the page.”



16 SQL Oracle Questions And Answers

1⟩ Explain 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.

 141 views

2⟩ What is 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.

Cursor is a variable.

it is similar to a 2D array .

used for processing multiple rows.

used for storing data temporarily.

 151 views

3⟩ Explain 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.

 144 views

4⟩ Explain Difference between database triggers and form triggers?

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

 155 views

6⟩ How to pass cursor variables in PL/SQL 2.2?

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.

 165 views

12⟩ 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

 133 views

13⟩ 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.

 176 views

14⟩ 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.

 148 views

16⟩ 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.

 146 views