Hi friends, Here we will look into various Interview questions which are common and frequently asked in Interviews.
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
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