SQL Interview Questions and Answers for Freshers, Experienced | Database - Page 2
9. What is a database transaction?
Frequently asked SQL Interview Questions,
Database transaction take DB from one consistent state to another. At the end of the transaction the system must be in the prior state if transaction fails or the status of the system should reflect the successful completion if the transaction goes through.10. What are properties of a transaction?
Frequently asked SQL Interview Questions
Properties of the transaction can be summarized as ACID Properties.
Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
In this , a transaction consists of many steps. When all the steps in the transaction go completed it get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one consistent state to another if the transaction succeeds and remain in the original state if the transaction fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the system
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis
11. What is a Database Lock?
Database lock tell a transaction if the data item in questions is currently being used by other transactions.
12. What are the type of locks?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read the item, but cant write into it.
2. Exclusive Lock
When a exclusive lock is applied on data item, other transactions cant read or write into the data item.
SQL Interview Questions and answers on Database Normalization
13. What are the different type of normalization?
Frequently asked SQL Interview Questions
In database design , we start with one single table, with all possible columns. Lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.
1. First Normal Form (1NF)
A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF , we can still have redundant data
2. Second Normal Form (2NF)
A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF , we can still have redundant data
3. Third Normal Form (3NF)
A relation is said to be in 3NF if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key
SQL Interview Questions and answers on Database Keys and Constraints
14. What is a primary key?
Frequently asked SQL Interview Questions
A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. In the examples given below “Employee_ID” field is the primary key .
1. To define a field as primary key, following conditions had to be met :
2. No two rows can have the same primary key value.
3. Every row must have a primary key value
4. Primary key field cannot be null
5. Values in primary key columns can never be modified or updated