Oracle SQL Interview Preparation Guide
Download PDF

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.

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.

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.

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.

5 :: Explain me can the default values be assigned to actual parameters?

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

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.

7 :: 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 '-')

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

9 :: Explain Which is more faster - IN or EXISTS?

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

10 :: 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