How Many Students Each Instructor Teaches

For every instructor, count how many different students are enrolled in any of their courses. Return first_name, last_name and students (the count). Instructors who teach no course, or whose courses have no enrollments, must appear with 0. A student taking two courses from the same instructor counts once. Sort by students descending, then last_name.

Expected result:

+------------+-----------+----------+
| first_name | last_name | students |
+------------+-----------+----------+
| Samuel     | Whitaker  |       14 |
| Helen      | Brooks    |       12 |
| Mei        | Chen      |       11 |
| Olivia     | Grant     |       10 |
| Grace      | Okafor    |       10 |
| Daniel     | Reyes     |        8 |
| Priya      | Natarajan |        6 |
| Amara      | Diallo    |        5 |
| Marcus     | Lindqvist |        4 |
| Tomasz     | Nowak     |        4 |
+------------+-----------+----------+
(showing 10 of 12 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.