Honour Roll From a View

Create a view called student_averages with one row per student who has at least one completed enrollment: columns student_id, completed (how many completed enrollments) and avg_grade (the average grade over those, rounded to 1 decimal). Then, in the same script, query the view: join it to students and return first_name, last_name, completed and avg_grade for everyone whose avg_grade is 85 or more, sorted by avg_grade descending, then last_name. Dropped and in-progress enrollments must not count.

Expected result:

Table student_averages
✓ student_id INTEGER
✓ completed (no type)
✓ avg_grade (no type)

+------------+-----------+-----------+-----------+
| first_name | last_name | completed | avg_grade |
+------------+-----------+-----------+-----------+
| Evelyn     | Park      |         2 |        96 |
| Sophia     | Rossi     |         2 |      92.5 |
| Grace      | Lee       |         3 |      92.3 |
| Emily      | Zhang     |         3 |      91.3 |
| Ava        | Patel     |         2 |        90 |
| Charlotte  | Dubois    |         3 |      87.7 |
| Mia        | Haddad    |         2 |      86.5 |
| Scarlett   | Johnson   |         2 |        86 |
+------------+-----------+-----------+-----------+
8 rows

Hints4 available

Topics:

Views & Indexes

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.