Set - 4

Question 1 :

What is correlated sub-query?

Answer :

Correlated sub-query is a sub-query, which has reference to the main query.
Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.


Question 2 :

Difference between SUBSTR and INSTR?

Answer :

INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.


Question 3 :

Explain UNION, MINUS, UNION ALL and INTERSECT?

Answer :

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.


Question 4 :

What is ROWID?

Answer :

ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.


Question 5 :

What is the fastest way of accessing a row in a table?

Answer :

Using ROWID.
CONSTRAINTS