List of top 50 most asked Oracle interview questions

Oracle DB Interview Questions

Oracle is a secured database, here we are trying to cover all questions based on the basic concepts-based of the Oracle database. The list top most asked Oracle interview questions with answers for freshers and experienced candidate job interviews.

List of Oracle Interview Questions

Q1 What is an Oracle table?

In Oracle, A table is used to contain all the accessible information of a user in rows and columns.

Q2 What is joins in Oracle?

In Oracle, Joins are used to extract data from multiple tables using some common column or condition.

Q3 What are UNION and UNION All Operators in Oracle?

• UNION operator used to return all the rows from both the tables except the duplicate rows.
• UNION ALL operator used to return all the rows from both the tables along with the duplicate rows.

Q4 What are the types of joins?

There are six types of joins used in the Oracle database.

1. Inner Join
2. Outer Join
3. Cross Join
4. Anti Join
5. Semi Join
6. Equi Join

Q5 Which language has been used to develop Oracle?

Oracle has been developed by using the C programming language.

Q6 What are nested tables?

The nested table holds the entire sub-table. It is a data type in Oracle which is used to support columns containing multi-valued attributes.

Q7 What are the various Oracle database objects?

• Table - Set of elements organized in vertical and horizontal
• Tablespaces - Logical storage unit in Oracle.
• Views - Virtual table derived from one or more tables.
• Indexes - Performance tuning method to process the records.
• Synonyms - Name for tables.

Q8 What is a tablespace?

A tablespace is a set of related logical structures, it contains a logical storage unit.

Q9 What is a Primary Key?

In Oracle, the Primary key is used to identify each table row uniquely. The Primary key never holds a null value. Only one primary key is used in a table.

Q10 What is the query to find out the current date & time in Oracle?

Find the current Date & Time using SYSDATE in Oracle.

Syntax Example

SELECT SYSDATE into CURRENT_DATE from dual;

Q11 What is COALESCE function?

COALESCE function is used to return the value which is set to be not null in the list. Supposed If all values in the list are null, then the coalesce function will return NULL.

Q12 What is the list of Aggregate functions in Oracle?

Basically, Aggregate functions in Oracle perform summary operations on a set of values to provide a single value.

1. AVG()
2. MIN()
3. MAX()
4. COUNT()
5. SUM()
6. STDEV()

Q13 How many memory layers are in the Oracle shared pool?

There are two memory layers in Oracle shared pool:

1. library cache
2. data dictionary cache