Top Oracle Interview Questions and Answers | Most Common Oracle Queries

1. What is RAW datatype?

RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.

2. What is the use of NVL function?

The NVL function is used to replace NULL values with another or given value.

3. What is COALESCE function?

COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL. Coalesce(value1, value2,value3,…)

4. What are nested tables?

Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.

5. What is DML?

Data Manipulation Language (DML) is used to access and manipulate data in the existing objects.  DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.

6. What is meant by Joins? List the types of Joins?

Joins are used to extract data from multiple tables using some common columns or conditions.

There are various types of Joins as listed below:

  • INNER JOIN
  • OUTER JOIN
  • CROSS JOINS or CARTESIAN PRODUCT
  • EQUI JOIN
  • ANTI JOIN
  • SEMI JOIN

7. How can we find out the duplicate values in an Oracle table?

We can use the below example query to fetch the duplicate records.

SELECT EMP_NAME, COUNT (EMP_NAME)
FROM EMP
GROUP BY EMP_NAME
HAVING COUNT (EMP_NAME) > 1;

8. What is the difference between SUBSTR & INSTR functions?

  • SUBSTR function returns the sub-part identified by numeric values from the provided string.
  • INSTR will return the position number of the sub-string within the string.

9. What is the difference between a Primary Key & a Unique Key?

Primary Key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

Given below are a few differences:

  • The primary key can be only one on the table while unique keys can be multiple.
  • The primary key cannot hold null value at all while the unique key allows multiple null values.
  • The primary key is a clustered index while a unique key is a non-clustered index.

10. What is the quickest way to fetch the data from a table?

The quickest way to fetch the data would be to use ROWID in the SQL query.

11. Why do we need integrity constraints in a database?

Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.

Various integrity constraints are available which include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

12. What do you mean by MERGE in Oracle and how can we merge two tables?

The MERGE statement is used to merge the data from two tables. It selects the data from the source table and inserts/updates it in the other table based on the condition provided in the MERGE query.

Syntax:

MERGE INTO TARGET_TABLE_1
USING SOURCE_TABLE_1
ON SEARCH_CONDITION
WHEN MATCHED THEN
INSERT (COL_1, COL_2…)
VALUES (VAL_1, VAL_2…)
WHERE <CONDITION>
WHEN NOT MATCHED THEN
UPDATE SET COL_1=VAL_1, COL_2=VAL_2…
WHEN <CONDITION>

13. What do you mean by a database transaction & what all TCL statements are available in Oracle?

Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements.

The set of statements include:

  • COMMIT: Used to make a transaction permanent.
  • ROLLBACK: Used to roll back the state of DB to last the commit point.
  • SAVEPOINT: Helps to specify a transaction point to which rollback can be done later.

14. Can we save images in a database and if yes, how?

BLOB stands for Binary Large Object, which is a data type that is generally used to hold images, audio & video files or some binary executables. This datatype has the capacity of holding data up to 4 GB.

15. What is a data dictionary and how can it be created?

Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.

16. What is a View and how is it different from a table?

View is a user-defined database object that is used to store the results of an SQL query, which can be referenced later. Views do not store this data physically but as a virtual table, hence it can be referred to as a logical table.

View is different from the table as:

  • A table can hold data but not SQL query results whereas View can save the query results, which can be used in another SQL query as a whole.
  • The table can be updated or deleted while Views cannot be done so.

17. What are the parameters that we can pass through a stored procedure?

We can pass IN, OUT & INOUT parameters through a stored procedure and they should be defined while declaring the procedure itself.

18. How will you distinguish a global variable with a local variable in PL/SQL?

Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared.

19. What is meant by a deadlock situation?

Deadlock is a situation when two or more users are simultaneously waiting for the data, which is locked by each other. Hence it results in all blocked user sessions.

20. What is meant by an index?

An index is a schema object, which is created to search the data efficiently within the table. Indexes are usually created on certain columns of the table, which are accessed the most. Indexes can be clustered or non-clustered.

21. What are the packages in PL SQL?

A package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.

PL/SQL Package structure consists of 2 parts: package specification & package body.

22. How will you distinguish a global variable with a local variable in PL/SQL?

Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared.

23. What is a trigger and what are its types?

A trigger is a stored program which is written in such a way that it gets executed automatically when some event occurs. This event can be any DML or a DDL operation.

PL/SQL supports two types of triggers:

  • Row Level
  • Statement Level

24. What are the parameters that we can pass through a stored procedure?

We can pass IN, OUT & INOUT parameters through a stored procedure and they should be defined while declaring the procedure itself.

25. What are the attributes that are found in a CURSOR?

A CURSOR has various attributes as mentioned below:

(i) %FOUND:

  • Returns INVALID_CURSOR if the cursor has been declared but closed.
  • Returns NULL if fetch has not happened but the cursor is open only.
  • Returns TRUE, if the rows are fetched successfully and FALSE if no rows are returned.

(ii) NOT FOUND:

  • Returns INVALID_CURSOR if the cursor has been declared but closed.
  • Returns NULL if fetch has not happened but the cursor is open only.
  • Returns FALSE, if rows are fetched successfully and TRUE if no rows are returned

(iii) %ISOPEN: Returns TRUE, if the cursor is OPEN else FALSE

(iv) %ROWCOUNT: Returns the count of fetched rows.


Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s