SQL Query Interview Questions and Answers Part 2

9. Write a SQL query to get department wise no. of employees and total salary.

Answer:

Select b.DeptName, count(a.EmpName), sum(a.Salary) from EmpName a , Department b where a.DeptId = b.DeptId group by b.DeptName

10. Write a SQL query to get department wise average salary.

Answer:

Select b.DeptName, avg(a.Salary) from EmpName a , Department b where a.DeptId = b.DeptId group by b.DeptName

11. Write a SQL query to select employee details from employee table if data exists in incentive table?

Answer:

select * from employee where exists (select * from incentives)

12. Select EmpId of those employees who didn't receive incentives without using sub query?

Answer:

select EmpId from employee
MINUS
select EmpId from incentives

13. Write a SQL query to select 30 % of salary from Satish, 10% of Salary for Ganesh and for other 15 % of salary from employee table.

Answer:

Select EmpName, CASE EmpName WHEN 'Satish' THEN Salary * .3 WHEN 'Ganesh' THEN Salary * .10 ELSE Salary * .15 END as Amount FROM employee

14. Select EmpName, Incentives from employee and incentives table for those employees who have incentives.

Answer:

Select a.EmpName, b.Incentives from employee a inner join incentives b on a.EmpId = b.EmpId

15. Write a SQL Query to select employee details whose department is not present in Department table.

Answer:

SELECT a.EmpId, a.EmpName, b.DeptId FROM employee a    
left outer join depatment b    
on a.DeptId = b.DeptId    
WHERE b.DeptId IS NULL

16. Can you write a query to select the list of employees with same salary?

Answer:

Select distinct a.EmpId, a.EmpName, a.Salary from employee a, employee a1  
where a.Salary = a1.Salary  and a.EmpId != a1.EmpId