Top 50 Oracle Interview Questions You Should Master in 2024

Knowledge of SQL is a must because the demand for SQL-expertise is high and is valued in the market. Oracle is a very popular secured database that is widely used across multinational companies. So, this article on Oracle interview questions will cover the most frequently asked interview questions and help you to brush up your knowledge before the interview.

If you are a fresher or an experienced, this is the right platform for you which will help you to start your preparation.

Let’s begin by taking a look at the most frequently asked questions.

• Oracle Basic Interview Questions
• SQL Interview Questions
• PL/SQL Interview Questions

So, let’s begin!

Oracle Basic Interview Questions

Q1. How will you differentiate between varchar & varchar2
Q2. What are the components of logical database structure in Oracle database?

Q3. Describe an Oracle table
Q4. Explain the relationship among database, tablespace and data file?
Q5. What are the various Oracle database objects?
Q6. Explain about the ANALYZE command in Oracle?
Q7. What types of joins are used in writing subqueries?
Q8. RAW datatype in Oracle
Q9. What is the use of Aggregate functions in Oracle?
Q10. Explain Temporal data types in Oracle

Q1. How will you differentiate between Varchar & Varchar2?

Both Varchar & Varchar2 are the Oracle data types which are used to store character strings of variable length. To point out the major differences between these,

Q2. What are the components of logical database structure in Oracle database?

The components of the logical database structure in Oracle database are:

• Tablespaces: A database mainly contains the Logical Storage Unit called tablespaces. This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.

• Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user specified.

Q3. Describe an Oracle table

A table is a basic unit of data storage in the Oracle database. A table basically contains all the accessible information of a user in rows and columns.

To create a new table in the database, use the “CREATE TABLE” statement. First, you have to name that table and define its columns and datatype for each column.

CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
…
column_n datatype [ NULL | NOT NULL ]
);

Here,

• table_name: This specifies the name of the table that you want to create.
• column..n: It specifies the number of columns which you want to add in the table. Here, every column must have a datatype and should either be defined as “NULL” or “NOT NULL”. If in case, the value is left blank, it is treated as “NULL” as default.

Q4. Explain the relationship among database, tablespace and data file?

An Oracle database possesses one or more logical storage units called tablespaces. Each tablespace in Oracle database consists of one or more files called the datafiles. These tablespaces collectively store the entire data of databases. Talking about the datafiles, these are the physical structure that confirms with the operating system as to which Oracle program is running.

Q5. What are the various Oracle database objects?

These are the Oracle Database Objects:

Tables: This is a set of elements organized in a vertical and horizontal manner.
Tablespaces: It is a logical storage unit in Oracle.
Views: Views are a virtual table derived from one or more tables.
Indexes: This is a performance tuning method to process the records.
Synonyms: It is a name for tables.

Q6. Explain about the ANALYZE command in Oracle?

This “Analyze” command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

• Analyze command is used to identify migrated and chained rows of the table or a cluster.
• It is used to validate the structure of an object.
• This helps in collecting the statistics about the object used by the user and are then stored on to the data dictionary.
• It also helps in deleting statistics that are used by an object from the data dictionary.

Q7. What types of joins are used in writing subqueries?

A Join is used to compare and combine, this means literally join and return specific rows of data from two or more tables in a database.

There are three types of joins in SQL that are used to write the subqueries.

• Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key which references its own primary key.
• Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.
• Equi-join: An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.

Q8. RAW datatype in Oracle

The RAW datatype in Oracle is used to store variable-length binary data or byte string values. The maximum size for a raw in a given table in 32767 bytes.

You might get confused as to when to use RAW, varchar, and varchar2. Let me point out the major differences between them. PL/SQL does not recognize the data type and hence, it cannot have any conversions when RAW data is transferred to different systems. This data type can only be queried or can be inserted in a table.

Q9. What is the use of Aggregate functions in Oracle?

An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations.
Some common Aggregate functions are:

• Average
• Count
• Sum

Q10. Explain Temporal data types in Oracle

Oracle mainly provides these following temporal data types:

