Basic Join Part 2

Part of the Fundamentals section of Coddy's SQL journey. Lesson 44 of 72.

Joins can also be used on tables that we create. To combine nested tables with joins we need to add AS to identify the nested query with a name.

For example:

SELECT table1.col1, table2.col2, ...
FROM table1, (SELECT * FROM table) AS table2
WHERE table1.id = table2.id

Challenge

Easy

Available tables and columns:
grades: course_id, student_id, grade
students: id, name

Calculate for each student their average grade and return the name and the average grade for each.

Name the columns student, grade.

Round the average to 2 decimal places and sort the result by the average grade in ascending order.

Try it yourself

-- Fill this list with the two output columns, renamed as the task asks
SELECT ____
FROM students,
(
    -- Inside these brackets, produce one rounded average per person
    SELECT student_id, ____
    FROM grades
    GROUP BY student_id
) AS avg_grades
-- Now match the inner result back to the outer table on the shared id columns
WHERE ____
ORDER BY grade