Hi guys, in this article I will talk about 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.
1) SQL Server query to split the Field into 2 Column.
This query the developer can use when the name consists of numeric value like Ram1, Ram2
SELECT Left(Lastname, 3) As characters, Right(Lastname, 1) As numbers
FROM [Amatya].[dbo].[tblUsers]
Note - First 3 Character will be in First Column and the Last One in another Column
2) SQL Server query to returns the index of First numeric in the column
SELECT PatIndex('%[0-9]%', LastName) from [Amatya].[dbo].[tblUsers]Note - The SQL Server (Transact-SQL) PATINDEX functions returns the location of a pattern in a string.
The search is not case-sensitive.
3) SQL server query to remove special characters from a string value.
Here all characters except 0-9, a-z and A-Z are removed and the remaining characters are returned.
Create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
with schemabinding
begin
if @s is null
return null
declare @s2 varchar(256)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
Example of how to use the function:
select dbo.RemoveSpecialChars('abc-123+ABC')Result:
abc123ABC
4) Queries to get the DepartmentName and the Employee in that Department
i.e Software | RamBabu,Sandhya,Amatya,Urvi,Shryansh
SELECT
DISTINCT P1.DepartmentName,
(SELECT FirstName +',' FROM [Amatya].[dbo].[tblUsers] WHERE DepartmentId = P1.DepartmentId FOR XML PATH(''))
FROM[Amatya].[dbo].[tblDepartments] P1
5) SQL query to fetch value in tblUsersDay but not in tblUsersNight
In SQL Server, PostgreSQL, and SQLite, this can be done using the except keyword as follows:
select * from tblUsersDay
except
select * from tblUsersNight;
6) Add the 3 more when Nmber column value is greater then zero otherwise add 2
update tblNum set Nmbr = case when Nmbr > 0 then Nmbr+3 else Nmbr+2 end;
7) Alpha-Numeric Sorting in SQL Server
SELECT Name
FROM [Amatya].[dbo].[tblTagNo]
ORDER BY
CAST(SUBSTRING(Name, 1,PATINDEX('%[A-Za-z]%', Name)-1) AS INT) DESC --First numeric sorting
,SUBSTRING(Name, PATINDEX('%[A-Za-z]%', Name),1) --Sort by Alphabets OR In Ascending
SELECT Name
FROM [PAMSVNEW].[dbo].[DemoTable]
ORDER BY
CAST(SUBSTRING(Name, 1,PATINDEX('%[A-Za-z]%', Name)-1) AS INT) --First numeric sorting
,SUBSTRING(Name, PATINDEX('%[A-Za-z]%', Name),1) --Sort by Alphabets
8) When we want to copy table from one Table to another new table
CREATE TABLE [dbo].[tblSubTaskSurveyEvalutaion]
(
ActivityTd int not null,
ID int IDENTITY(1,1) NOT NULL,
ActivityName nvarchar(max) Null
)
SET IDENTITY_INSERT tblSubTaskSurveyEvalutaion ON
INSERT INTO tblSubTaskSurveyEvalutaion(ActivityId,ID,ActivityName)
SELECT ActivityId,ID, ActivityName FROM tblSlippageSubActivity;
SET IDENTITY_INSERT tblSubTaskSurveyEvalutaion OFF
9) DateTime Example Modification in SQL Server.
SELECT A.*,CONVERT(VARCHAR(24),A.createdon ,113)as NewCreatedOn,CONVERT(VARCHAR(24),
A.createdon,103)as createddate,CONVERT(time(0),A.createdon,103)as createdtime from tblDetails A
where UID=@ID
10) SQL Server query to find the third highest Salary from Salary Table.
Select Max(Salary) from [Amatya].[dbo].[Salary] where Salary <
(Select Max(Salary) from [Amatya].[dbo].[Salary] where Salary <
(Select Max(Salary) from [Amatya].[dbo].[Salary]))
Hope it will help you guys in your preparation.
Your opinion and suggestion are welcome.