Oracle SQL Interview Questions :

In my previous article i have given different types of advanced SQL interview questions. In this article I would like to give you the Oracle SQL Interview questions with answers. The SQL language is used by so many database management systems like Microsoft SQL, PostgreSQL as well. In this article i will focus on different Oracle SQL Interview Questions. I will explain the Oracle SQL Interview questions with different real life industry examples. These interview questions are really useful for the people who are searching the job in Oracle.

Most Important Oracle SQL Interview Questions :

Question 1 : What is Oracle SQL? Explain its history?

Answer :

1.SQL Stands for Structured Query Language which is a database programming language.

2.Oracle SQL stands for SQL used to do the database programming in Oracle.

3.Larry Ericsson is the father of oracle who has started the company in 1977.

4.The following is step-wise evaluation of different Oracle versions

• In 1977, SEL (Software Development Laboratory) … V1
• In 1979, RSI (Relational S/W Incorporation) …. V2
• In 1983, Oracle Corporation à Oracle 3 [Developed Using ‘C’, which supports simple queries but does not support transactions]
• In 1984, Oracle 4 Supports Transactions [Commit/Rollback]
• In 1985, Oracle 5  Client-Server Architecture [Only install DB in Server, so that ‘N’ no of Clients can connect is known as Client-Server Architecture].
• In 1989, Oracle 6  PL/SQL
• In 1992, Oracle 7  Supports DWH [OLAP-Online Analytical Processing]
• In 1997, Oracle 8 ORDMBS
• In 1999, Oracle 8i  ‘I’ means Internet & it has inbuilt JVM (JAVA Virtual Machine)
• In 2001, Oracle 9i  with 400 New features, e.g. XML (X tended Markup Language), RAC (Real Application Clusters) etc which provided high availability & performance.
• In 2003, Oracle 10g ‘g’ means grid (group of DB Servers)
• In 2006, Oracle 11g  we can add columns with values etc.
• In July 2014 Oracle 12 C is launched which means oracle with Cloud.
• In Feb 2018 Oracle 18 C is launched which is worlds first autonomous database.

Question 2 : What are different Advantages of Oracle SQL?

Answer :

There are following different Advantages of Oracle SQL :

1.High Speed

2.Well Defined Standards

3.Most databases uses SQL (Oracle,Postgresql,SQL server)

4.Simple Syntax

5.User Friendly

6.Less Coding

Question 3 : Explain about latest version of Oracle with its advantages?

Answer :

1.Oracle has announced the new version in 2018 which is ‘Oracle 18c’.

2.Oracle 18c is worlds first autonomous database which will do lot of tasks will be automated using Artificial Intelligence Engine.

3.Following are some advantages of Oracle 18c :

1. Oracle 18c uses adaptive machine learning which will reduce the human errors.
2. It reduces the complexity of database
3. It is highly relible and secure database
4. It reduces the operational cost
5. Self Driving database
6. Self Tuning database
7. Most efficient consumption of resources
8. Less human intervention

Question 4 : Explain difference between Varchar and Varchar2 in Oracle SQL?

Answer :

1. Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of memory space.

2. Varchar will occupy the space for null values whereas varchar2 can not occupy the space for null values.

3.So varchar2 is good to use not to  face performance related problems. varchar2 is faster than varchar datatype.

Question 5 : How to find count of duplicate rows from Employees table? (95% asked in Oracle SQL Interview Questions )

Answer:

Select Emp_no, count (Emp_no) from Employees

Group by Emp_no

Having count (Emp_no)>1

Order by count (Emp_no) desc;

The above query will help user to find out the count of duplicate rows from Employees table.

Question 6 : What is difference between clustered index and non clustered index?

Answer :

