Students, Staff and Pay per Department

Build a one-row-per-department report from two CTEs: majors counts students per major_department_id (students with no major are not counted), and staff counts instructors per department_id and averages their salary (rounded to 2 places). Join both to departments and return department (the name), students, instructors and avg_salary, sorted by students descending, then department. Aggregating inside the CTEs matters: joining the raw tables first would multiply the rows and inflate every figure.

Expected result:

+------------------+----------+-------------+------------+
| department       | students | instructors | avg_salary |
+------------------+----------+-------------+------------+
| Computer Science |        8 |           3 |      89500 |
| Mathematics      |        5 |           2 |      88750 |
| Biology          |        4 |           2 |      88000 |
| Business         |        4 |           2 |      92000 |
| English          |        4 |           2 |      81500 |
| Fine Arts        |        1 |           1 |      69000 |
+------------------+----------+-------------+------------+
6 rows

Hints4 available

Topics:

CTEs

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.