SQL Subquery Exercises: Display the department ID, full name, salary for highest salary drawar in a department

SQL SUBQUERY: Exercise-54 with Solution

From the following table, write a SQL query to find those employees who earn the highest salary in a department. Return department ID, employee name, and salary.

Sample table: employees

Sample Solution:

SELECT department_id, first_name || ' ' || last_name AS Employee_name, salary 
	FROM employees a
		WHERE salary = 
			(SELECT MAX(salary) 
				FROM employees 
			WHERE department_id = a.department_id);

Sample Output:

department_id	employee_name	salary
90		Steven King			24000.00
60		Alexander Hunold	9000.00
100		Nancy Greenberg		12000.00
30		Den Raphaely		11000.00
50		Adam Fripp		8200.00
80		John Russell		14000.00
0		Kimberely Grant		7000.00
10		Jennifer Whalen		4400.00
20		Michael Hartstein	13000.00
40		Susan Mavris		6500.00
70		Hermann Baer		10000.00
110		Shelley Higgins		12000.00

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those departments managed by 'Susan'. Return all the fields of departments.
Next: From the following table, write a SQL query to find those employees who did not have any job in the past. Return all the fields of employees.