How Popular Is Each Major?

The registrar at Fairhaven University is sizing lecture rooms for next semester and needs to know how many students each major has to seat.

The student record has one row per person and no totals line, so five rows have to become one number per major. The majors are not kept in a list of their own anywhere in this database — the only place they exist is on the student rows themselves, spelled out in full as text.

students — one row per enrolled student. major is the subject they are reading for, as text. gpa is their grade point average out of 4.0.

The table already exists in the database — there is nothing to create or load.

Task: Write a query returning two columns, major and num_students, with one row for every major that at least one student is reading. A major nobody is reading produces no row at all rather than a row reading zero. Rows come back in alphabetical order of major.

Example output — shape only, on invented subjects and counts.