Sql Server Interview Questions and Answers

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]))