Set - 1

Question 16 :

Correlated Subquery

Answer :

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.


Question 17 :

Multiple columns

Answer :

Multiple columns can be returned from a Nested Subquery.


Question 18 :

Sequences

Answer :

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.


Question 19 :

Synonyms

Answer :

Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.
Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.


Question 20 :

Indexes

Answer :

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.