w3resource

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:

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

Practice Online


Query Visualization:

Duration:

Query visualization of Display the department ID, full name, salary for highest salary drawar in a department - Duration

Rows:

Query visualization of Display the department ID, full name, salary for highest salary drawar in a department - Rows

Cost:

Query visualization of Display the department ID, full name, salary for highest salary drawar in a department - 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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

id | age
--------
0  | 25
1  | 25
2  | 23
SELECT age, count(age) 
  FROM Students 
 GROUP by age

Output:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

Ref: https://bit.ly/3zbLPQm