31. How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?
Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.
32. What are the uses of SYSDATE and USER keywords?
SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.
33. How does ROWID help in running a query faster?
ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.
34. What are database links used for?
Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.
35. What does fetching a cursor do?
Fetching a cursor reads Result Set row by row.
36. What does closing a cursor do?
Closing a cursor clears the private SQL area as well as de-allocates memory
37. Explain the uses of Control File.
It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.
38. Explain Consistency
Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.
39. Differ between Anonymous blocks and sub-programs.
Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.
40. Differ between DECODE and CASE.
DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.
select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);
This statement returns an error.
CASE is directly used in PL/SQL, but DECODE is used in PL/SQL through SQL only.
