w3resource
SQL exercises

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. Write a query in SQL to display job ID, number of employees, sum of salary, and difference between highest salary and lowest salary for a job.

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)

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: Write a query in SQL to display the ID for those employees who did two or more jobs in the past.
Next: Write a query in SQL to display job ID for those jobs that were done by two or more for more than 300 days.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming