Question 1 :
What is correlated sub-query?
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?
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?
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?
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?