w3resource

SQL SORTING and FILTERING on HR Database: Display job ID, number of employees, sum of salary, and 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, sum of all salary, and difference between the highest salary and lowest salary 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)

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: From the following table, write a SQL query to find those employees who worked more than two jobs in the past. Return employee id.
Next: From the following table, write a SQL query to find each job ids where two or more employees worked for more than 300 days. Return job id.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS