SQL Exercise: Difference between highest and lowest salary for a job
SQL SORTING and FILTERING on HR Database: Exercise-23 with Solution
23. From the following table, write a SQL query to count the number of employees, the sum of all salary, and difference between the highest salary and lowest salaries by each job id. Return job_id, count, sum, salary_difference.
Sample table: employees
Sample Solution:
SELECT job_id, COUNT(*), SUM(salary),
MAX(salary)-MIN(salary) AS salary_difference
FROM employees
GROUP BY job_id;
Sample Output:
job_id | count | sum | salary_difference ------------+-------+-----------+------------------- AC_ACCOUNT | 1 | 8300.00 | 0.00 ST_MAN | 5 | 36400.00 | 2400.00 IT_PROG | 5 | 28800.00 | 4800.00 SA_MAN | 5 | 61000.00 | 3500.00 AD_PRES | 1 | 24000.00 | 0.00 AC_MGR | 1 | 12000.00 | 0.00 FI_MGR | 1 | 12000.00 | 0.00 AD_ASST | 1 | 4400.00 | 0.00 MK_MAN | 1 | 13000.00 | 0.00 PU_CLERK | 5 | 13900.00 | 600.00 HR_REP | 1 | 6500.00 | 0.00 PR_REP | 1 | 10000.00 | 0.00 FI_ACCOUNT | 5 | 39600.00 | 2100.00 SH_CLERK | 20 | 64300.00 | 1700.00 AD_VP | 2 | 34000.00 | 0.00 SA_REP | 30 | 250500.00 | 5400.00 ST_CLERK | 20 | 55700.00 | 1500.00 MK_REP | 1 | 6000.00 | 0.00 PU_MAN | 1 | 11000.00 | 0.00 (19 rows)
Code Explanation:
The said query in SQL that aggregates data from the 'employees' table, grouping by the "job_id" column. It returns the values mentioned below for each group of employees with the same job_id:
COUNT(*): the number of employees in the group
SUM(salary): the total salary of all employees in the group
salary_difference: the difference between the highest and lowest salary in the group, calculated as MAX(salary) - MIN(salary).
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous SQL Exercise: Find employees who did two or more jobs in the past.
Next SQL Exercise: Jobs done by two or more for more than 300 days.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Difference between natural join and inner join
One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-
Consider:
TableA TableB +------------+----------+ +--------------------+ |Column1 | Column2 | |Column1 | Column3 | +-----------------------+ +--------------------+ | 1 | 2 | | 1 | 3 | +------------+----------+ +---------+----------+
The INNER JOIN of TableA and TableB on Column1 will return
SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1); SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+ | a.Column1 | a.Column2 | b.Column1| b.Column3| +------------------------+---------------------+ | 1 | 2 | 1 | 3 | +------------+-----------+----------+----------+
The NATURAL JOIN of TableA and TableB on Column1 will return:
SELECT * FROM TableA NATURAL JOIN TableB +------------+----------+----------+ |Column1 | Column2 | Column3 | +-----------------------+----------+ | 1 | 2 | 3 | +------------+----------+----------+
Ref: https://bit.ly/3AG5CId
- 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
- JavaScript String Exercises
- JavaScript HTML Form Validation
- 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