Min, max and group by

Another variation on the same story. Now we want to identify the top salary and the bottom one for each department in our firm.

We spice the SQL statement a bit add the requirement to show in a dedicated column the gap between max and min salary, displaying as column name a short label, and ordering the report accordingly to the gap:

select department_id as dep, max(salary) - min(salary) as gap,
  max(salary) as max, min(salary) as min
from employees
group by department_id
order by gap desc;

A fun basic book on SQL: Head First SQL.