Department Pay Versus the College Average

Compare each department's instructors with the college as a whole. Return department (the name), instructors (how many work there), avg_salary (their average salary, rounded to 2 decimal places) and vs_college (that department's average minus the average salary of all 12 instructors, rounded to 2 decimal places). Sort by vs_college descending. The college-wide figure is one number for every row, so compute it with a subquery inside the select list.

Expected result:

+------------------+-------------+------------+------------+
| department       | instructors | avg_salary | vs_college |
+------------------+-------------+------------+------------+
| Business         |           2 |      92000 |       5500 |
| Computer Science |           3 |      89500 |       3000 |
| Mathematics      |           2 |      88750 |       2250 |
| Biology          |           2 |      88000 |       1500 |
| English          |           2 |      81500 |      -5000 |
| Fine Arts        |           1 |      69000 |     -17500 |
+------------------+-------------+------------+------------+
6 rows

Hints4 available

Topics:

Aggregates & GROUP BY

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.