• Date Data Type: Different formats of Dates.
• TimeStamp Data Type: Has different formats of Time Stamp.
• Interval Data Type: Interval between dates and time.

Q11. What is a View?

A view is a logical table based on one or more tables or views. A View is also referred as a user-defined database object that is used to store the results of a SQL query, that can be referenced later in the course of time. Views do not store the data physically but as a virtual table, hence it can be referred as a logical table. The corresponding tables upon which the views are signified are called Base Tables and this doesn’t contain data.

Q12. How to store pictures on to the database?

It is possible to store pictures on to the database by using Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only on Long Raw data type.

Q13. Where do you use DECODE and CASE Statements?

Both these statements Decode and Case will work similar to the if-then-else statement and also they are the alternatives for each of them. These functions are used in Oracle for data value transformation.

Example:

Decode function

Select OrderNum,
DECODE (Status,’O’, ‘Ordered’,’P’, ‘Packed,’ S’,’ Shipped’, ’A’,’Arrived’)
FROM Orders;

Case function

Select OrderNum
, Case(When Status=’O’ then ‘Ordered’
When Status =’P’ then Packed
When Status=’ S’ then ’Shipped’
else ’Arrived’) end
FROM Orders;

Both these commands will display Order Numbers with their respective Statuses like this,

Status O= Ordered
Status P= Packed
Status S= Shipped
Status A= Arrived

Q14. What do you mean by Merge in Oracle and how can you merge two tables?

Merge statement is used to merge the data from two tables subsequently. It selects the data from the source table and then inserts/updates it in the other table based on the condition provided in the query. It is also useful in data warehousing applications. 

Q15. What is the data type of DUAL table?

The Dual table is basically a one-column table that is present in the Oracle database. This table has a single Varchar2(1) column called Dummy which has a value of ‘X’.

SQL Interview Questions

Q16. Explain about integrity constraint?

An integrity constraint is actually a declaration that is defined as a business rule for a table column. They are used to ensure accuracy and consistency of data in the database. It can also be called as a declarative way to define a business rule for a table’s column. There are a few types, namely:

• Domain Integrity
• Referential Integrity
• Domain Integrity

Q17. What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is used to communicate with the server in order to access, manipulate and control data. There are 5 different types of SQL statements available. They are:

1. Select: Data Retrieval
2. Insert, Update, Delete, Merge: Data Manipulation Language (DML)
3. Create, Alter, Drop, Rename, Truncate: Data Definition Language (DDL)
4. Commit, Rollback, Savepoint: Transaction Control Statements
5. Grant, Revoke: Data Control Language (DCL)

Q18. Briefly explain what is Literal? Give an example where it can be used?

A Literal is a string that contains a character, a number, or a date that is included in the Select list and which is not a column name or a column alias.

Also note that, Date and character literals must be enclosed within single quotes (‘ ‘), whereas you don’t have to do that for the number literals.

For example: Select last_name||’is a’||job_id As “emp details” from employee;

In this case, “is a” is literal.

Q19. How to display row numbers with the records?

In order to display row numbers along with their records numbers you can do this:

Select rownum <fieldnames> from table;

This above query will display the row numbers and the field values from the given table.

This query will display row numbers and the field values from the given table.

Q20. What is the difference between SQL and iSQL*Plus?

Q21. What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are a very powerful feature of SQL. These functions can take arguments but always return some value. There are two distinct types of SQL functions available. They are:

• Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions are:

1. Character
2. Number
3. Date
4. Conversion
5. General

• Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

1. avg
2. count
3. max
4. min
5. sum
6. stddev
7. variance

Q22. Describe different types of General Function used in SQL?

General functions are of following types:

1. NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
2. NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
3. NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
4. COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
5. Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

Q23. What is a Sub Query? Describe its Types?

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in where having and from clause.

Guidelines for using subqueries:

1. You should enclose the sub-queries within parenthesis.
2. Place these subqueries on the right side of the comparison condition.
3. Use Single-row operators with single-row subqueries.
4. Use Multiple-row operators with multiple-row subqueries.

Types of subqueries:

