SQL Interview Questions and Answers Part 4

20. What is a Database Lock ? What are the type of locks ?

Answer:

Database lock provides exclusive access to the record. A user can only modify those records to which he has applied a lock. This prevents data from being corrupted when multiple users try to write to the database.

Type of lock

1. Shared Lock
When a shared lock is applied on data item, other transactions can only read the item, but can't write into it.

2. Exclusive Lock
When an exclusive lock is applied on data item, other transactions can't read or write into the data item.

21. What is a Composite Key?

Answer:

A composite primary key represents a set of columns whose values uniquely identify every row in a table.

For example: if "StudentId" and "Student Name" in a table are combined to uniquely identify a row, it is a Composite Key.

22. What is a Foreign Key?

Answer:

A foreign key is used to link two tables together. A foreign key in one table points to a primary key in another table.

They are used to enforce referential integrity and prevent any actions that would destroy links between tables with the corresponding data values.

23. What are the advantages and disadvantages of views in a database?

Answer:

Advantages:

1. The result set of a view is not stored physically, doesn't consume extra disk space.
2. The view hide some of the columns and complexity of joins from the user.
3. Views help limit data access to specific users.

Disadvantages:

1. When a table is dropped, associated view become irrelevant.
2. Since the view is created when a query requesting data from view is triggered, its a bit slow.

24. What is a materialized view?

Answer:

It is a database object that contains the results of a query. Unlike Views which are virtual tables composed of the result set of a SQL query, Materialized Views store result set in a physical object like a table.

We can index materialized view.

Materialized view is used to improve the response time on expensive operations such report queries which join two very large tables.

25. Explain the difference between DELETE, TRUNCATE and DROP commands?

Answer:
DELETE operation deletes the data of a table but we can perform Commit and Rollback to retrieve data. Where condition can be used along with a delete statement.

TRUNCATE is used to delete the data of a table. Here, Commit and Rollback statement can’t be performed. Where condition can't be used along with TRUNCATE statement.

Drop command is used to drop a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.