SQL SERVER – EXPLICIT Conversion - CONVERT() vs. CAST()

Convert() and Cast() Both the functions are used to convert the data of a column/output parameter of one data type to another data type explicitly.

The main difference between these two is the syntax.

Syntax :                                                                                   Convert(DataType,Expression,Style)                            CAST(Expression as DataType)

CONVERT function can be  used to convert the output to any format we like. Convert is specific to SQL and more flexible while converting Date time, fractional and monetary values.

The CONVERT function's third optional parameter called ‘style’ plays a key role in formatting the value while  converting the  a date, money, float or real value to a VARCHAR.

If formatting is not an issue then, perhaps the CAST function would be better to use. Cast is more ANSI standard , more portable.

CAST() is required when converting between decimal and numeric to Preserve the Number of Decimal Places in original expression.

Suppose we want to calculate the Exact number of working days of an employee by using his total working hours in a month.Assuming that the number of working hours per day is 9 and the number of working hours of the employee in a month is 185. Now In order to get the Exact Number of working days of an employee, we will divide the total working hours of the employee by the working hours per day i.e.  9

Select 185/9 'No. of working days'

Output:

On observing the above result, we can found that the system has returned the approximate integer value instead of the Exact decimal  value. In order to get the exact value, we have to use the Cast() function where we convert the integer value to decimal.

Select CAST(185 as Decimal)/9  'No. of working days'

Output:

CAST() can also be used to Truncate the Decimal Values. If we used ROUND() function to truncate the decimal values, still we are able to see the zeros after the decimal as shown below.

Select Round(185.23,0) 'Truncate'

Output:

To avoid the zeros, We can use either CAST() or Convert() function. The following examples are to truncate the data to 0 or 2 decimal places

Select CAST(185.235467 as INT) 'Truncate'
Select CAST(185.235467 as DECIMAL(5,2)) 'Truncate'  Select CONVERT(DECIMAL(5,2),185.235467)  'Truncate' 

Output:

Similarly CAST() can also be used to Concatenate Different Data Types. Let us take an example of AlphaNumeric Identity Column whose values are the concatenation of an integer and a string.

Here If we tried to concatenate an integer to a  string, it throws an error message as shown below.

Select 'EMP'  + 1

Output:

On using the CAST() function,

Select  'EMP' + CAST(1 as VARCHAR)  'EMPID'

Output:

The Convert() function is used to change the Datetime Datatype to Varchar. It can be used to get the Date and time values separately.

Example:

Select  Convert(VARCHAR(10),GetDate(),101)'Date(mm/dd)'
Select  Convert(VARCHAR(10),GetDate(),103)'Date(dd/mm)'

Output:

SQL SERVER – Data Type Conversions

In general, while writing Stored Procedures or Queries in the SQL Server, there will be a need to assign an object of one data type to another object of different data type. Also, Some times we have to concatenate a string to an integer or Truncate/Preserve the Decimal parts of a column or change the DATETIME formats. All these actions are possible by Data type Conversion.

In SQL Server, two types of data type conversion is possible.

Implicit Data Type Conversion:                                        This will be handled by SQL Server automatically where it converts the data from one data type to another. These type of conversions are invisible to the user. For example, if an INT is compared to BIGINT, then INT is implicitly converted to BIGINT before the comparison.

Example :

Let us first create a table  IMPLICIT_CONVERSION.

CREATE TABLE IMPLICIT_CONVERSION                             (ID INT,VALUE_INT  INT, VALUE_DECIMAL DECIMAL(5,2))

The table includes three columns to hold integer information: VALUE_INT , VALUE_DECIMAL. The two column names reflect the names of the data types used to define the columns. Now let's Insert data into those columns:

INSERT INTO IMPLICIT_CONVERSION
Select 1,555,555

Finally, Let us see how the data is stored in this table

Select * FROM IMPLICIT_CONVERSION

OutPut :

As you can see, each Integer value is a little different. The VALUE_INT Column stores the integer values while the VALUE_DECIMAL stores the Decimal value.

Explicit Data Type Conversion:                                            In Order to Convert the data explicitly, we have to use either the CAST() or CONVERT() Transact-SQL functions.

Examples :

