SQL Queries asked during Interviews

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.