Several steps

SQL · Unit 10: WITH and sets

Full plan
SQL
8 exercises

The school gives an award for the best average. Who gets it? First you need to know two things: what average each student has, and which of those averages is the highest. Then, whose it is.

The first is a step like last lesson's. In grades, each row carries the student's number, the course's number, and the grade. You get six averages. The 7 is Julia's, student 4, and the 8.33 is Megan's, student 5.

WITH per_student AS (
  SELECT student_id,
    AVG(grade) AS average
  FROM grades
  GROUP BY student_id
)
SELECT student_id,
  ROUND(average, 2) AS rounded
FROM per_student;

Returns

student_id	rounded
1	7.75
2	7.75
3	7.83
4	7
5	8.33
6	5.25

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Multiple choice
Julia, student 4, takes two courses. She has a 7 in Math, and her Science grade hasn't been entered yet. Where does her average of 7 come from?
2. Predict the output
The six averages are 7.75, 7.75, 7.83, 7, 8.33, and 5.25. grades has six grades below 7.5. What does this query return?
3. Find the bug
This query should return the school's highest average. Here SQL answers near "best": syntax error, and best is spelled right. Which line has the problem?
4. Predict the output
At this school, a 6 passes. Of the 14 rows in grades, 11 have a 6 or higher. Lucas, Emma, and Henry have two of those each, Megan has three, and Julia and Leo have one each. What does this query return?
5. Multiple choice
A three-step query returns a number you don't trust, and you suspect the second step, per_student. How do you look at the rows it left?
6. Complete the code
Besides the award, the school gives an honorable mention to anyone a point or less below the best average, which is 8.33. The six averages are 7.75, 7.75, 7.83, 7, 8.33, and 5.25. Fill in the blank in the ON's comparison.
7. Find the case that fails
Only students with all their grades entered can win the award. Today Megan, student 5, wins it with 8.33 across three courses. Julia, student 4, has a 7 in Math and Science pending. Leo, student 6, has a 4 and a 6.5. This query doesn't check whether a grade is missing. Which of these changes makes it give the award to the wrong person?
8. Write the code
The school wants to offer extra help to whoever has the lowest average. The first step is already there. Write a second step, worst, with the lowest average in a column value, and the query that returns one column, the student_id of whoever has that average. If two tie at the bottom, return both.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →