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 rows
13. 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 time
14. 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.
