You can find maximum salary for each
department by grouping all records by DeptId and then using MAX()
function to calculate maximum salary in each group or each department.
SQL Query:
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
This questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well. Here is the query
SQL Query:
SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;
In this query we have use RIGHT OUTER JOIN because we need name of department from Department table which is on right side of JOIN clause, even if there is no reference of dept_id on Employee table.
SQL Query:
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
This questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well. Here is the query
SQL Query:
SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;
In this query we have use RIGHT OUTER JOIN because we need name of department from Department table which is on right side of JOIN clause, even if there is no reference of dept_id on Employee table.
Comments
Post a Comment