Most Frequently asked Oracle Interview Questions and Answers:
Top 40 Oracle interview questions along with answers covering almost all the basic concepts of Oracle.
This is an in-depth series covering almost all Oracle Interview questions:
Part #1: Oracle Basic, SQL, PL/SQL Questions (this article)
Part #2: Oracle DBA, RAC, and Performance Tuning Questions
Part #3: Oracle Forms and Reports Interview Questions
Part #4: Oracle Apps and Oracle SOA Technical Interview Questions
Let’s start with 1st article in the series.
Types of questions covered in this article:
- Basic Oracle interview questions
- Oracle SQL interview questions
- Oracle PL/SQL interview questions
You will find the basics of Oracle explained with simple examples for your understanding. If you are planning to appear for an Oracle interview, these sets of questions covered in this article will definitely be of great help.
Let’s go ahead!!
Table of Contents:
List Of Top Oracle Interview Questions
Q #1) What is Oracle and what are its different editions?
Answer: Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts, and hence it is referred to as Oracle RDBMS as well. It is widely used for online transaction processing, data warehousing, and enterprise grid computing.
Q #2) How will you identify Oracle Database Software Release?
Answer: Oracle follows a number of formats for every release.
For Example,
Release 10.1.0.1.1 can be referred to as:
10: Major DB Release Number
1: DB Maintenance Release Number
0: Application Server Release Number
1: Component Specific Release Number
1: Platform Specific Release Number
Q #3) How will you differentiate between VARCHAR & VARCHAR2?
Answer: Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length. Their differences are:
- VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
- VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.
Q #4) What is the difference between TRUNCATE & DELETE commands?
Answer: Both commands are used to remove data from the database.
The difference between the two include:
- TRUNCATE is a DDL operation while DELETE is a DML operation.
- TRUNCATE removes all the rows but leaves the table structure intact. It can not be rolled back as it issues COMMIT before and after the command execution while the DELETE command can be rolled back.
- The TRUNCATE command will free the object storage space while the DELETE command does not.
- TRUNCATE is faster compared to DELETE.
Q #5) What is meant by RAW datatype?
Answer: RAW datatype is used to store variable-length binary data or byte strings.
The difference between RAW & VARCHAR2 datatype is that PL/SQL does not recognize this data type and hence, cannot do any conversions when RAW data is transferred to different systems. This data type can only be queried or inserted in a table.
Syntax: RAW (precision)
Q #6) What is meant by Joins? List the types of Joins.
Answer: 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
Q #7) What is the difference between SUBSTR & INSTR functions?
Answer:
- SUBSTR function returns the sub-part identified by numeric values from the provided string.
- For Example, [SELECT SUBSTR (‘India is my country, 1, 4) from dual] will return “Indi”.
- INSTR will return the position number of the sub-string within the string.
- For Example, [SELECT INSTR (‘India is my country, ‘a’) from dual] will return 5.
Q #8) How can we find out the duplicate values in an Oracle table?
Answer: 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;
Q #9) How does the ON-DELETE-CASCADE statement work?
Answer: Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.
We can add the ON DELETE CASCADE option to an existing table using the below set of commands.
Syntax:
ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES PARENT_T1 (COLUMN1) ON DELETE CASCADE;
Q #10) What is an NVL function? How can it be used?
Answer: NVL is a function that helps the user to substitute value if null is encountered for an expression.
It can be used as the below syntax.
NVL (Value_In, Replace_With)
Q #11) What is the difference between a Primary Key & a Unique Key?
Answer: 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 a 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.
Q #12) How TRANSLATE command is different from REPLACE?
Answer: TRANSLATE command translates characters one by one in the provided string with the substitution character. REPLACE command will replace a character or a set of characters with a complete substitution string.
For Example:
TRANSLATE (‘Missisippi’,’is’,’15) => M155151pp1 REPLACE (‘Missisippi’,’is’,’15) => M15s15ippi
Q #13) How can we find out the current date and time in Oracle?
Answer: We can find the current date & time using SYSDATE command in Oracle.
Syntax:
SELECT SYSDATE into CURRENT_DATE from dual;
Q #14) Why do we use COALESCE function in Oracle?
Answer: COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. There must be a minimum of two arguments in an expression.
Syntax:
COALESCE (expr 1, expr 2, expr 3…expr n)
Q #15) How will you write a query to get 5th RANK students from the table STUDENT_REPORT?
Answer: The query will be as follows:
SELECT TOP 1 RANK FROM (SELECT TOP 5 RANK FROM STUDENT_REPORT ORDER BY RANK DESC) AS STUDENT ORDER BY RANK ASC;
Q #16) When do we use the GROUP BY clause in SQL Query?
Answer: GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG, etc.
Syntax:
SELECT COLUMN_1, COLUMN_2 FROM TABLENAME WHERE [condition] GROUP BY COLUMN_1, COLUMN_2
Q #17) What is the quickest way to fetch the data from a table?
Answer: The quickest way to fetch the data would be to use ROWID in the SQL query.
Q #18) Where do we use DECODE and CASE Statements?
Answer: Both DECODE & CASE statements will function like IF-THEN-ELSE statements and they are the alternatives for each other. These functions are used in Oracle to transform the data values.
For 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 the commands will display order numbers with their respective status as,
If,
Status O= Ordered
Status P= Packed
Status S= Shipped
Status A= Arrived
Q #19) Why do we need integrity constraints in a database?
Answer: 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.
Q #20) What do you mean by MERGE in Oracle and how can we merge two tables?
Answer: 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>
Q #21) What is the use of Aggregate functions in Oracle?
Answer: Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. These are:
- AVG
- MIN
- MAX
- COUNT
- SUM
- STDEV
Q #22) What are the set operators UNION, UNION ALL, MINUS & INTERSECT meant to do?
Answer: The set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are the same in the source tables.
- UNION operator returns all the rows from both tables except the duplicate rows.
- UNION ALL returns all the rows from both the tables along with the duplicate rows.
- MINUS returns rows from the first table, which does not exist in the second table.
- INTERSECT returns only the common rows in both tables.
Q #23) Can we convert a date to char in Oracle and if so, what would be the syntax?
Answer: We can use the TO_CHAR function to do the above conversion.
Syntax:
SELECT to_char (to_date ('30-01-2018', 'DD-MM-YYYY'), 'YYYY-MM-DD') FROM dual;
Q #24) What do you mean by a database transaction & what all TCL statements are available in Oracle?
Answer: 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 includes:
- 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.
Q #25) What do you understand by a database object? Can you list a few of them?
Answer: The object used to store the data or references of the data in a database is known as a database object. The database consists of various types of DB objects such as tables, views, indexes, constraints, stored procedures, triggers, etc.
Q #26) What is a nested table and how is it different from a normal table?
Answer: A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.
For Example:
CREATE TABLE EMP ( EMP_ID NUMBER, EMP_NAME TYPE_NAME)
Here, we are creating a normal table as EMP and referring to a nested table TYPE_NAME as a column.
Q #27) Can we save images in a database and if yes, how?
Answer: 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.
Q #28) What do you understand by database schema and what does it hold?
Answer: Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema. The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions, etc.
Q #29) What is a data dictionary and how can it be created?
Answer: 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.
Q #30) What is a View and how is it different from a table?
Answer: 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.
The view is different from the table:
- 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.
Q #31) What is meant by a deadlock situation?
Answer: A 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.
Q #32) What is meant by an index?
Answer: 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.
Q#33) What is a ROLE in the Oracle database?
Answer: Giving access to individual objects to individual users is a tough administrative task. In order to make this job easy, a group of common privileges is created in a database, which is known as ROLE. The ROLE, once created can be assigned to or revoked from the users by using GRANT & REVOKE command.
Syntax:
CREATE ROLE READ_TABLE_ROLE; GRANT SELECT ON EMP TO READ_TABLE_ROLE; GRANT READ_TABLE_ROLE TO USER1; REVOKE READ_TABLE_ROLE FROM USER1;
Q #34) What are the attributes that are found in a CURSOR?
Answer: 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.
Q #35) Why do we use %ROWTYPE & %TYPE in PLSQL?
Answer: %ROWTYPE & %TYPE are the attributes in PL/SQL that can inherit the datatypes of a table defined in a database. The purpose of using these attributes is to provide data independence and integrity.
If any of the datatypes or precision gets changed in the database, PL/SQL code gets updated automatically with the changed data type.
%TYPE is used for declaring a variable that needs to have the same data type as of a table column.
While %ROWTYPE will be used to define a complete row of records having a structure similar to the structure of a table.
Q #36) Why do we create Stored Procedures & Functions in PL/SQL and how are they different?
Answer: A stored procedure is a set of SQL statements that are written to perform a specific task. These statements can be saved as a group in the database with an assigned name and can be shared with different programs if permissions are there to access the same.
Functions are again subprograms that are written to perform specific tasks but there are differences between both of them.
| Stored Procedures | Functions |
|---|---|
| Stored Procedures may or may not return a value and can return multiple values as well. | Function will always return only single value. |
| Stored Procedures can include DML statements like insert, update & delete. | We cannot use DML statements in a function. |
| Stored Procedures can call functions. | Functions cannot call stored procedures. |
| Stored Procedures support exception handling using Try/Catch block. | Functions does not support Try/Catch block. |
Q #37) What are the parameters that we can pass through a stored procedure?
Answer: We can pass IN, OUT & INOUT parameters through a stored procedure and they should be defined while declaring the procedure itself.
Q #38) What is a trigger and what are its types?
Answer: A trigger is a stored program that 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
Q #39) How will you distinguish a global variable from a local variable in PL/SQL?
Answer: 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.
Q #40) What are the packages in PL SQL?
Answer: 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.
Conclusion
I hope the above set of questions would have helped you to get a glimpse of what Oracle is all about.
Even if you have a thorough knowledge of all the basic concepts, the way in which you present them in the interview matters a lot. Hence stay calm and face the interview confidently without any hesitation.
Read NEXT Part 2: Oracle DBA, RAC, and Performance Tuning Questions
We wish you all the success!!