Which Cities Each Department Reaches

For each department, count the distinct students who have ever enrolled in one of its courses (dropped rows count too) and the distinct home cities those students come from. Return department (the name, aliased), students and cities. Fine Arts has a course nobody takes and must appear with 0 and 0. Sort by students descending, then department.

Expected result:

+------------------+----------+--------+
| department       | students | cities |
+------------------+----------+--------+
| Mathematics      |       14 |      8 |
| Computer Science |       13 |      4 |
| English          |       12 |      6 |
| Business         |       10 |      5 |
| Biology          |        5 |      3 |
| Fine Arts        |        0 |      0 |
+------------------+----------+--------+
6 rows

Hints4 available

Topics:

Joins

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.