Top 31 Databases Interview Questions And Answers

Q: What is _Normalisation_?

**Normalization** is basically to design a database schema such that **duplicate and redundant data is avoided**. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption. There is a number of normalization levels from 1. normal form through 5. normal form. Each normal form describes how to get rid of some specific problem. By having a database with normalization errors, you open the risk of getting invalid or corrupt data into the database. Since data "lives forever" it is very hard to get rid of corrupt data when first it has entered the database.

Q: What is the difference between _Data Definition Language (DDL)_ and _Data Manipulation Language (DML)_?

* **Data definition language (DDL)** commands are the commands which are used to define the database. **CREATE**, **ALTER**, **DROP** and **TRUNCATE** are some common DDL commands. * **Data manipulation language (DML)** commands are commands which are used for manipulation or modification of data. **INSERT**, **UPDATE** and **DELETE** are some common DML commands.

Q: How a database index can help performance?

The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined. An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table.

Q: What is Denormalization?

It is the process of improving the performance of the database by _adding_ redundant data.

Q: When would you use NoSQL?

It depends from some general points: * NoSQL is typically good for unstructured/"schemaless" data - usually, you don't need to explicitly define your schema up front and can just include new fields without any ceremony * NoSQL typically favours a denormalised schema due to no support for JOINs per the RDBMS world. So you would usually have a flattened, denormalized representation of your data. * Using NoSQL doesn't mean you could lose data. Different DBs have different strategies. e.g. MongoDB - you can essentially choose what level to trade off performance vs potential for data loss - best performance = greater scope for data loss. * It's often very easy to scale out NoSQL solutions. Adding more nodes to replicate data to is one way to a) offer more scalability and b) offer more protection against data loss if one node goes down. But again, depends on the NoSQL DB/configuration. NoSQL does not necessarily mean "data loss" like you infer. * IMHO, complex/dynamic queries/reporting are best served from an RDBMS. Often the query functionality for a NoSQL DB is limited. * It doesn't have to be a 1 or the other choice. My experience has been using RDBMS in conjunction with NoSQL for certain use cases. * NoSQL DBs often lack the ability to perform atomic operations across multiple "tables".

Q: What are the difference between _Clustered_ and a _Non-clustered_ index?

* With a **Clustered** index the rows are stored **physically** on the disk in the same order as the index. Therefore, there can be only one clustered index. A clustered index means you are telling the database to store close values actually close to one another on the disk. * With a **Non Clustered** index there is a second list that has **pointers** to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records. * It is generally _faster to read_ from a **clustered** index if you want to get back all the columns. You do not have to go first to the index and then to the table. * _Writing_ to a table with a **clustered** index can be _slower_, if there is a need to rearrange the data.

Q: What is _Optimistic Locking_?

**Optimistic Locking** is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit. If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it. This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.

Q: When should I use a NoSQL database instead of a relational database?

**Relational databases** enforces ACID. So, you will have schema based transaction oriented data stores. It's proven and suitable for 99% of the real world applications. You can practically do anything with relational databases. But, there are limitations on speed and scaling when it comes to massive high availability data stores. For example, Google and Amazon have terabytes of data stored in big data centers. Querying and inserting is not performant in these scenarios because of the blocking/schema/transaction nature of the RDBMs. That's the reason they have implemented their own databases (actually, **key-value stores**) for massive performance gain and scalability. If you need a NoSQL db you usually know about it, possible reasons are: * client wants 99.999% availability on a high traffic site. * your data makes no sense in SQL, you find yourself doing multiple JOIN queries for accessing some piece of information. * you are breaking the relational model, you have CLOBs that store denormalized data and you generate external indexes to search that data.

Q: What's the difference between a _Primary Key_ and a _Unique Key_?

**Primary Key:** - There can only be one primary key in a table - In some DBMS it cannot be `NULL` - e.g. MySQL adds `NOT NULL` - Primary Key is a _unique_ key identifier of the record - Primary key can be created on multiple columns (composite primary key) **Unique Key:** - Can be more than one _unique_ key in one table - Unique key can have `NULL` values - It can be a candidate key - Unique key can be `NULL` ; multiple rows can have `NULL` values and therefore may not be considered "unique" _Difference_ between Primary Key and Unique key * **1. Behavior:** Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry). * **2. Indexing:** By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key. * **3. Nullability:** Primary key does not include Null values, whereas Unique-key can. * **4. Existence:** A table can have at most one primary key, but can have multiple Unique-key. * **5. Modifiability:** You can’t change or delete primary values, but Unique-key values can.

Question 1

What are the advantages of NoSQL over traditional RDBMS?

Accepted Answer

**NoSQL is better** than RDBMS because of the following reasons/properities of NoSQL:

* It supports semi-structured data and volatile data
* It does not have schema
* Read/Write throughput is very high
* Horizontal **scalability** can be achieved easily
* Will support Bigdata in volumes of Terra Bytes & Peta Bytes
* Provides good support for Analytic tools on top of Bigdata
* Can be hosted in cheaper hardware machines
* In-memory caching option is available to increase the performance of queries
* Faster development life cycles for developers

Still, **RDBMS is better** than NoSQL for the following reasons/properties of RDBMS:
* Transactions with **ACID** properties - Atomicity, Consistency, Isolation & Durability
* Adherence to **Strong Schema** of data being written/read
* Real time query management ( in case of data size < 10 Tera bytes )
* Execution of complex queries involving **join** & **group by** clauses

Question 2

What is _Normalisation_?

Accepted Answer

**Normalization** is basically to design a database schema such that **duplicate and redundant data is avoided**. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption.

There is a number of normalization levels from 1. normal form through 5. normal form. Each normal form describes how to get rid of some specific problem.

