Introduction to Oracle database Interview Questions and Answers
Oracle Database Interview Questions are the frequently asked questions in the interview process for database related job roles. Oracle as a database service provider to the industry is the leading vendor for Transactional systems, Data warehouse, enterprise grid computing, and in house legacy applications. Oracle as a database supports the Relational database concepts and features for maintaining the data transactionality. The Interview questions for Oracle database are related to SQL queries, concepts on stored procedures, functions, triggers, cursors, views, tables. The interview questions are also related to the administration, configuration and user roles for the oracle database and schemas. Also, the Oracle database version-specific dependency and features are part of the interview questions.
Now, if you are looking for a job which is related to the Oracle database then you need to prepare for the 2021 Oracle database Interview Questions. It is true that every interview is different as per the different job profiles. Here, we have prepared the important Oracle database Interview Questions and Answers which will help you get success in your interview.
In this 2021 Oracle database Interview Questions article, we shall present 10 most important and frequently asked Oracle database interview questions. These top interview questions are divided into two parts are as follows:
Part 1 – Oracle database Interview Questions (Basic)
This first part covers basic Interview Questions and Answers.
Q1. What is Join and list the various types of joins?
Answer:
Joins are mainly used to fetch the data from two or more tables based on the requirements or conditions on the columns of the table. There are different types of joins that are Inner join, outer join, cross join, equijoin, anti-join, Semi join.
Q2. What is varchar, varchar2 and Blob data type?
Answer:
This is the common Oracle database Interview Questions asked in an interview. Varchar can store up to 2000 bytes and will occupy the space for Null values. Varchar2 can store up to 4000 bytes and it will not occupy any space. Blob is the data type which is used to store two gigabytes memory and for the BLOB, length should be specified bytes.
Q3. Difference between stored procedure and function in Oracle?
Answer:
A stored procedure is referred as the set of SQL statements that are used for performing the specific task and these statements can be saved in the group in the database and can be shared with different programs by providing or granting them access to same. A stored procedure may or may not return any value or they can return multiple values as well. In a stored procedure, we can use DML statement like insert, update and delete. Functions can be called through stored procedures. It also supports the exception handling while using Try catch block. Functions are referred to as subprograms which are used to perform the required task. Functions mainly return only the single value. In functions, we cannot use DML statements in the function. Functions cannot call procedures and it does not support exception handling as the stored procedure does.
Let us move to the next Interview Questions.
Q4. Explain trigger and its types?
Answer:
A trigger is referred to as the stored program which can be written in such a way that it gets executed automatically on any event occur. This event can be any DML or DDL operation. Oracle has two types of triggers that are Row level and state level.
Q5. Explain the use of MERGE in Oracle and example?
Answer:
In Oracle, the MERGE statement is used for merging the data or content of the two tables. It selects the data from the source table and inserts or updates the values in other table based on the conditions applied in the query while executing. Example of merge statement is MERGE into Table_new using Table_old On condition When Matched Then update table set. where <condition> When not matched then insert… when <condition>
Part 2 – Oracle database Interview Questions (Advanced)
Let us now have a look at the advanced Oracle database Interview Questions.
Q6. Explain Cursors in Oracle?
Answer:
A cursor is referred to as a pointer in Oracle, where it controls the context area. A cursor can hold the rows, which are returned by the SQL statements based on the requirements. The set of rows the cursor hold is referred to as an active set. There are two types of cursor that are Implicit cursor and explicit cursor. Implicit cursors are created automatically when SQL statement is executed. In Oracle, the implicit cursor has different attributes such as %Found, %ISOPEN, %Rowcount, %NOTFOUND, %BULK_ROWCOUNT, %BULK_Exceptions for FORALL statement. In Oracle, an Explicit cursor is defined by the programmers or user-defined cursor. It can be created when any select statement returns more than one record. The syntax for creating cursor consists of Declaring the cursor, opening the cursor, fetching the cursor for retrieving the data and closing the cursor.
Q7. Explain primary key and unique key in Oracle?
Answer:
A primary key is used to identify each table row uniquely. A primary key can be only one on the table and it cannot hold the null value in the table and we cannot insert any duplicate or null value in a table for that specific column on which the primary key constraint has applied. It is referred to as cluster index. Unique key mainly used to refrain the duplication of values in the table. In the table, unique keys can be multiple and it can hold one null value per column. It referred to as a non-cluster index.
Let us move to the next Interview Questions.
Q8. What are transactions in Oracle and list the statements available for the same?
Answer:
Transactions in oracle referred as when there is an execution of multiple statements in one go and for controlling these executing statements, there is TCL which is known as transaction control statement and it helps in controlling and managing the statement execution. The TCL statements include of Commit that is used to make the changes or transaction permanent, and other is Rollback which is used to roll back the state of DB to the last state, and last is save point which helps to specify the transaction point to which the rollback can be done later.
Q9. Explain views in Oracle?
Answer:
This is the advanced Oracle database Interview Questions asked in an interview. A view is referred as the user-defined object in Oracle database, which is used to retrieve the records from the different tables for the specific columns which are required to populate or selected based on the certain conditions. It mainly stores the result of a SQL statement and that can be referenced wherever it’s required.it stores the data virtually, it’s not like as table storing the data. It can be referred to as a logical table. Views cannot be updated or deleted but tables can be updated or deleted. In Views, we cannot manipulate the data which is coming from the different tables.
Q10. Explain Role in Oracle?
Answer:
Roles are used to providing access to objects and users in the group which has common privileges assigned in the database. The role can be provided with help of the grant and revoke command to provide the access to and revocation from the users.
Recommended Articles
This has been a guide to list Of Oracle database Interview Questions and Answers. Here we have covered few commonly asked interview questions with their detailed answers so that it helps candidates to crack interviews with ease. You may also look at the following articles to learn more –
