Let
me share share some of the Sql questions I have been asked during interviews.
1.
Random
Record
Select Random Records From Table
Select top 1 * from Table order by newId()
2. Max Amount without Order by
Consider a table having a column say amount, write a query to get maximum
amount without using order by and max keyword.
1.Declare @MaxValue int
Select @MaxValue=(case when @MaxValue>Amount then
@MaxValue else Amount end) From T1 Select @Maxvalue
2.Declare @MaxValue int
Select @MaxValue=Amount From T1 Group by Amount
Select @MaxValue
3.SELECT T11.Id,T11.Amount as MaxAmount
FROM T1 AS T11 left outER JOIN
T1 AS T12 ON T11.Amount < T12.Amount
GROUP BY T11.Amount, T11.Id
having COUNT(T12.Amount)=0
3.
Swap
Column Values
Consider you have table Say TblEmployee with a column say EmployeeName
which contain Records as A,B,A,A,B,C,B,C,A your task is to write a query which
will change the EmployeeName A to B and B to A.
update TblEmployee
set EmpName = (CASE
WHEN EmpName='A' THEN 'B'
WHEN EmpName='B' THEN 'A'
ELSE EmpName
END)
4.
Get the
nth Largest
Consider you have a table TblEmployee with
columns EmployeeName and Salary. Find the third largest salary.
è
1.
Select min(Salary) from
(Select top 3 Salary From
TblEmployee order by
Salary desc)
TempEmployee
2.
Select Top 1 EmployeeName,Salary from
(Select Top 3 EmployeeName,Salary
From TblEmployee order
by Salary desc) TblTempEmployee order
by Salary
5. Top N from Each Group
Consider a table Student with columns
StudenId,Marks,Standard.
Find the Top 3 Students from Each Standard.
With MyTempView
(
StudentId varchar(40),
Standard varchar(max),
TotalMarks int,
RowIndex int
)
as
(
Select *,row_number () over (partition by Standard order by TotalMarks desc)
)
Select StudentId,Standard,TotalMarks From MyTEmpView where
RowIndex<=3
6. Shadow copy using Sql
Write a query to create a clone of existing table without using Create
Command.
(Data will not be copied).
Select top 0 * into NewTable From
ExistingTable.
7. Delete Duplicate Records
What will be the query to delete duplicate records in a table.
1.
Delete b1
from BookIssue b1
join BookIssue b2
on b1.id>b2.id and b1.Book=b2.Book
2.
Incase table deosnt have identity column
Select distinct Book into #Temp from
BookIssue
Truncate Table BookIssue
Insert into BookIssue
Select Book From #Temp
Hope to see some good
comments, some questions from seniors.
Thanks.