w3resource

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 Expression: Display job ID, number of employees, sum of salary, and difference between highest and lowest salary for a job.

Relational Algebra Tree:

Relational Algebra Tree: Display job ID, number of employees, sum of salary, and difference between highest and lowest salary for a job.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display job ID, number of employees, sum of salary, and difference between highest and lowest salary for a job - Duration

Rows:

Query visualization of Display job ID, number of employees, sum of salary, and difference between highest and lowest salary for a job - Rows

Cost:

Query visualization of Display job ID, number of employees, sum of salary, and difference between highest and lowest salary for a job - 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.



Follow us on Facebook and Twitter for latest update.

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

 





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