Clustered Index	Non- Clustered Index
The Clustered Indexes are indexes which will sort the data physically	Non- Clustered indexes does not sort the data physically. These are logical indexes.
Only one clustered index created for one column	User can create up to 999 non- clustered indexes
These indexes are faster to read than non- clustered indexes	Non- clustered indexes are slower in read operation as compare to clustered indexes.
The select operations are fast in clustered indexes	Insert and update operations are fast in non-clustered indexes
Clustered indexes will only sort the table in specific order so it will not consume the physical space	Non-clustered indexes are physically stored indexes which works in logical way.
Clustered index contains data at the leaf node	Non- clustered indexes do not contain data at leaf node.

Question 7 : What are different features of Oracle 18c ?

Answer :

1. Self Driving Database :

1.1. Oracle 18 c is self driving database in which patching,upgrades and backups can be done automatically.

1.2.No delay waiting for human process and downtime.

1.3.Automated treat detection and remediation.

1.4. All database maintenance tasks will be done without human interventions.

2. Reliable :

2.1. It is very reliable database as it has self recovering capability of detecting and applying corrective actions.

2.2.Oracle Autonomous Database Cloud automatically implements Oracle Real Application Cluster(RAC).

2.3.No downtime required for upgrades,patching or adding storage capacity.

3.Lower Cost :

3.1.It eliminates the costly downtime

3.2.Self Tuning uses adaptive machine learning which automatically activates caching,indexing,storage of indexes.

3.3.It avoids costly overprovisioning.

3.4. It also helps to cut the labour cost as Oracle 12c is automated self driving database.

3.5. Oracle 18c is 5x to 13x less expensive than AWS (Amazon Web Services).

Question 8 :How to find fifth highest salary in Employee table using self-join?(90% asked Oracle SQL Interview Questions )

Answer:

