me share share some of the Sql questions I have been asked during interviews.
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
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
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.
set EmpName = (CASE
WHEN EmpName='A' THEN 'B'
WHEN EmpName='B' THEN 'A'
Consider you have a table TblEmployee with
columns EmployeeName and Salary. Find the third largest salary.
Select min(Salary) from
(Select top 3 Salary From
TblEmployee order by
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
Find the Top 3 Students from Each Standard.
Select *,row_number () over (partition by Standard order by TotalMarks desc)
Select StudentId,Standard,TotalMarks From MyTEmpView where
6. Shadow copy using Sql
Write a query to create a clone of existing table without using Create
(Data will not be copied).
Select top 0 * into NewTable From
7. Delete Duplicate Records
What will be the query to delete duplicate records in a table.
from BookIssue b1
join BookIssue b2
on b1.id>b2.id and b1.Book=b2.Book
Incase table deosnt have identity column
Select distinct Book into #Temp from
Truncate Table BookIssue
Insert into BookIssue
Select Book From #Temp
Hope to see some good
comments, some questions from seniors.