Given a Employee table with three columns
ID, Salary, DeptID
10, 1000, 2
20, 5000, 3
30, 3000, 2
Department table with two columns:
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