Students Above Their Major's Average

List the students whose gpa beats the average gpa of the students in their own major. Return major (the department's name, aliased), first_name, last_name and gpa. Sort by major, then gpa descending, then last_name. Students with no declared major have no group to compare against and are left out.

Expected result:

+------------------+------------+-----------+------+
| major            | first_name | last_name |  gpa |
+------------------+------------+-----------+------+
| Biology          | Harper     | Singh     | 3.55 |
| Biology          | Isabella   | Okoro     |  3.1 |
| Business         | Abigail    | Larsen    |  3.3 |
| Business         | Mason      | Nguyen    | 3.25 |
| Computer Science | Grace      | Lee       | 3.88 |
| Computer Science | Ava        | Patel     |  3.8 |
| Computer Science | Emily      | Zhang     | 3.72 |
| Computer Science | Liam       | Nguyen    | 3.45 |
| English          | Mia        | Haddad    |  3.8 |
| English          | Zoe        | Campbell  |  3.5 |
+------------------+------------+-----------+------+
(showing 10 of 13 rows)

Hints4 available

Topics:

Subqueries

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.