1. Single-Row Subquery: Queries that return only one row from the inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
2. Multiple-Row Subquery: These are queries that return more than one row from the inner Select statement. You will also find multiple-column subqueries that return more than one column from the inner select statement. Operators include: IN, ANY, ALL.

Q24. What is the use of Double Ampersand (&&) in SQL Queries? Give an example

You can use && if you want to reuse the variable value without prompting the user each time.

For example: Select empno, ename, &&column_name from employee order by &column_name;

Q25. Describe VArray

VArray is basically an Oracle data type used to have columns containing multivalued attributes and it can hold a bounded array of values. All Varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Each element in a Varray has an index associated with it. It has a maximum size (max_size) that can be changed dynamically.

Q26. What are the attributes of the Cursor?

Each Cursor in Oracle has a set of attributes that enables an application program to test the state of the Cursor. The attributes can be used to check whether the cursor is opened or closed, found or not found and also find row count.

Q27. Name the various constraints used in Oracle

These are the following constraints used:

• NULL: It is to indicate that a particular column can contain NULL values.
• NOT NULL: It is to indicate that particular column cannot contain NULL values.
• CHECK: Validate that values in the given column to meet the specific criteria.
• DEFAULT: It is to indicate the value is assigned to a default value.

Q28. What is the fastest query method to fetch data from the table?

The fastest query method to fetch data from the table is by using the Row ID. A row can be fetched from a table by using RowID.

Q29. Difference between Cartesian Join and Cross Join?

There are no such differences between these Joins. Cartesian and Cross join are the same.

Cross join gives a cartesian product of two tables i.e., the rows from the first table is multiplied with another table that is called cartesian product.

Cross join without the where clause gives a Cartesian product.

Q30. How does the ON-DELETE-CASCADE statement work?

Using this On Delete Cascade you can automatically delete a record in the child table when the same record is deleted from the parent table. This statement can be used with Foreign Keys as well.

You can add this On Delete Cascade option on an existing table.

Syntax:

Alter Table Child_T1 ADD Constraint Child_Parent_FK References
Parent_T1(Column1) ON DELETE CASCADE;

Now let’s move on to the next part of this Oracle Interview Questions article.

Oracle PL/SQL Interview Questions

Q31. What is PL SQL?

PL/SQL is an extension of Structured Query Language (SQL) that is used in Oracle. It combines the data manipulation power of SQL with the processing power of procedural language in order to create super-powerful SQL queries. PL SQL means instructing the compiler what to do through SQL and how to do it through its procedural way.

Q32. Enlist the characteristics of PL/SQL?

There are a lot of characteristics of PL/SQL. Some notable ones among them are:

• PL/SQL is a block-structured language.
• It is portable to all environments that support Oracle.
• PL/SQL is integrated with the Oracle data dictionary.
• Stored procedures help better sharing of application.

Q33. What are the data types available in PL/SQL?

There are two data types available in PL/SQL. They are namely:

• Scalar data types

Example: Char, Varchar, Boolean, etc.

• Composite datatypes

Example: Record, table etc.

Q34. What are the uses of a database trigger

Triggers are the programs which are automatically executed when some events occur:

• Implement complex security authorizations.
• Drive column values.
• Maintain duplicate tables.
• Implement complex business rules.
• Bring transparency in log events. 

Q35. Show how functions and procedures are called in a PL SQL block

A Procedure can have a return statement to return the control to the calling block, but, it cannot return any values through the return statement. They cannot be called directly from Select statements but they can be called from another block or through EXEC keyword.

The procedure can be called in the following ways:
a) CALL <procedure name> direc
b) EXCECUTE <procedure name> from calling environment
c) <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways
a) Execute<Function name> from calling environment. Always use a variable to get the return value.
b) As part of an SQL/PL SQL Expression

Q36. What are the two virtual tables available at the time of database trigger execution?

Columns are referred as Then.column_name and Now.column_name.

• INSERT related triggers, Now.column_name values are available only.
• DELETE related triggers, Then.column_name values are available only.
• UPDATE related triggers, both Table columns are available.

Q37. What are the differences between Primary Key and Unique Key?

Q38. Explain the purpose of %TYPE and %ROWTYPE data types with the example?

