SQL Query Interview Questions and Answers Part 3

17. Write a SQL query to find duplicate records in Employee table.

Answer:

SELECT EmpId, EmpName, Salary, COUNT(*) AS CNT  
FROM Employee GROUP BY EmpId, EmpName, Salary  
HAVING Count(*)>1  

18. Write a query to find second highest salary.

Answer:

Select max(Salary) from employee  
where Salary not in (Select max(Salary) from employee)  

19. Write a query to get employees whose Id is even.

Answer:

select * from employee where EmpId %2 = 0

20. Write a query to get employees whose ID is an odd number.

Answer:

select * from employee where EmpId %2 != 0

21. Create an empty table with same structure as some other table.

Answer:

SELECT * INTO NewEmp FROM Employee WHERE 1 = 0

22. Create a new table with data and structure copied from another table.

Answer:

SELECT * INTO NewEmp FROM Employee

23. Write a SQL query to get all the employees who are also managers.

Answer:

SELECT DISTINCT a.EmpName FROM Employee a
INNER JOIN Employee b
ON a.EmpId = b.EmpId