What is Normalization?

Normalization means to separate the data in multiple related tables using formal methods. I.e. its major objective is to divide larger tables in to smaller table to reduce data redundancy and improve performance of the data base.

There are several benefits for using Normalization in Database.

Benefits :

1. Eliminate data redundancy
2. Improve performance
3. Query optimization
4. Faster update due to less number of columns in one table
5. Index improvement

First Normal Form:

1. The data in each column should be atomic. No multiple values, separated by comma.
2. The table does not contain any repeating column groups
3. Identify each record uniquely using primary key.

Second Normal Form:

1. Move redundant data to a separate table
2. Create relationship between these tables using foreign keys. 

Third Normal Form:

2.Does not contain columns (attributes) that are not fully dependent upon the primary key

What are Triggers? Explain different types of Triggers?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. 

After Triggers that run after an update, insert, or delete can be used in several ways. 

An AFTER trigger can be created only on tables, not on views.

Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted. INSTEAD OF triggers can be specified on both tables and views.

What are Constraints?

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. 

Types of constraints: 
NOT NULL, 
CHECK, 
UNIQUE, 
PRIMARY KEY, 
FOREIGN KEY.

Difference Primary Key and Unique Key?

Both primary key and unique enforce uniqueness of the column on which they are defined. 

But by default primary key creates a clustered index on the column, where are unique creates a no clustered index. 

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

What is Join and explain different types of joins?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. 

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

 Inner Join shows only matched record from multiple tables.
Left Join shows all records from left table and matched records from right table.
 Right Join shows all records from right table and matched records from left table.
Full outer Join shows all records from multiple tables. (Inner + Left + Right)                         

What is use of ISNULL and Coalesce functions?

Is Null function check for null, if the value is null set user assigned value. Limitation is ISNULL allow only two columns. Coalesce returns the first non-null column from more than one columns.