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.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
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
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion