|
|
Interview Questions
|
Database concepts Interview questions -
Part 3
<< Previous Next>>
Part 1 |
Part 2 | Part 3 | Part 4
Question - What is Union and Union All operator?
Answer
Union is used to combine distinct records from
two tables.
Union all combines all records from
two tables.
Question - What is
cursor?
Answer
A Cursor
is a database object that represents a result set and is used to
manipulate data row by row. When a cursor is opened, it is
positioned on a row and
that row is available for processing.
Question - Explain the cursor types.
Answer
DYNAMIC:
It reflects changes happened on the table while scrolling through
the row.
STATIC: It works on snapshot of record set and
disconnects from the server. This kind doesn’t reflects changes
happened on the table while scrolling through the
row.
KEYSET: In this kind, new record is not reflected, but data
modification can be seen.
Question - Explain in brief the cursor optimization tips.
Answer
Close
cursor when it is not required.
You shouldn’t forget to
deallocate cursor after closing it.
You should fetch least number
of records.
You should use FORWARD ONLY option when
there is no need to update rows.
Question - What is sub-query?
Answer
Sub-query
is a query within a Query.
Example of sub-query:
Select CustId, Custname From
Customer Where Cust_Id IN (Select Doct_Id from Doctor)
Question - Explain the use of group by clause.
Answer
"Group By" is used to derive aggegate
values by grouping similar data.
Question - Difference between clustered and non-clustered index.
Answer
Both stored as B-tree structure. The
leaf level of a clustered index is the actual data where as leaf
level of a non-clustered index is pointer to data. We can have only
one clustered index in a table but we can have many non-clustered
index in a table. Physical data in the table is sorted in the order
of clustered index while not with the case of non-clustered
data.
|
|
|
|
|