Database Interview Questions - 2

Q: What are mutating triggers?
A: A trigger giving a SELECT on the table on which the trigger is written.

Q: What are constraining triggers?
A: A trigger giving an Insert / Update on a table having referential integrity constraint on the triggering table.

Q: Describe Oracle database's physical and logical structure?
A: Physical : Data files, Redo Log files, Control file.
     Logical : Tables, Views, Tablespaces, etc.

Q: Can you increase the size of a tablespace? How?
A: Yes, by adding datafiles to it.

Q: Can you increase the size of datafiles? How?
A: Yes (by using the Resize clause).

Q: What is the use of Control files?
A: Contains pointers to locations of various data files, redo log files, etc.

Q: What is the use of Data Dictionary?
A: Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc

Q: What are the advantages of clusters?
A: Access time reduced for joins.

Q: What are the disadvantages of clusters?
A: The time for Insert increases.

Q: Can Long/Long RAW be clustered?
A: No.

Q: Can null keys be entered in cluster index, normal index?
A: Yes.

Q: Can Check constraint be used for self referential integrity? How?
A: Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

Q: What are the min. extents allocated to a rollback extent?
A: Two

Q: What are the states of a rollback segment? What is the difference between partly available and needs recovery?
A: The various states of a rollback segment are :

·         ONLINE,

·         OFFLINE,

·         PARTLY AVAILABLE,

·         NEEDS RECOVERY and

·         INVALID.

Q: What is the difference between unique key and primary key?
A: Unique key can be null; Primary key cannot be null.

Q: An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted?
A: No.

Q: Can you define multiple savepoints?
A: Yes.

Q: Can you Rollback to any savepoint?
A: Yes.

Q: What is the maximum no. of columns a table can have?
A: 254.

Q: What is the significance of the & and && operators in PL SQL?
A: The & operator means that the PL SQL block requires user input for a variable.

    The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.
   

Q: If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate?
A: It will terminate (Please check ).

Q:Can you pass a parameter to a cursor?
A: Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.
     CURSOR c1 (median IN NUMBER) IS
     SELECT job, ename FROM emp WHERE sal > median;

Q: What are the various types of RollBack Segments?
A:       Public Available to all instances

Private Available to specific instance

Q: Can you use %RowCount as a parameter to a cursor?
A: Yes

Q: Is the query below allowed :
    Select sal, ename Into x From emp Where ename = 'KING'
    (Where x is a record of Number(4) and Char(15))
A: Yes

Q: Is the assignment given below allowed :
     ABC = PQR (Where ABC and PQR are records)
A: Yes

Q: Is this for loop allowed :
     For x in &Start..&End Loop
A: Yes

Q: How many rows will the following SQL return :
      Select * from emp Where rownum < 10;
A: 9 rows

Q: How many rows will the following SQL return:
     Select * from emp Where rownum = 10;
A: No rows

Q: Which symbol preceeds the path to the table in the remote database?
A: @