Select * from Employee a Where 5 = (Select Count (distinct Salary) from Employee where a.salary<=b.salary;

The above query will find out the 5th highest salary of Employee from Employee table.

Question 9 : What are different types of indexes in oracle SQL?(90% asked Oracle SQL Interview Questions )

Answer :

There are following different type of indexes in oracle SQL:

1.Normal Indexes

2.Bit map indexes

3.B-tree Indexes

4.Unique Indexes

5.Function Based Indexes

Question 10 : How to find out details about constraints in Oracle SQL?

Answer :

To find details about constraint following query is used:

1.Select  * from User_constraints;

2.Select * from User_cons_columns;

Question 11 : Explain about with clause in Oracle SQL?

Answer :

1. With Clause in Oracle  is released in Oracle 9i release 2 to improve the performance of complex sql queries.
2. The clause works like a global temporary tables of oracle which is used to improve the query speed of complex sql queries.
3. This technique is also called as sub-query factoring as it is used to De-factor the subqueries.
4. With clause in oracle is not supported by all oracle versions ,the oracle version 9i and beyond versions.
5. When sub-query needs to be executed multiple times at that time With clause is used.
6. The name which is assigned to the sub-query is treated as though it was an inline view or table.
7. The With Clause is useful in Recursive queries as well.

Question 12 : What is difference between primary key and foreign key in SQL?(90% asked Oracle SQL Interview Questions )

Answer :

Primary Key	Foreign Key
Primary Key is a chosen candidate key that uniquely defines a tuple in a relation.	Foreign key in a table refers to the primary key of other table.
Primary key value can never be NULL.	Foreign key accepts NULL value.
No two tuples in a relation carry duplicate values for a primary key attribute.	Tuples can carry duplicate value for a foreign key attribute.
There can be only one primary key of a relation.	There can be multiple foreign keys in a relation.
Primary key constraint can be defined on the temporary tables.	Foreign Key constraint can not be defined on the temporary tables.
By default, a primary key is clustered indexed.	Foreign key is not clustered indexed automatically; it has to be done manually.
We can insert a value to a primary key attribute, even if the referencing foreign key does not have that value in its column.	We can not insert a value to a foreign key, if that value is not present in the referenced primary key column.
Before you delete a primary key value, make sure that value is not still present in the referencing foreign key column of referencing table.	You can delete a value from foreign key column without bothering, whether that value is present in referenced primary key column of referenced relation.

Question 13 : What is PL SQL in Oracle?

Answer :

PL SQL is Procedural Language Structured Query Language which is used for adding Business logic to the database.

There are so many situations which is not handled through the SQL functionality.These kind of business logic is added using the PL SQL language.

Question 14 : What is ref cursor?Why it is used?(90% asked Oracle SQL Interview Questions )

Answer :

As the name suggested ref cursor is a variable which will point to the address or reference of the cursor.Ref cursor is variable not cursor but that variable points to cursor.

There are 2 type of ref cursors :

1.Strong Ref cursor

2.Weak Ref cursor

Question 15 : What is cursor?Explain its types?

Answer :

Cursor is a buffer area which is used to process multiple records and also record by record tabs.

There are 2 types of cursors :

1.Implicit cursor

2.Explicit cursor

Question 16 : What are different Set Operators?

Answer :

Set operators are nothing but the operators which are used to connect two tables and fetch the records from the two tables.We need to follow one condition that the table set 1 columns and table set 2 columns are same and its datatype must be same.SQL Set Operators combines the result of 2 queries or components on to the single result.

Following are Set Operators in SQL:

1. Union
2. Unionall
3. Intersect
4. Minus

Question 17 : What is correlated Subquery in Oracle SQL?(90% asked Oracle SQL Interview Questions )

Answer:

Correlated query is the query which is executed after the outer query is executed.The outer query is always dependent on inner query.The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.  (Source-click here)

Example:

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Execution of query:

Step 1:

Select * from Employee E ;

It will fetch the all employees

Step 2:

The First Record of the Employee second query is executed and output is given to first query.

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Step 3:

Step 2 is repeated until and unless all output is been fetched. (Source-Click here)

Question 18 : What is Raise_Application_Error?(90% asked Oracle SQL Interview Questions )

Answer :

When user wants to insert Error message then user needs to use the Raise_Application_Error procedure. Raise_Application_Error is the system defined procedure of package named DBMS_STANDARD.

Syntax :

Raise_Application_Error(Error_Code,Error_Message);

Example :

Raise_Application_Error (-20343, ‘The balance is too low.’);

Question 19 : What is commit,RollBack,Savepoint in Oracle SQL?

Answer :

Commit :

When user commits the data after transaction that changes are permanent changes.

1.Other users can see the data changes made by the transaction.

2.The locks acquired by the transaction are released.

3.The work done by the transaction becomes permanent.

Rollback :

When transaction become wrong user can rollback the data.

1.The work done in a transition is undone as if it was never issued.

2.All locks acquired by transaction are released.

Savepoint :

It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.

Question 20 : How many triggers can be applied on one table?

Answer :

There are maximum 12 triggers can be applied on one table.

Question 21 : How to Find out the database name in oracle SQL?

Answer :

Following queries are useful to find out the database name in Oracle SQL

SELECT Ora_Database_Name FROM DUAL;

SELECT * FROM GLOBAL_NAME;

SELECT Name from V$DATABASE;

Question 22 : What are different Cursor Attributes?

Answer :

%ISOPEN :

Checks if the cursor is open or not

%ROWCOUNT :

The number of rows that are updated, deleted or fetched.

%FOUND :

Checks if the cursor has fetched any row. It is true if rows are fetched

%NOT FOUND :

Checks if the cursor has fetched any row. It is True if rows are not fetched.

Question 23 : What is use of DBLink?

Answer :

Database links are used to communicate between 2 databases.Database links are created in order to form communication between various databases, or different environments like test, development and production.When user wants to insert or update data from one environment to another environment then database links are used.

Question 24 : What is difference between Case and Decode function?(90% asked Oracle SQL Interview Questions )

Answer :

DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.

select decode(Total_salary=50000,’high’,40000,’medium’) as “Decode Test” from Employee where Employee_number in (1,2,3,4,5,6);

This statement will return an error.

CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.

These are some most important Oracle SQL Interview Questions. These Oracle SQL Interview questions are useful to different Oracle developers are well as data analysts. Hope you like this article on Oracle SQL Interview Questions. If you like this article or if you have issues with the same kindly comment in to comments section.