Write a SQL Query to find Max salary and Department name from each department.

Given a Employee table with three columns
ID, Salary, DeptID
10, 1000, 2
20, 5000, 3
30, 3000, 2

Department table with two columns:
ID, DeptName
1, Marketing
2, IT
3, Finance

Answer:
This is a trick question. There can be some department without any employee. So we have to ask interviewer if they expect the name of such Department also in result.

If yes then we have to join Department table with Employee table by using foreign key DeptID. We have to use LEFT OUTER JOIN to print all the departments.

Query would be like as follows:

SELECT d.DeptName, MAX(e.Salary)
FROM Department d LEFT OUTER JOIN Employee e
ON e.DeptId = d.ID
GROUP BY DeptName

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*