Some SQL queries which should be practiced before going to Interview - Part 2

Amatya
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 1592 red flag
Rating: 4 out of 5  
 1 vote(s)

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.

Introduction

Some SQL queries which should be practiced before going to Interview - Part 2

Background

Do refer the mentioned article for better approach


Describe the topic with code snippets

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.
Here we go- 

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.

Conclusion

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: Amatya Agyey
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)