Welcome to Subscribe On Youtube
2853. Highest Salaries Difference
Description
Table: Salaries
+-------------+---------+ | Column Name | Type | +-------------+---------+ | emp_name | varchar | | department | varchar | | salary | int | +-------------+---------+ (emp_name, department) is the primary key for this table. Each row of this table contains emp_name, department and salary. There will be at least one entry for the engineering and marketing departments.
Write an SQL query to calculate the difference between the highest salaries in the marketing and engineering department. Output the absolute difference in salaries.
Return the result table.
The query result format is in the following example.
Example 1:
Input: Salaries table: +----------+-------------+--------+ | emp_name | department | salary | +----------+-------------+--------+ | Kathy | Engineering | 50000 | | Roy | Marketing | 30000 | | Charles | Engineering | 45000 | | Jack | Engineering | 85000 | | Benjamin | Marketing | 34000 | | Anthony | Marketing | 42000 | | Edward | Engineering | 102000 | | Terry | Engineering | 44000 | | Evelyn | Marketing | 53000 | | Arthur | Engineering | 32000 | +----------+-------------+--------+ Output: +-------------------+ | salary_difference | +-------------------+ | 49000 | +-------------------+ Explanation: - The Engineering and Marketing departments have the highest salaries of 102,000 and 53,000, respectively. Resulting in an absolute difference of 49,000.
Solutions
Solution 1: GROUP BY Clause
We can first calculate the highest salary for each department, and then calculate the difference between the two highest salaries.
-
# Write your MySQL query statement below SELECT max(s) - min(s) AS salary_difference FROM ( SELECT max(salary) AS s FROM Salaries GROUP BY department ) AS t;