Project: a course report
SQL · Unit 5: Aggregates
- Full plan
- SQL
- Project
- 4 exercises
Another database, the school's, where last unit Julia showed up as "Failed" before anyone had graded her exam. Its grades table has one row for each student in each course, with their grade. A 6 passes.
Rose teaches Science, which is course 3, and asks for her class's report. The first result: four enrolled and three grades. Julia's still hasn't been entered.
SELECT COUNT(*) AS enrolled,
COUNT(grade) AS with_grade
FROM grades
WHERE course_id = 3;Returns
| enrolled | with_grade |
|---|---|
| 4 | 3 |
Exercises in this lesson
You do them in the app, which checks them on the spot and explains why.
1. Multiple choice
The three Science grades are 6, 8.5, and 6.5, and their average is 7. Someone suggests this query "so Julia isn't left out." What does it do to the class's average?
2. Put the lines in order
Before Science, a dry run with Math, which is course 1 and has all its grades. Put in order the query that gives how many grades there are and their average with two decimals. One line doesn't belong.
3. Write the code
The report for Science, course 3. Write a query with four columns, in this order: how many grades there are, the average rounded to two decimals, the highest grade, and the lowest. Nobody makes up a grade for Julia.
4. Write the code
The principal asks about the students who passed Math, course 1. A 6 or higher passes. Write a query with two columns, in this order: how many passed and the average of their grades, rounded to two decimals.
It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.