Select CAST('5' As INT) Integer_Value                       Select CONVERT(INT,27.23) Integer_Value
Select CONVERT(VARCHAR(10),GetDate(),103) CurrentDate

Output :  

SQL SERVER – How To Find the Number Of Records in a Table

There are multiple methods to find out the number of records in a table.

Let us first consider a table Organization.

Select  OrgId,Acctnum,CompId,OrgName,Phone              From  Organization

Output:

As shown in the above output, the Organization table Contains 5 records.

Now, To find out the Number of records in the table Organization, we can follow the below methods.

1. Using Count(*)

Example :                                                                          Select  Count(*) RecordCount  From  Organization

Output:

2. Using the System Stored Procedure                           

Example :                                                                              Exec SP_SpaceUsed  'Organization'

Output:

3. Using System Views                                               

Example :                                                                                     Select  Max(Rows) RecordCount                                 From SysObjects So, SysIndexes Si
where so.type='U' and si.id=object_id('Organization')

Output:

SQL SERVER – How To Find the First Day and Last Day of a Month

This is a quite common Interview question - “How To find the First and Last Days Of  a Month”. There are Multiple methods to achieve this.

A.  To find out the First Day of the current Month: 

In Order to find out the first day of the current month, we will use the following query :

Select Convert(varchar,DateAdd(d,-day(GetDate())+1, GetDate( ) ) ,101) 'First Day'

As discussed in the Earlier Articles, GetDate() returns the current date and time

Select GetDate() 'Current Date'

OutPut:

From this Current Date, I have extracted the Day, using the function DAY().

Select Day(GetDate())  'Day'

OutPut:

Now, In order to find the first day of the month, we have to subtract (the Current day –1) days,  i.e. (24-1) days,  from the current date. For this, DateADD() has been used.

As discussed Earlier, The Dateadd() function contains three arguments - Datepart,Integer and Date. Here,

Datepart is  ‘d’ which represents days                         Integer  is  -day(GetDate())+1 i.e.-24+1                      Date          is  The Current Date

On clubbing all the above functions, the final query is..

Select DateAdd(d,-day(GetDate())+1,GetDate()) 'First Day'

Output:

In order to convert the output into mm/dd/yyyy format,we can use the Convert() function.

Select Convert(Varchar,DateAdd(d,-day(GetDate())+1, GetDate()), 101) 'First Day'

Output:

B.  To find out the Last Day of the current Month: 

The Query to find out the Last day of the month is :

Select DateAdd(d,-Day(GetDate()),DateAdd(m,1,GetDate())) 'Last Day'

Here, moved a month ahead from the Current date and then subtracted the Current day,  to get the last day of the Current month.

Select DateAdd(m,1,GetDate()) 'Next Month'

Output:

From this, Current day of the month has been subtracted, using DateAdd() function.

Datepart is  ‘d’ which represents days                         Integer  is  -day(GetDate()) i.e.-24                                  Date    is  DateAdd(m,1,GetDate()), Next Month’s Date

The final Query is:

Select DateAdd(d,-Day(GetDate()),DateAdd(m,1,GetDate())) 'Last Day'

Output:

Again, on converting the output into mm/dd/yyyy format,

Select Convert(Varchar,DateAdd(d,-Day(GetDate()),DateAdd(m,1,GetDate())), 103) 'Last Day'

Output:

SQL SERVER – Differences Between a View and a Stored Procedure

View

View is a Logical way of Looking at the data that is located in the Physical table. View Contains Only the definition but not the Data.

View Contains only the Select Statement. CREATE,INSERT,UPDATE,DELETE and TRUNCATE Statements are not Allowed.

Views does not Contain any Parameters.

Views does not Contain ‘Select Into’, ‘Group By’ , ‘Order By’ and ‘Having’.

Stored Procedure

Stored Procedure is a database object which can used to move the data from the application to the Backend enforcing the Business Rules.

In addition to the Select statement, Stored Procedure contains all the DML statements like INSERT,UPDATE,DELETE and TRUNCATE Statements.

Stored Procedure can contain three types of Parameters IN, OUT and INOUT.

Stored Procedure can have  the clauses like  ‘Group By’ , ’Order By’, ’Having’

 

Microsoft’s Bing – A Decision Engine

