Top Sql Server Interview questions and their answers with examples (Part 3)

Amatya
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 744 red flag

Hi friends, Here we will look into various Interview questions which are common and frequently asked in Interviews.
Hope it benefits for freshers and experienced.

Introduction

Hi friends, here we will look into an article for Interviews question which are common and frequently asked in Interviews.

Hope it benefits to all of you guys.

Background


Do refer the mentioned article for better approach

Describe the topic with code snippets

Hi guys, in this article we will look into some SQL Server queries which you should prepare before going to your interview. Basically while going to C#, ASP .Net interview SQL Server interview is common. So be prepare for SQL server queries.
Here we go- 

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 

  Hope it will help you guys in your preparation.

Conclusion

Future article will cover more SQL Server queries.
All the Best. Stay blessed.
Your opinion and suggestion are welcome.

Reference

From my notebook and learning experience

Page copy protected against web site content infringement by Copyscape

About the Author

Amatya
Full Name: Adit Gupta
Member Level: Silver
Member Status: Member
Member Since: 5/9/2015 12:56:12 AM
Country: India
Feel free to share informations 9731764134 mail Id ' adityagupta200@gmail.com Thanks
http://www.dotnetfunda.com
Software Enginner

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)