By having a database with normalization errors, you open the risk of getting invalid or corrupt data into the database. Since data "lives forever" it is very hard to get rid of corrupt data when first it has entered the database.

Question 3

What is the difference between _Data Definition Language (DDL)_ and _Data Manipulation Language (DML)_?

Accepted Answer

* **Data definition language (DDL)** commands are the commands which are used to define the database. **CREATE**, **ALTER**, **DROP** and **TRUNCATE** are some common DDL commands.

* **Data manipulation language (DML)** commands are commands which are used for manipulation or modification of data. **INSERT**, **UPDATE** and **DELETE** are some common DML commands.

Question 4

How a database index can help performance?

Accepted Answer

The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined. An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table.

Question 5

What is Denormalization?

Accepted Answer

It is the process of improving the performance of the database by _adding_ redundant data.

Question 6

When would you use NoSQL?

Accepted Answer

It depends from some general points:

* NoSQL is typically good for unstructured/"schemaless" data - usually, you don't need to explicitly define your schema up front and can just include new fields without any ceremony
* NoSQL typically favours a denormalised schema due to no support for JOINs per the RDBMS world. So you would usually have a flattened, denormalized representation of your data.
* Using NoSQL doesn't mean you could lose data. Different DBs have different strategies. e.g. MongoDB - you can essentially choose what level to trade off performance vs potential for data loss - best performance = greater scope for data loss.
* It's often very easy to scale out NoSQL solutions. Adding more nodes to replicate data to is one way to a) offer more scalability and b) offer more protection against data loss if one node goes down. But again, depends on the NoSQL DB/configuration. NoSQL does not necessarily mean "data loss" like you infer.
* IMHO, complex/dynamic queries/reporting are best served from an RDBMS. Often the query functionality for a NoSQL DB is limited.
* It doesn't have to be a 1 or the other choice. My experience has been using RDBMS in conjunction with NoSQL for certain use cases.
* NoSQL DBs often lack the ability to perform atomic operations across multiple "tables".

Question 7

What are the difference between _Clustered_ and a _Non-clustered_ index?

Accepted Answer

* With a **Clustered** index the rows are stored **physically** on the disk in the same order as the index. Therefore, there can be only one clustered index. A clustered index means you are telling the database to store close values actually close to one another on the disk.
* With a **Non Clustered** index there is a second list that has **pointers** to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.
* It is generally _faster to read_ from a **clustered** index if you want to get back all the columns. You do not have to go first to the index and then to the table.
* _Writing_ to a table with a **clustered** index can be _slower_, if there is a need to rearrange the data.

Question 8

What is _Optimistic Locking_?

Accepted Answer

**Optimistic Locking** is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.

If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.

Question 9

When should I use a NoSQL database instead of a relational database?

Accepted Answer

**Relational databases** enforces ACID. So, you will have schema based transaction oriented data stores. It's proven and suitable for 99% of the real world applications. You can practically do anything with relational databases.

But, there are limitations on speed and scaling when it comes to massive high availability data stores. For example, Google and Amazon have terabytes of data stored in big data centers. Querying and inserting is not performant in these scenarios because of the blocking/schema/transaction nature of the RDBMs. That's the reason they have implemented their own databases (actually, **key-value stores**) for massive performance gain and scalability.

If you need a NoSQL db you usually know about it, possible reasons are:

* client wants 99.999% availability on a high traffic site.
* your data makes no sense in SQL, you find yourself doing multiple JOIN queries for accessing some piece of information.
* you are breaking the relational model, you have CLOBs that store denormalized data and you generate external indexes to search that data.

Question 10

What's the difference between a _Primary Key_ and a _Unique Key_?

Accepted Answer

**Primary Key:**

- There can only be one primary key in a table
- In some DBMS it cannot be `NULL` - e.g. MySQL adds `NOT NULL`
- Primary Key is a _unique_ key identifier of the record
- Primary key can be created on multiple columns (composite primary key)

**Unique Key:**

- Can be more than one _unique_ key in one table
- Unique key can have `NULL` values
- It can be a candidate key
- Unique key can be `NULL` ; multiple rows can have `NULL` values and therefore may not be considered "unique"

_Difference_ between Primary Key and Unique key

* **1. Behavior:** Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry).
* **2. Indexing:** By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.
* **3. Nullability:** Primary key does not include Null values, whereas Unique-key can.
* **4. Existence:** A table can have at most one primary key, but can have multiple Unique-key.
* **5. Modifiability:** You can’t change or delete primary values, but Unique-key values can.

Top 31 Databases Interview Questions

What is the difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?

What is Normalisation?

What are the advantages of NoSQL over traditional RDBMS?

Define ACID Properties

What Is ACID Property Of A System?

What is Optimistic Locking?

When should I use a NoSQL database instead of a relational database?

When would you use NoSQL?

What's the difference between a Primary Key and a Unique Key?

How a database index can help performance?

What is Denormalization?

What are the difference between Clustered and a Non-clustered index?

Explain the difference between Exclusive Lock and Update Lock

How do you track record relations in NoSQL?

Explain eventual consistency in context of NoSQL

How does B-trees Index work?

What Is Sharding?

What is the cost of having a database index?

What is BASE property of a system?

How do you off load work from the Database?

What is Optimistic Locking and Pessimistic Locking?

What is Index Cardinality and why does it matter?

What is the difference between B-Tree, R-Tree and Hash indexing?

Why you should never use GUIDs as part of clustered index?

How do you make schema changes to a live database without downtime?

Is the C in ACID is not the C in CAP?

What Does Eventually Consistent Mean?

How does database Indexing work?

What are some other types of Indexes (vs B-Trees)?

Explain the differences in conceptual data design with NoSQL databases?

Name some disadvantages of a Hash index