Microsoft has improved and rebranded it’s new Search Engine called Bing,  which was launched on 3rd June 2009. This is a replacement of its previous Search Engine 'Live Search'. Now Bing is the default Microsoft search Engine. 

Till Now, ‘Live Search’ struggled a lot to maintain search share among the major search engines Google and Yahoo. It is currently in a distant third-Place position behind Google and Yahoo Inc. Microsoft  faced tough challenges to increase its share of the Internet search market.

Bing is Microsoft’s answer for a need to rebrand Live Search and to add new competitive features. The Company is calling it a ‘Decision Engine’ - can challenge Google at its own best game. The company is planning to spend as much as $100 million on Bing advertising

Coming to the new features, With Bing, we don't have to leave what we're doing and open another browser window, we can directly go to any of MSN, IE8, Messenger or Hotmail or any of our other favorite places online

Bing auto suggests  as we types the search query.On hovering on the search result it displays automatically an extract of the article at its right side.

We can also save  the search queries in a folder on the local computer for future use.

Bing Travel compares all the global Hotel’s price and amenities.

Bing Maps is a Microsoft’s mapping platform which maps for Enterprise.

We can Experience an Enhanced Video features where we can have a more comprehensive way to search for video.On hovering over the video starts the playback.No need to press play.

Bing Health can be used to access the medical information. Bing Shopping can be used for searching any product sorted by price, brand, or rating. Bing XRank keeps track of notable people and puts them in order with its own ranking formula.

In my opinion, With these all new features, Bing can definitely hold up against Google and Yahoo in the challenge game.

Let us see whether Microsoft can do to wean the users  away from Google?

 

SQL SERVER - How To Display all Tables Of a Database

In order to display the Names of all Tables Or Details of all Tables of a Database, We have different alternative methods.

1. Using Information_Schema : By using Information_Schema.Tables Or Information_Schema.Columns

Syntax:

• Select Table_Name From Information_Schema.Tables  Where Table_type = 'BASE TABLE'
• Select Distinct Table_Name From Information_Schema.Columns

2. Using SysObjects :

Syntax:

• Select * From Sys.Objects Where Type = 'U'
• Select * From SysObjects Where Xtype = 'U'

3. Using System Stored Procedures

Syntax:

• Exec Sp_Tables @Table_Type = "'TABLE'"
• Exec Sp_MsForEachTable 'Print ''?'''

Difference Between Delete and Truncate Commands

Delete Command Is a DML statement and  Maintain log for each of the record that will delete. Delete fires the trigger which was defined on it.Delete maintains the Identity value.Delete can be Roll backed

Truncate Command Is a DDL Statement and does not create any log while truncating. That's why Truncate is faster than Delete. Truncate will not fire any trigger. Truncate Reseeds the Identity value. Once Truncate Operation has been done, we can not Roll back the changes. 

Now let us see all the differences between these two commands 

DELETE	TRUNCATE
1.  Is a DML statement	1.  Is a DDL Statement
2.  Will maintain log for  each of the record	2.  Will not create any log
3.  Little bit Slower	3.  Faster than Delete
4.  Will fire the trigger, if any Delete trigger was defined on the table	4.  Will not fire any trigger
5.  Contains the 'Where' clause	5.  Does not contain 'Where' clause
6.  Maintains the Identity value	6.  Reseeds the Identity value
7.  Can be Roll backed	7.  Cannot be Roll backed
8.  Does not require Disabling of Referential Constraints	8.  Requires Disabling the Referential Constraints

Differences Between Stored Procedure and User Defined Function

The Differences Between a Stored Procedure and a User Defined Function are :

1. User Defined Function Can be executable from SQL Select statement where as Stored Procedure cannot be executable from SQL select statement. We have to use execute Or Exec Keyword to run the Stored Procedure.
2. Stored Procedure Supports Output Parameters where as User Defined Function does not Return Output Parameter.
3. Stored Procedure Can’t return a Table variable while the User Defined Function Can.
4. Stored Procedure Can’t Join to a stored Procedure while a user defined function can join to a UDF.
5. Stored procedure can be used to change some of Server Environment & Operating Environment where as this is not possible in case of User Defined Function.
6. T-SQL ignores the Errors and Processes next statement in case of stored Procedure But it stops the process in case of User Defined function.
7. Stored Procedure can be used in XML for Clause while Used defined function cannot.

