SQL Exercises: 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
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);
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
The said query in SQL that selects the "department_id", concatenated "first_name" and "last_name" as "Employee_name", and "salary" from the 'employees' table. It only selects the employees who have the maximum salary in their department. The subquery retrieves the maximum salary for each department, and the outer query filters the employees by comparing their salary with the maximum salary of their respective department.
Using JOINs and Subqueries:
SELECT a.department_id, a.first_name || ' ' || a.last_name AS Employee_name, a.salary FROM employees a JOIN ( SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id ) b ON a.department_id = b.department_id WHERE a.salary = b.max_salary;
Using a Window Function:
SELECT department_id, first_name || ' ' || last_name AS Employee_name, salary FROM ( SELECT department_id, first_name, last_name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees ) ranked WHERE rank = 1;
Using a Correlated Subquery with EXISTS:
SELECT a.department_id, a.first_name || ' ' || a.last_name AS Employee_name, a.salary FROM employees a WHERE EXISTS ( SELECT 1 FROM employees b WHERE b.department_id = a.department_id GROUP BY b.department_id HAVING MAX(b.salary) = a.salary );
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display the details of departments managed by Susan.
Next SQL Exercise: Employees who did not have any job in the past.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Is SQL syntax case sensitive?
The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. However in some setups table and column names are case-sensitive. MySQL has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook