SQL Query Interview Questions and Answers Part 4

24. Write a SQL query to select all employees from Employee table who have a incentive record in incentives table.

Answer:

SELECT * from Employee a
where EXISTS
(SELECT * from Incentive b where  a.EmpId = b.EmpId)

25. Select the maximum salary from each department.

Answer:

select DeptId, max(Salary) as Salary from employee group by DeptId  

26. Write a query to find employees with age greater than 30.

Answer:

select * from employee where datediff(year,DOB, getdate()) > 30  

27. Write a query to fetch EmpName, Incentive from employee and incentives table for all employees even if they didn't get incentives and set incentive amount as 0 for those who didn't get incentives.

Answer:

Select EmpName, ISNULL(Incentive,0) from employee a left join incentives b on a.EmpId = b.EmpId

28. Write a query to fetch EmpName, Incentive from employee and incentives table for those who have incentives and incentive greater than 4000.

Answer:

Select EmpName, incentive from employee a inner join incentives b on a.EmpId = b.EmpId and Incentive > 4000

29. Write a query to fetch EmpName, Incentive from employee and incentives table for all those who got incentives.

Answer:

Select EmpName, isnull(Incentive,0) from employee a right join incentives b on a.EmpId=b.EmpId

30. Write a query to select details of employee who got the max incentives.

Answer:

select DeptName, (select ISNULL(max(Incentive),0) from incentives i where i.EmpId = e.EmpId) MaxIncentive from employee e