How To Count the Number of Occurrences of a word in a Sentence

A very simple method to find out the Number of Occurrences of a word in a sentence is by using the String Functions     Len() and Replace().

Let us take a tongue twister.

"I wish to wish the wish you wish to wish, but if you wish the wish the witch wishes, I won't wish the wish you wish to wish"

Now we want to find out the number of occurrences of the word "wish" in the above sentence.

This can be done by the following Code:

Declare @Str Varchar(500)

Set @Str = 'I wish to wish the wish you wish to wish, but if you wish the wish the witch wishes, I won''t wish the wish you wish to wish'

Select (LEN(@Str) - LEN(REPLACE(@Str, 'wish', ''))) / LEN('wish') [Word Count]

And The Out put is :

SQL SERVER - How To Retrieve Records From a Table with out Specifying the Column Names

Let us Consider a case where we want to List all the records of all columns of a table called Organization. In general, we will make use the following Query.

Select * From Organization

Here we may or may not know the Columns Names of the table Organization. The ‘*’ in the above query will take care of everything and displays all the records of all the columns as shown below.

Now Let us assume that we are not Aware of the Column Names of the table Organization and we want to list the First 5 Column records from the table Organization. How to do this??

Here is the Sample Code to achieve this.

Method 1 :

Here we have used the SysColumns table to Retrieve First 5 column names from the table Oraganization.

Sample Code :

Declare @Columns nvarchar(4000)
Declare @Query nvarchar(4000)

-- To get the Column Names each separated by comma
Select @Columns = Coalesce(@Columns + ',','') + Name From SysColumns where ID = Object_ID('Organization') And
ColOrder <=5

--Build Select Statement
Set @Query = 'Select ' + @Columns + ' From Organization'

Exec SP_ExecuteSql @Query

Method 2 :

Here we have used the Information_schema.Columns to Retrieve First 10 column names from the table Oraganization.

Sample Code :

Declare @Columns nvarchar(4000)
Declare @Query nvarchar(4000)

-- To get the Column Names each separated by comma
Select @Columns = Coalesce(@Columns + ',','') + Column_Name From Information_schema.Columns where Table_name Like 'Organization' And
Ordinal_Position <=5

--Build Select Statement
Set @Query = 'Select ' + @Columns + ' From Organization'

Exec SP_ExecuteSql @Query

The Final Output is :

SQL SERVER - How To Split Comma Separated String in to Separate records

Let us consider the temperatures of a city for one week and these are assigned to a String variable in the form of Comma Separated values as shown below.  

@Temperatures= ‘43,41,41,42,45,44,42’

Now we want to split these temperatures in to separate records. We can do this in many ways either by using Common Table Expression or by using the REPLACE() string function.

First Method : By using the Common Table Expression

Declare @Temperatures varchar(50)
Set @Temperatures= '43,41,41,42,45,44,42';

With Temperatures As(
Select Substring(@Temperatures,1,Charindex(',',@Temperatures)-1) FirstDay, Convert(Varchar(50), Substring(@Temperatures, Charindex(',',@Temperatures)+1, Len(@Temperatures))) NextDay
Union All
Select Substring(NextDay,1,Charindex(',',NextDay)-1) , Convert(varchar(50),Substring(NextDay,Charindex(',',NextDay)+1,Len(NextDay))+ ',') From Temperatures
Where Substring(NextDay,1,1)<> ',')

Select FirstDay Temperatures From Temperatures

Hmm..Seems to be a complicated code.

Ok, We will see another alternate method which is very simple.

Second Method : By Using the Replace() function.

Declare @SplitStr nvarchar(400)

Declare @Temperatures varchar(20)

Set @Temperatures= '43,41,41,42,45,44,42'  ;

