Oracle interview questions and answers - 2
8. What is the usage of Merge Statement?
Answer:
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?
Answer:
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?
Answer:
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?
Answer:
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?
Answer:
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.
Syntax
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 rows13. What are temporal data types in Oracle?
Answer:
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 time14. What is VArray?
Answer:
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.