%ROWTYPE and %TYPE are the attributes in PL/SQL which can inherit the datatypes of a table that are defined in a database. The main purpose of using these attributes in Oracle is to provide data independence and integrity. Also note that, if any of the datatypes gets changed in the database, PL/SQL code gets updated automatically including the change in the datatypes.

%TYPE: This is used for declaring a variable that needs to have the same data type as of a table column.
%ROWTYPE: This is used to define a complete row of record having a structure similar to the structure of a table.

Q39. Explain the difference between Triggers and Constraints?

Triggers are very different from Constraints in the following ways:

Q40. Exception handling in PL/SQL

When an error occurs in PL/SQL, the corresponding exception is raised. This also means, to handle undesired situations where PL/SQL scripts gets terminated unexpectedly, error-handling code is included in the program. In PL/SQL, all exception handling code is placed in the Exception section.

There are 3 types of Exceptions:

• Predefined Exceptions: Common errors with predefined names.
• Undefined Exceptions: Less common errors with no predefined names.
• User-defined Exceptions: Do not cause runtime error but violate business rules.

Comparison based Interview Questions 

Q41. What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)?

COUNT (*): This returns a number of rows in a table including the duplicates rows and the rows containing null values in the columns.
COUNT (EXP): This returns the number of non-null values in the column identified by an expression.
COUNT (DISTINCT EXP): It returns the number of unique, non-null values in the column identified by an expression.

Q42. Difference between the “VERIFY” and “FEEDBACK” command?

The major differences between Verify and Feedback commands are:

Verify Command: You can use this command to confirm the changes in the SQL statement which can have old and new values that are defined with Set Verify On/OFF.
Feedback Command: It displays the number of records that are returned by a query.

Q43. List out the difference between Commit, Rollback, and Savepoint?

The major differences between these are listed below:

• Commit: This ends the current transaction by ensuring that all pending data changes are made permanent.
• Rollback: This ends the current transaction by discarding or deleting all pending data changes.
• Savepoint: It divides a transaction into smaller parts. You can rollback the transaction until you find a particular named savepoint.

Q44. What is the difference between SUBSTR and INSTR?

SUBSTR returns a specific portion of a string whereas INSTR provides the character position in which a pattern is found in a string. SUBSTR returns string whereas INSTR returns numeric values.

Q45. Point out the difference between USER TABLES and DATA DICTIONARY?

User Tables: This is a collection of tables created and maintained by the user. It also contains user information.
Data dictionary: This is a collection of tables that are created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.

Q46. Major difference between Truncate and Delete?

Q47. Point the difference between TRANSLATE and REPLACE?

Translate is used for character by character substitution whereas Replace is used to substitute a single character with a word.

Q48. What is the difference between $ORACLE_BASE and $ORACLE_HOME?

$Oracle_base is the main or root directory of Oracle whereas Oracle_Home is located beneath the base folder in which all Oracle products reside.

Q49. What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. This is done by creating a group of log files altogether. It ensures that the LGWR automatically writes it to all the members of the current on-line redo log group. If the group fails, the database automatically switches over to the next group and it diminishes the performance of the database.

Q50. What is the difference between a hot backup and a cold backup in Oracle? Explain about their benefits as well

Hot backup (Online Backup): A hot backup is also known as an online backup because it is done while the database is active. Some sites can’t shut down their database while making a backup copy and they are used 24*7.
Cold backup (Offline Backup): A cold backup is also known as an offline backup because it is done while the database has been shut down using the SHUTDOWN command. If the database is suddenly shutdown with an uncertain condition, it should be restarted with RESTRICT mode and then shutdown with the NORMAL option. For a complete cold backup, the corresponding files must be backed up i.e., all datafiles, All control files, All online redo log files and the init.ora file (you can recreate it manually).

I hope this set of Oracle Interview Questions will help you in preparing for your interviews. All the best!

Also, check out our website Edureka, for more exciting technologies and career guide for a noob and professionalists. Edureka is a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe.If you wish to learn more about SQL, then check out our SQL Training.

 Got a question for us? Please mention it in the comments section of this “Oracle Interview Questions” article and we will get back to you.