Top 20 Oracle RDMS Interview Questions

Ques: 1. What is an Index? Explain the different types of index.

Ans: An index is a performance enhancement method that allows faster retrieval of records from the table. An index creates an entry for each value thus making data retrieval faster.
While creating an index, we should remember the columns which will be used to make SQL queries and create one or more indexes on those columns.
Following are the available indexes.

a. Clustered index:

It sorts and stores the rows of data in the table or view, based on its keys. These are the columns included in the index definition. There can be only one clustered index per table because sorting of data rows can be done only in one order.

b. Non-clustered index:

It contains the non-clustered index key value and each key value entry, in turn, has a pointer to the data row. Thus a non-clustered index contains a pointer to the physical location of the record. Each table can have 999 non-clustered indexes.

c. Unique Index:

This indexing does not allow the field to have duplicate values if the column is unique indexed. It can be applied automatically when a primary key is defined.

Ques: 2. What are Constraints? Explain the different Constraints available in SQL?

Ans: These are the set of rules that determine or restrict the type of data that can go into a table, to maintain the accuracy and integrity of the data inside the table. Following are the most frequent used constraints, applicable to a table:

• <NOT NULL> It restricts a column from holding a NULL value. It does not work on a table.
• <UNIQUE> It ensures that a field or column will only have unique values. It is applicable to both column and table.
• <PRIMARY KEY> uniquely identifies each record in a database table and it cannot contain NULL values.
• <FOREIGN KEY> It is used to relate two tables. The FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables.
• <CHECK CONSTRAINT> It is used to restrict the value of a column between a range. It performs a check on the values, before storing them into the database. It’s like condition checking before saving data into a column.
• <DEFAULT> It is used to insert a default value into a column.

Ques: 3. What are Triggers? What are its benefits? Can we invoke a trigger explicitly?

Ans: The trigger is a type of stored program, which gets fired automatically when some event occurs. We write a Trigger as a response to either of the following event:

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
• SQL allows defining Trigger on the table, view, schema, or database associated with the event.

Following are its benefits:

• Generating some derived column values automatically.
• Enforcing referential integrity.
• Event logging and storing information on table access.
• Auditing.
• Synchronous replication of tables.
• Imposing security authorizations.
• Preventing invalid transactions.

It is not possible to invoke a trigger explicitly. It gets invoked automatically if an event gets executed on the table having an association with the trigger.

Ques: 4. What is the purpose of isolation levels in SQL?

Ans: Transactions use an isolation level that specifies the extent to which a transaction must be isolated from any data modifications caused by other transactions. These also help in identifying which concurrency side-effects are permissible.

Please refer the below list for more clarity on the different type of levels.

i. Read Committed.

It ensures that SELECT query will use committed values of the table only. If there is any active transaction on the table in some other session, then the SELECT query will wait for any such transactions to complete. Read Committed is the default transaction isolation level.

ii Read Uncommitted.

There is a transaction to update a table. But, it is not able to reach to any of these states like complete, commit or rollback. Then these values get displayed (as Dirty Read) in SELECT query of “Read Uncommitted” isolation transaction.

iii. Repeatable Read.

This level doesn’t guarantee that reads are repeatable. But it does ensure that data won’t change for the life of the transaction once.

iv. Serializable.

It is similar to Repeatable Read level. The only difference is that it stops Phantom Read and utilizes the range lock. If the table has an index, then it secures the records based on the range defined in the WHERE clause (like where ID between 1 and 3). If a table does not have an index, then it locks complete table.

v. Snapshot.

It is similar to Serializable isolation. The difference is that Snapshot does not hold a lock on a table during the transaction. Thus allowing the table to get modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data. In case any data modification happens in other sessions then existing transaction displays the old data from Tempdb.

Ques: 5. How do we Tune the Queries?

Ans: Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical a nalysis about the query using which appropriate actions can be taken. 

Ques: 6. What is the difference between BEFORE and AFTER in Database Triggers?

Ans: BEFORE triggers are usually used when validation needs to take place before accepting the change. They run before any change is made to the database. Let’s say you run a database for a bank. You have a table accounts and a table transaction. If a user makes a withdrawal from his account, you would want to make sure that the user has enough credits in his account for his withdrawal. The BEFORE trigger will allow to do that and prevent the row from being inserted in transactions if the balance in accounts is not enough.

AFTER triggers are usually used when information needs to be updated in a separate table due to a change. They run after changes have been made to the database (not necessarily committed). Let’s go back to our back example. After a successful transaction, you would want balance to be updated in the accounts table. An AFTER trigger will allow you to do exactly that.