Top Sql Server Interview Questions and their Answers

1)  How to create an empty table emp1 with same structure as emp?

 Create table emp1 as select * from emp where 1=2;

2)  How to find the Max Salary from each department?

Select D.DepName, MAX(E.Salary) as Salary from tbl_Emp E  
     Inner Join tbl_Department D on E.Fk_DepId = D.DepId Group By D.DepName

3) Retreive year part from the table ..

Select DATEPART(YEAR,coldate) as year from tblA

Similarly for MONTH

Select DATEPART(MONTH,coldate) as year  from tblA

4) Get the difference of month of DateTime.

Select DateDiff(MM, colA, colb) from tblA

    Similarly for Days

Select DateDiff(DD, colA, colb) from tblA

5) Get all Employee details whose Joining Year is 2016.

Select * from tblA where DATEPART(YYYY, JoiningYear) = '2016'

6) Select all the Employees whose JoiningDate is JAN(1).

Select * from tblA where DATEPART(MM,JoiningDate) = '1'

7) Split Date, month, Year from DateTime
Here is query
You can achieve this task in two ways.
a) DATEPART
b) Year,Mont,Day
Let’s see the Query:
Split Year,Month and Day from DateTime Column in SQL Server
a )

Select DATEPART(YEAR,ColName) as [Year],DATEPART(Month,ColName) as [Month],DATEPART(Day,ColName) as date fromtbl_Name where ColName!=''

b)

Select YEAR(ColName) as [Year],Month(ColName) as [Month],Day(ColName) as date from tbl_Name  where ColName !=''

8) Function to Retrieve only character part from the given string in SQL Server

 Create FUNCTION RetrieveChar(@Val VARCHAR(1000))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNum VARCHAR(100)
SET @Count = 0
SET @IntNum = ''
WHILE @Count <= LEN(@Val)
BEGIN
IF SUBSTRING(@Val,@Count,1) <> '0' AND SUBSTRING(@Val,@Count,1) <> '1'
AND SUBSTRING(@Val,@Count,1) <> '2' AND SUBSTRING(@Val,@Count,1) <> '3'
AND SUBSTRING(@Val,@Count,1) <> '4'AND SUBSTRING(@Val,@Count,1) <> '5'
AND SUBSTRING(@Val,@Count,1) <> '6' AND SUBSTRING(@Val,@Count,1) <> '7'
AND SUBSTRING(@Val,@Count,1) <> '8' AND SUBSTRING(@Val,@Count,1) <> '9'
BEGIN
SET @IntNum = @IntNum + SUBSTRING(@Val,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNum
END
GO

You can call the function like – 

SELECT dbo.RetrieveChar('Amatya200Adi978tya');

The output will be – 
AmatyaAditya
9) Function to Retrieve only Integer part from the given string in SQL Server

Alter FUNCTION RetrieveInt(@Val VARCHAR(200))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNum VARCHAR(1000)
SET @Count = 0
SET @IntNum = ''
WHILE @Count <= LEN(@Val)
BEGIN
IF SUBSTRING(@Val,@Count,1) >= '0'
AND SUBSTRING(@Val,@Count,1) <= '9'
BEGIN
SET @IntNum = @IntNum + SUBSTRING(@Val,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNum
END
GO

You can call this function like – 

SELECT dbo.RetrieveInt('Amatya200Adi978tya');

The Output will be – 
200978