SQL Query interview question part 1

EmployeeID

EmpName

Gender

Address

Email

Designation

Salary

Employee Table



1. Ques: Write a query to find 3rd highest salary in employee table. 
Answer:
Select Min (salary) from (select distinct top 3 salary from employee order by salary desc) as emp
                                                 or

Select top 1 Salary as '3rd Highest Salary' from (select distinct  top 3 Salary from Employee order by Salary desc) a order by Salary asc


2. Ques: Write a query to find nth highest salary in employee table.
Answer
Select Min (salary) from (select distinct top nth salary from employee order by salary desc) as emp


3. Ques:Write a query to find 3rd lowest salary in employee table. 
Answer:
Select top 1 Salary as '3rd Lowest Salary' from (select distinct  top 3 Salary from Employee order by Salary asc) a order by  Salary desc


4. Ques:Write a query to find  duplicate records in employee table. 
Answer:
Select EmpName, Count(EmpName)as EmpRepeat from tblEmployee  Group by EmpName  having Count(EmpName) > 1

5. Ques: Write a query to Delete Duplicate Rows From employee table. 
Answer:
delete from tblemployee where EmployeeId not in  (Select MIN(EmployeeID) FROM tblemployee  group by  EmpName,Gender,Salary)  

6. Ques: Write a query to find the highest salary of an Employee?
Answer:
Select MAX(Salary) as Salary from tblemployee


7. Ques: Write a query to find the name of an employee whose name contains "Sa" and the length is greater than 6?
Answer:
Select EmpName from tblemployee  where EmpName Like '%Sa%' and Len(EmpName) > 6


8. Ques: Write a query to find list of all tables in a database ?
Answer:

                              Select * from sysobjects where  xtype='U'

                                

                                                          Or

                       SELECT * FROM INFORMATION_SCHEMA.TABLES 

9. Ques: How to reverse the gender of each employee in a employee table?
Answer:
Select EmpName, Case Gender When 'Male' then 'Female' else 'Male' End As Gender from tblemployee


10. Ques: Write a query to get all the employee details from the Employee table whose "EmpName" does not start with any single character between "b" and "p"?
Answer:
Select * from tblemployee where EmpName like '[^b-p]%'


11. Ques: List the employees whose salary is higher than the average salary with self 
Answer:
select  e1.employeeid,  from  employees e1,employees e2 group by e1.employeeid, e1.salary having   e1.salary > avg(e2.salary);