SQL Query Interview Questions and Answers Part 2
9. Write a SQL query to get department wise no. of employees and total salary.
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.
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?
select * from employee where exists (select * from incentives)
12. Select EmpId of those employees who didn't receive incentives without using sub query?
select EmpId from employee
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.
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.
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.
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?
Select distinct a.EmpId, a.EmpName, a.Salary from employee a, employee a1
where a.Salary = a1.Salary and a.EmpId != a1.EmpId