SQL server tricky questions

1. Suppose we have EmpTbl as below

And Data like

Now we have queries like 

1.  Select maximum salary from EmpTbl

Ans. Select max(Salary) as Salary from EmpTbl

You will get the output like below

2. Select Salary and Name in Descending Order

Ans. 1st Way :

         Select max(salary) as Salary, Name from EmpTbl Group by Name order by Salary desc

         2nd Way : 

         Select Salary, Name from EmpTbl  Order by Salary desc

You will get the output like below

3. Select employee name with highest salary Department wise.

Ans. Select Salary, Name, Department from EmpTbl where Salary in (select max(Salary) from EmpTbl Group by Department)

You will get the output like below

4. Select the second highest Salary from the table.

Ans. 1st Way :

         Select Top(1) * from (select top(2) Salary from EmpTbl order by Salary desc) As T Order by               Salary asc 

         2nd Way :

         Select  Max(Salary) from EmpTbl where Salary <(Select Max(Salary) from EmpTbl)  

You will get the output like below

5. Select Sum of Salary Department wise.

Ans. Select Sum(Salary) as Salary, Department from EmpTbl Group by Department

You will get the output like below

6. Select Department whose Sum of Salary is greater than 25000.

Ans. Select Sum(Salary) as Salary, Department from EmpTbl Group by Department having Sum(Salary)>25000

You will get the output like below

7. Select the name of the employees whose Salary is between 10000 to 50000.

Ans. Select Name, Salary from EmpTbl where Salary between 10000 and  50000       

You will get the output like below.

8. Select Salary whose name starts with 'R'.
Ans. Select Name, Salary from EmpTbl where Name like 'R%'
You will get the output like below

9. Select the name of all employees as comma separated.

Ans. SELECT Name + ', ' From EmpTbl For XML PATH('')
You will get the output like below.

10. How to swap the department Asp.net and PHP?

Ans UPDATE EmpTbl 

   SET Department = CASE  

                WHEN Department = 'Asp.net' THEN 'PHP'  

                ELSE Case When Department= 'PHP' then 'Asp.net'  End 

                END Where Department='Asp.net' or Department='PHP'

11. Get the name of Employees whose salary is the same.

Ans. select e1.Salary,e1.Name from EmpTbl as e1,EmpTbl as e2

Where e1.Salary=e2.Salary and e1.EmpId!=e2.EmpId

12. How to find the duplicate records?
Ans. select EmpId,Name,Department,Salary,BirthDate ,Count(*) as Cnt
from EmpTbl Group by EmpId,Name,Department,Salary,BirthDate having Count(*)>1

13. Select employee names whose age is greater than 30.

Ans. select Name from EmpTbl  

where datediff(year,BirthDate, getdate()) >30 

14. Select the names of the employees whose Birthdate date is between 01/01/1988 to 01/01/1999.
Ans. SELECT DISTINCT Name FROM EmpTbl
WHERE BirthDate BETWEEN '01/01/1988' AND '01/01/1999'

15. What will be the output of the query
Select Null/0
Ans. NULL

16. What will be the output of the query
Select SUM(NULL)
Ans. Error

17.What will be the output of the query
Select 4 from EmpTbl
Ans.


18. What will be the output of the query
Select (1/2)*10

Ans. 0

19. How to fetch the first 10 characters of the string?
A. SELECT SUBSTRING(Name,1,10) AS EmployeeName FROM EmpTbl

20. How to add column LastName in EmpTbl?
Ans. ALTER TABLE EmpTbl ADD LastName nvarchar(max)

21. How to get the list of all the tables of a database?
Ans. SELECT * from sys.Tables