Oracle interview questions and answers - 2

8. What is the usage of Merge Statement?


Merge statement is used to select rows from one or more data source for updation and insertion into a table or a view. It is used to combine multiple operations. It can be used to combine insert, update, and delete operations into one statement.

9. What do you mean by GROUP BY Clause?


A GROUP BY clause is used in select statement to collect data across multiple records and group the results by one or more columns.

10. What is a sub query and what are the different types of subqueries?


A subquery is a query within a query which is used to get data from multiple tables.

There are two different types of subqueries:

Correlated sub query
A correlated subquery uses values from the outer query, thereby requiring the inner query to execute once for each outer query

There is a significant performance impact on the execution time of the query, and for that reason, correlated subqueries should be avoided if possible.

In many cases a correlated subquery can be re-written as a standard join

Non-Correlated subquery
A non correlated subquery is subquery that is independent of the outer query and it can execute on its own without relying on main outer query.

11. What is a database transaction? What TCL statements are available in Oracle?


Transaction occurs when a set of SQL statements are either completed as a unit or undone as a unit.

To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements. i.e COMMIT, ROLLBACK, SAVEPOINT

COMMIT is used to make a transaction permanent.

ROLLBACK is 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.

12. What is cross join?


Cross join produces the Cartesian product of two tables. Cross join will produce result which combines each row from the first table with the each row from the second table.  A cross join or Cartesian product is formed when every row from one table is joined to all rows in another.


SELECT table1.column, table2.column FROM table1 CROSS JOIN table2;

Here, each row from 1st table joins all the rows of another table. If 1st table contains 3 rows and 2nd table contains 4 rows, then the result set will be 3 * 4 = 12 rows

13. What are temporal data types in Oracle?


Temporal data types is use to store date, time, and time-interval information.

Oracle provides following temporal data types:

Date Data Type - stores different formats of Dates
TimeStamp Data Type - stores different formats of Time Stamp
Interval Data Type - Holds interval between dates and time

14. What is VArray?


Varrays, variable length array is quite similar to array of C++ or Java. It has fixed upper-bound size that has to be specified while it is declared.

They can only be used when you know in advance about the maximum number of items to be stored.