Set @SplitStr = ' Select ''' +  Replace(@Temperatures, ',', ''' Temperatures UNION All Select ''') + ''''

Exec SP_ExecuteSql @SplitStr

Here I have used the Replace() function in order to replace the Comma with in the string with ' Union all Select '.

Finally, the end Result is :

SQL SERVER – Frequently Asked Interview Questions And Answers - II

What are the Different Data types  in SQL Server ?Different types of Data types :                                  Exact & Approximate Numerics :                            TinyInt, SmallInt, Int, BigInt, Numeric, Decimal, Float,Real,Bit

Character & Unicode Character Strings :                      Char, Varchar, text, nchar, nVarchar, ntext                     

Binary Strings :                                                           Binary, varbinary, Image                                                

Date and time :                                         SmallDatetime, Datetime                                            

Monetary :                                                            SmallMoney, Money                                               

Other Data Types :                                               Cursor, unique identifier, Timestamp, Rowversion, Sysname, table, sql_variant, xml,User Defined Data type

How many bytes will TinyInt, SmallInt, Int and BigInt  Occupy?                                                                                 TinyInt, SmallInt, Int and BigInt will Occupy 1,2,4,8 bytes  respectively.

What is the Difference between Numeric and Decimal?    In general,There is no difference between Numeric and Decimal. we can use both of them interchangeably to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Generally we will use this data type to control the accuracy of our calculations in terms of the number of decimal digits.

What is the Difference between Float and Real?            The only difference between float and real data types are their minimum and maximum values and their storage values. Float ranges from -1.79E + 308 to 1.79 + 308, while real ranges from     -3.40E + 38 to 3.40E + 38. The storage size of Float is 8 bytes while that of Real it is 4 bytes. The real data type is the same as float(24) — a floating data type with 24 digits to the right of the decimal point.

What is a Bit data type?                                                   Bit is used to store the logical information like True/False or  Yes/No. These Information will be stored as an Integer data type that can take a value of 1, 0, or NULL. Bit Occupies 1 byte for every 8 bit columns.

Difference Between Char/Varchar/nvarchar datatype? Char[(n)]     - Fixed-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes.
Varchar[(n)] - Variable-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length.                                                             nvarchar(n)  - Variable-length, Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length.                                                                     

What is the Difference between text and Image Data type?
Individual text, ntext, and image values can be a maximum of 2GB, which is too long to store in a single data row.All that is stored in the row is not the actual data but it is a pointer to the database pages that contain the Actual data. Coming to the difference between text and Image, Text data type is used for character data for storing more than 8000 characters. Image is used for binary large objects (BLOBs) such as digital images.

Name the Datatype which cannot be assigned to a table column.                                                              Cursor data type is the only system data type that cannot be assigned to a table column.

What is a Cursor? Is it a Data type or database object? 
Cursor data type is the only system data type that cannot be assigned to a table column in a CREATE TABLE statement. It can be used only with variables and stored procedure output parameters that contain a reference to a cursor. That means,  It is either declared with the FOR clause that will bind it to a SELECT statement or it will act as an alias for another cursor, with the SET statement.

What is UniqueIdentifier Data type?                            The UniqueIdentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites

What is a Table Data type?
The Table data type is an alternative way to create a temporary table. The variable declaration requires the column definition for the table and it creates a temporary table in tempdb.Table datatype is used to store a result set for later processing. We cannot use this datatype for a column in a CREATE TABLE statement. You should use DECLARE @local_variable to declare variables of type table. Table variables should be used instead of temporary tables, whenever possible, because table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined and these table variables require less locking and logging resources.

What is the difference between Timestamp and Rowversion?                                                                    Timestamp and Rowversion are two different names for the exact same data type. Timestamp is the synonym for the Rowversion data type. Microsoft deprecated timestamp data type and replaced the same with Rowversion In SQL 2005.In general These types has nothing at all to do with dates or times.Rowversion Is a data type that exposes automatically generated, unique binary numbers within a database and generally used as a mechanism for version-stamping table rows.The storage size is 8 bytes.

What is the difference between UniqueIdentifier  and Identity?

TIMESTAMP	UNIQUEIDENTIFIER
Size of TIMESTAMP value is 8 bytes	size of UNIQUEIDENTIFIER is 16 bytes
not based on system date or time	based on the computer's MAC addresses and system date time.
purpose to TIMESTAMP is to track the operation in tables on the database level	purpose is to have a unique value assigned to a row. It remains unique in any system in the world.
It should not be a primary key	it may be a primary key

What is SQL_variant Data type?                               Sql_variant data type is used to store values of various SQL Server supported data types, except text, ntext, timestamp, and sql_variant. The maximum length of sql_variant data type is 8016 bytes. You can store in one column of type sql_variant, the rows of different data types. For example in the Address column of sql_variant data type, we can store int, bigint, char and varchar values.

What is User defined data type?                                 User defined Data type can be used to create custom data types that are based on system data types using the system stored procedure sp_addtype.

What is Sysname datatype?                                               sysname data type is used for table columns, variables, and stored procedure parameters that store object names.Sysname is functionally the same as nvarchar(128) except that, sysname is NOT NULL.

What are the Different Types of Database Objects?Table, View, Procedure, User Defined Function, Trigger, Rule, Check Constraint, Default Constraint, PRIMARY KEY  Constraint, FOREIGN KEY Constraint, UNIQUE Constraint, Synonym, System Base Table

SQL SERVER – Frequently Asked Interview Questions And Answers - I

What is a Database?                                                           A database is a structured collection of information that is organized so that it can easily be accessed, managed, and updated. The information stored in a database has to continue to exist even after the application that saved and manipulated the data have ceased to run.

What is a Relational Database?                                             A Relational Database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. Each table contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns.

What is DBMS ?                                                                        DBMS is a set of Prewritten programs that controls the organization, storage, management, and retrieval of data in a database. DBMS are categorized according to their data structures or types. The DBMS accepts requests for data from the application program and instructs the operating system to transfer the appropriate data.

What is RDBMS ?                                                                     An RDBMS is a Relational Data Base Management System. which adds the additional condition that the system supports a tabular structure for the data with enforced relationships between the tables. Database in which all data is stored in Relations which are tables, with rows and columns. Each table is composed of records (called Tuples) and each record is identified by a field (called attribute) containing a unique value. Every table shares at least one field with another table in 'one to one,' 'one to many,' or 'many to many' relationships. These relationships allow the database users to access the data in almost an unlimited number of ways, and to combine the tables as building blocks to create complex and very large databases.

What is the Difference between DBMS and RDBMS?

DBMS	RDBMS
A Set of Prewritten Programs dedicated to control the data storage	A DBMS which Supports Tabular structure of Database. Only way to view the data is as a set of tables
Entity Based where all the tables are treated as different entities	Relation Based where tables are dependent and the user can establish various integrity constraints on these tables, so that the ultimate data used by the user remains correct
Single User system, Only one user can access one database at a time.	Multi-User system, Multiple users simultaneously access the same database
DBMS does not impose any constraints or security with regard to data manipulation It is the user's or programmer's  responsibility to ensure the ACID Properties of the database	RDBMS is more Secure than DBMS by defining the integrity constraints for the purpose of holding ACID Properties
Does not necessarily Support Client/Server Architecture & Distributed Databases	Supports Client/Server Architecture & Distributed Databases
Examples: FoxPro data files and earlier Ms Access	Examples: ORACLE, SQL Server and DB2

What is Normalization?                                                           A Relational database is basically composed of tables that contains Related data. So the Process of organizing this Related data into tables is referred to as Normalization. Alternatively, The process of refining tables, keys, columns, and relationships to create an efficient database is called Normalization.

What are the advantages of Normalization ?                  Normalization Used to reduce Data Redundancy and permits Simple and rapid retrieval of all necessary information. Some of the benefits of Normalization are:                           

• Data Integrity (As there is No Redundant Data)
• Optimized Queries (As Normalized tables produce rapid and efficient  Joins)                                               
• Faster Index creation and sorting (As the tables have fewer   Columns)                                      
• Faster UPDATE Performance (As there are fewer indexes per table)                                       
• Improved Concurrency(As table locks will affect less Data)
• Eliminates Redundancy

What are the Disadvantages of Normalization ?             As a result of Normalization, Data is stored in multiple tables.To retrieve or modify the information, we have to establish joins across multiple tables.Joins are expensive from I/O standpoint.  Also, Multi table Joins can have adverse impact on the performance of the system.

What are Different levels of Normalization?                     There are a few rules for database normalization. Each rule is called a "Normal form." If the first rule is observed, the database is said to be in First Normal Form. Similarly, If the first three rules are observed, then the database is considered to be in Third Normal Form. Although other levels of normalization are possible, Third normal form is considered to be the highest level necessary for most applications.

First Normal Form(1NF):    Remove Repetitive groups  from Record Design.For this,Create a separate Table for each set of Related data and Identify each set of Related data with a primary key.

Second Normal Form(2NF):  Each and every field is Functionally Dependant on Primary Key. Here, Create separate tables for sets of values that apply to multiple records and Relate these tables with a foreign key. Records should not depend on anything other than a table's primary key.

Third Normal Form(3NF): Transitive dependencies are removed from Record Design. For this,Eliminate fields that do not depend on the key.

Boyce-Codd Normal Form(BCNF): A relation is in Boyce/Codd Normal Form if and only if the only determinants are candidate key. (A determinant is any attribute on which some other attribute is (fully) functionally dependent.)

4th Normal Form(4NF): A table is in 4NF if it is in BCNF and if it has No multi-valued dependencies. 

5th Normal Form(5NF or PJNF): A table is in 5NF, also called "Projection-Join Normal Form", if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain/key Normal Form(DKNF): A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is DeNormalization and when would you go for it? DeNormalization is the Reverse process of Normalization. It allows the designers to keep redundant data in the system. This improves the query performance as the number of joins could be reduced. We will go for the DeNormalization, where the data does not change much frequently.

What are all the Basic things we need to observe before DeNormalizing a Database?                                                

• Be sure that we have a good Over all understanding of logical design of system
• Don’t make an attempt to DeNormalize entire Database at a time
• Determine whether we need virtual columns
• Understand Data Integrity issues & storage techniques for the data
• Determine the frequency at which data might change

What are the Essential DeNormalization Techniques?Keeping Redundant Data                                            Using Virtual Columns                                              Performing Horizontal Data Partition                     Performing Vertical Data Partition

What are Constraints? Explain Different types of Constraints?                                                                          Constraints enable the RDBMS to enforce the integrity of the database automatically, without creating the triggers, rule or defaults. Again,Constraints can defined as Restrictions Placed either at a Column level or Table level which ensures that the data meets certain Data integrity rules

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

What is  Entity Integrity Constraint?                               Entity Integrity Constraint ensures that each and every row should have a unique value for a column or combination of columns.

What is  Domain Integrity Constraint?                               Domain Integrity Constraint refers to a pool of Data, for a column of a table, from which the column will take its values.

Define Candidate key, Primary Key,  Alternate key and Composite key.                                                                        A Candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest all are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Define Foreign key                                                       When Primary key of one table appears as an attribute in another table, then it is called Foreign key in the second table.

What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys. Referential Integrity ensures that for every value of foreign key, there is a matching value of primary key.

What is the Difference between a Primary Key and a Unique Key?                                                                               Both primary key and Unique key enforce uniqueness of the column on which they are defined. The differences between these two are as shown below:

Primary Key	Unique Key
Does not  Allow Null Values	Allows Null Values
Only one Primary key exists in a table	More than one Unique key exists in a table
Creates a Clustered index on the column	Creates a Non Clustered Index on the column

What is DDL, DML, DCL and TCL Commands.                Data Definition Language(DDL): Data Definition Language statements are used to define the database structure or schema.
Ex : CREATE,ALTER,DROP,TRUNCATE,RENAME

Data Manipulation Language(DML) :  Data Manipulation Language statements are used for manipulate or manage the data within the schema objects
Ex : INSERT,UPDATE,DELETE

Data Control Language(DCL) :  Data Control Language statements are used to create roles, permissions, and referential integrity
Ex : GRANT,REVOKE

Transaction Control Language(TCL) : Transaction Control Language statements are used to manage different transactions occurring within a database.
Ex : COMMIT,SAVEPOINT,ROLLBACK

What is a Transaction?                                                              Transaction can be defined as a logical unit of work a grouping of one or more DBMS commands that are treated as a whole.

What are ACID Properties.                                                    Atomicity                                                       Consistency                                                         Isolation                                                               Durability

Explain Different Isolation Levels                                      An isolation level determines the degree of isolation of data between concurrent transactions.                                The Isolation levels are :

• Read Committed
• Read Uncommitted
• Repeatable Read
• Serializable