Solved exercise: JOINs, subqueries and GROUP BY in SQL

Modules below their programme's average in SQLite: JOIN, derived subquery, GROUP BY, HAVING and NULL handling, with an explained solution and tests.

Updated on November 10, 2026

Exam Code SQL Databases Exam level Tested code

Database Systems · Final examQuestion 2 · 2.5 points · 30 min

A university registry wants to find the modules that perform worse than their own degree programme. You will work in SQLite 3 on this schema: programmes(id, name), where name is unique; modules(id, name, programme_id), where each module belongs to exactly one programme; and enrolments(student_id, module_id, mark), with primary key (student_id, module_id). A mark of NULL means the student is enrolled but did not sit the assessment.

The input is an SQL script that creates the three tables and fills them with INSERT statements. Your solution is a single SELECT statement that runs against that data. Some programmes may have no modules, some modules may have no enrolments, and some modules may have enrolments where nobody sat.

Two averages are defined. The module average is the average of the non-null marks of that module's enrolments. The programme average is the average of all non-null marks of all enrolments in all of the programme's modules. Every mark carries the same weight; modules do not carry equal weight.

Return one row for each module that has at least one non-null mark and whose average is strictly less than its programme's average. The columns, in this order and with these names, are: programme (programme name), module (module name), enrolled (number of enrolments in the module, sat or not), sat (enrolments with a non-null mark), avg_mark (module average) and programme_avg (programme average). Display both averages with ROUND(…, 2).

The filter compares the unrounded values. Sort by programme ascending, then by the unrounded module average ascending and, if two modules have exactly the same average, by module ascending. The grader prints a header line with the column names and one line per row, with values separated by |. Reals are printed with two decimals, except integral reals, which are printed without decimals.

Sample input

CREATE TABLE programmes (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE modules (id INTEGER PRIMARY KEY, name TEXT NOT NULL, programme_id INTEGER NOT NULL REFERENCES programmes(id));
CREATE TABLE enrolments (student_id INTEGER NOT NULL, module_id INTEGER NOT NULL REFERENCES modules(id), mark REAL, PRIMARY KEY (student_id, module_id));
INSERT INTO programmes VALUES (1,'Computer Science'),(2,'Mathematics'),(3,'Physics');
INSERT INTO modules VALUES (1,'Databases',1),(2,'Programming',1),(3,'Networks',1),(4,'Operating Systems',1),(5,'Algebra',2),(6,'Calculus',2),(7,'Topology',2);
INSERT INTO enrolments VALUES (101,1,4),(102,1,6),(103,1,5),(104,1,NULL),(101,2,8),(102,2,7),(105,2,9),(106,2,6),(103,3,3),(107,3,7),(201,5,5),(202,5,6.5),(203,5,NULL),(201,6,4),(202,6,4.5),(204,6,9),(203,7,NULL),(205,7,NULL);

Expected output

programme|module|enrolled|sat|avg_mark|programme_avg
Computer Science|Databases|4|3|5|6.11
Computer Science|Networks|2|2|5|6.11
Mathematics|Algebra|3|2|5.75|5.80

Correct JOIN between programmes, modules and enrolments, with no duplicated or lost rows

0.5

Counts handle NULL correctly: COUNT(*) for enrolled students and COUNT(mark) for students who sat

0.5

Programme average computed in a subquery as a weighted average of all its marks, not as an average of averages

0.75

Filter placed in HAVING, with a strict comparison and modules without marks excluded

0.25

Columns, aliases, rounding and ordering exactly as the statement requires

0.5

Hints

Hint 1 · Why can't I compute both averages in the same GROUP BY?

If you group by module, AVG(e.mark) gives you that module's average. The programme average, however, needs the marks of other modules, and those rows are not in the group. You need a second aggregation at a different granularity: per programme. In SQL that means a subquery, either derived (in the FROM clause) or correlated.

Hint 2 · How do I count enrolled students and students who sat at the same time?

Aggregate functions ignore NULL arguments, but COUNT(*) counts rows. With an inner JOIN between modules and enrolments, each row in a group is one enrolment. So COUNT(*) gives the number enrolled and COUNT(e.mark) gives the number who sat. AVG(e.mark) already divides by the number of non-null marks only.

Hint 3 · Where does the filter go, and what happens to modules with no marks?

The filter compares an aggregate, so it belongs in HAVING, not in WHERE. If a module has no marks at all, its AVG is NULL, and any comparison with NULL is unknown, so HAVING drops that row without any extra condition. Compute the programme average over the join of modules and enrolments, grouped by programme_id.

Solution

Explained solution

The query aggregates at two levels. A derived subquery, pa, computes the weighted average of each programme once. The outer query joins programmes, modules and enrolments, groups by module, computes the counts and the module average, and keeps the modules that fall below pa.prog_avg.

The subquery is not joined directly to the outer query's enrolments. It is evaluated on its own, produces one row per programme, and only then is joined. No mark is counted twice.

SELECT p.name AS programme,
       m.name AS module,
       COUNT(*) AS enrolled,
       COUNT(e.mark) AS sat,
       ROUND(AVG(e.mark), 2) AS avg_mark,
       ROUND(pa.prog_avg, 2) AS programme_avg
FROM modules m
JOIN programmes p ON p.id = m.programme_id
JOIN enrolments e ON e.module_id = m.id
JOIN (SELECT m2.programme_id AS prog_id,
             AVG(e2.mark) AS prog_avg
      FROM modules m2
      JOIN enrolments e2 ON e2.module_id = m2.id
      GROUP BY m2.programme_id) pa ON pa.prog_id = m.programme_id
GROUP BY m.id, m.name, p.name, pa.prog_avg
HAVING AVG(e.mark) < pa.prog_avg
ORDER BY p.name, AVG(e.mark), m.name;

The subquery pa joins modules m2 with enrolments e2 and groups by m2.programme_id. AVG(e2.mark) runs over every mark in the programme, regardless of module, and skips NULLs. In the example, Computer Science has nine marks summing to 55, so prog_avg is 6.111…, displayed as 6.11. Mathematics has five marks summing to 29, giving 5.80. The two null marks in Topology do not count.

In the outer query, the JOIN with programmes p supplies the name, and the JOIN with enrolments e produces one row per enrolment. That is why Operating Systems, which has no enrolments, disappears at this step. It does no harm here, since it would have no average anyway. The last JOIN attaches each row's programme average. Because pa has exactly one row per programme, it does not multiply rows.

GROUP BY m.id, m.name, p.name, pa.prog_avg groups by module. Grouping on m.id prevents two modules with the same name from merging. The other three columns are listed because they appear outside aggregates, even though they depend functionally on m.id. For Databases, COUNT(*) gives 4 and COUNT(e.mark) gives 3, because student 104 did not sit. AVG(e.mark) is 15/3 = 5, printed as 5 because it is an integral real.

HAVING AVG(e.mark) < pa.prog_avg compares unrounded values. Programming (7.5) and Calculus (5.833… against 5.8) are excluded, and so is Topology, whose average is NULL. In the test "Strict comparison and programmes without marks", Art has average 6.75 and is the only module in History, so its average equals the programme's. The strict operator excludes it. Philosophy has a null prog_avg and produces no rows.

The ORDER BY uses AVG(e.mark), not the rounded column. The test "Ordering by the unrounded average" checks this: Zoology (4.33) and Botany (13/3 = 4.333…) both display as 4.33, yet Zoology comes first, although it would come second alphabetically. The tie-break on m.name applies only to exact ties, such as Databases and Networks in the example, both with average 5.

The test "Weighted average versus average of averages" separates a correct solution from one that only looks correct. In Chemistry, the average of module averages would be (2 + 6 + 5) / 3 ≈ 4.33, and Inorganic (5) would not qualify. Under the statement's definition the programme average is 48/9 ≈ 5.33, so Inorganic does appear, as the expected output shows.

Code and tests on GitLab

Test cases

Case	Input	Expected output	Actual output	Result
Statement example	CREATE TABLE programmes (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE); CREATE TABLE modules (id INTEGER PRIMARY KEY, name TEXT NOT NULL, programme_id INTEGER NOT NULL REFERENCES programmes(id)); CREATE TABLE enrolments (student_id INTEGER NOT NULL, module_id INTEGER NOT NULL REFERENCES modules(id), mark REAL, PRIMARY KEY (student_id, module_id)); INSERT INTO programmes VALUES (1,'Computer Science'),(2,'Mathematics'),(3,'Physics'); INSERT INTO modules VALUES (1,'Databases',1),(2,'Programming',1),(3,'Networks',1),(4,'Operating Systems',1),(5,'Algebra',2),(6,'Calculus',2),(7,'Topology',2); INSERT INTO enrolments VALUES (101,1,4),(102,1,6),(103,1,5),(104,1,NULL),(101,2,8),(102,2,7),(105,2,9),(106,2,6),(103,3,3),(107,3,7),(201,5,5),(202,5,6.5),(203,5,NULL),(201,6,4),(202,6,4.5),(204,6,9),(203,7,NULL),(205,7,NULL);	programme|module|enrolled|sat|avg_mark|programme_avg Computer Science|Databases|4|3|5|6.11 Computer Science|Networks|2|2|5|6.11 Mathematics|Algebra|3|2|5.75|5.80	programme|module|enrolled|sat|avg_mark|programme_avg Computer Science|Databases|4|3|5|6.11 Computer Science|Networks|2|2|5|6.11 Mathematics|Algebra|3|2|5.75|5.80	OK
Weighted average versus average of averages	CREATE TABLE programmes (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE); CREATE TABLE modules (id INTEGER PRIMARY KEY, name TEXT NOT NULL, programme_id INTEGER NOT NULL REFERENCES programmes(id)); CREATE TABLE enrolments (student_id INTEGER NOT NULL, module_id INTEGER NOT NULL REFERENCES modules(id), mark REAL, PRIMARY KEY (student_id, module_id)); INSERT INTO programmes VALUES (1,'Chemistry'); INSERT INTO modules VALUES (1,'Analytical',1),(2,'Organic',1),(3,'Inorganic',1); INSERT INTO enrolments VALUES (1,1,2),(1,2,6),(2,2,6),(3,2,6),(4,2,6),(5,2,6),(6,2,6),(2,3,5),(3,3,5);	programme|module|enrolled|sat|avg_mark|programme_avg Chemistry|Analytical|1|1|2|5.33 Chemistry|Inorganic|2|2|5|5.33	programme|module|enrolled|sat|avg_mark|programme_avg Chemistry|Analytical|1|1|2|5.33 Chemistry|Inorganic|2|2|5|5.33	OK
Ordering by the unrounded average	CREATE TABLE programmes (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE); CREATE TABLE modules (id INTEGER PRIMARY KEY, name TEXT NOT NULL, programme_id INTEGER NOT NULL REFERENCES programmes(id)); CREATE TABLE enrolments (student_id INTEGER NOT NULL, module_id INTEGER NOT NULL REFERENCES modules(id), mark REAL, PRIMARY KEY (student_id, module_id)); INSERT INTO programmes VALUES (1,'Biology'),(2,'Geology'); INSERT INTO modules VALUES (1,'Botany',1),(2,'Zoology',1),(3,'Genetics',1),(4,'Ecology',1); INSERT INTO enrolments VALUES (1,1,4),(2,1,4),(3,1,5),(1,2,4.33),(2,3,9),(3,3,9);	programme|module|enrolled|sat|avg_mark|programme_avg Biology|Zoology|1|1|4.33|5.89 Biology|Botany|3|3|4.33|5.89	programme|module|enrolled|sat|avg_mark|programme_avg Biology|Zoology|1|1|4.33|5.89 Biology|Botany|3|3|4.33|5.89	OK
Strict comparison and programmes without marks	CREATE TABLE programmes (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE); CREATE TABLE modules (id INTEGER PRIMARY KEY, name TEXT NOT NULL, programme_id INTEGER NOT NULL REFERENCES programmes(id)); CREATE TABLE enrolments (student_id INTEGER NOT NULL, module_id INTEGER NOT NULL REFERENCES modules(id), mark REAL, PRIMARY KEY (student_id, module_id)); INSERT INTO programmes VALUES (1,'History'),(2,'Law'),(3,'Philosophy'); INSERT INTO modules VALUES (1,'Art',1),(2,'Civil Law',2),(3,'Criminal Law',2),(4,'Ethics',3); INSERT INTO enrolments VALUES (1,1,6),(2,1,7.5),(3,2,3),(4,2,NULL),(3,3,8),(5,4,NULL);	programme|module|enrolled|sat|avg_mark|programme_avg Law|Civil Law|2|1|3|5.50	programme|module|enrolled|sat|avg_mark|programme_avg Law|Civil Law|2|1|3|5.50	OK
A single student	CREATE TABLE programmes (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE); CREATE TABLE modules (id INTEGER PRIMARY KEY, name TEXT NOT NULL, programme_id INTEGER NOT NULL REFERENCES programmes(id)); CREATE TABLE enrolments (student_id INTEGER NOT NULL, module_id INTEGER NOT NULL REFERENCES modules(id), mark REAL, PRIMARY KEY (student_id, module_id)); INSERT INTO programmes VALUES (1,'Architecture'); INSERT INTO modules VALUES (1,'Drawing',1),(2,'Structures',1); INSERT INTO enrolments VALUES (1,1,4),(1,2,6.5);	programme|module|enrolled|sat|avg_mark|programme_avg Architecture|Drawing|1|1|4|5.25	programme|module|enrolled|sat|avg_mark|programme_avg Architecture|Drawing|1|1|4|5.25	OK

Actual outputs: code compiled with SQLite 3.46.1 and run in an isolated container on 10 November 2026.

Complexity

Let P, M and E be the number of programmes, modules and enrolments. programmes.id and modules.id are INTEGER PRIMARY KEY, that is, the SQLite rowid, so each lookup by those keys costs O(log). The primary key of enrolments starts with student_id, so it cannot locate the enrolments of a given module. The planner's sensible choice is to scan enrolments in full and look up each module and its programme by rowid, which costs O(E log M) at each of the two levels.

SQLite has no hash joins. When a join has no usable index, it either runs a nested loop, O(M·E) in the worst case, or builds a transient automatic index, costing O(n log n) to build and O(log n) per probe. The latter is typical when joining the materialised subquery pa on prog_id. Grouping is done by sorting or with a temporary B-tree, O(E log E). The total is O(E log E). A hash join, with expected O(M + E), is an option in other engines such as PostgreSQL, not in SQLite.

For memory, the intermediate result pa takes O(P) and the outer groups take O(M), plus temporary sort space. If the query ran often on large tables, CREATE INDEX ix_enr_module ON enrolments(module_id, mark) would allow access by module and let AVG(mark) be computed from the index alone.

Common mistakes

• Computing the programme average as the AVG of each module's average. A module with one mark then weighs the same as one with fifty. This fails the Chemistry test.
• Using COUNT(e.mark) for the enrolled column or COUNT(*) for the sat column. With null marks, as in Databases or Civil Law, the numbers come out swapped or inflated.
• Putting the comparison with the average in WHERE. Aggregates do not exist yet at that stage and SQLite rejects the query with misuse of aggregate.
• Joining the enrolments table twice in the same query, once for the module and once for the programme, without a subquery. The row product multiplies the marks and the counts stop being correct.
• Sorting by the rounded avg_mark column or using <= in the filter. The first puts Botany before Zoology; the second lets Art through, although its average equals its programme's exactly.
• Believing you must add WHERE e.mark IS NOT NULL. With that filter, COUNT(*) no longer counts students who did not sit, and the enrolled column is wrong.

Variants

Solve it with a correlated subquery

Instead of the derived table, compute the programme average inside HAVING and in the select list with a subquery that references the outer query's m.programme_id. It is easier to read, but conceptually it is evaluated once per group. SQLite does not reuse the result across groups of the same programme, so with many modules it costs more than the derived version. It produces the same output on every test.

SELECT p.name AS programme,
       m.name AS module,
       COUNT(*) AS enrolled,
       COUNT(e.mark) AS sat,
       ROUND(AVG(e.mark), 2) AS avg_mark,
       ROUND((SELECT AVG(e2.mark)
              FROM enrolments e2
              JOIN modules m2 ON m2.id = e2.module_id
              WHERE m2.programme_id = m.programme_id), 2) AS programme_avg
FROM modules m
JOIN programmes p ON p.id = m.programme_id
JOIN enrolments e ON e.module_id = m.id
GROUP BY m.id, m.name, p.name, m.programme_id
HAVING AVG(e.mark) < (SELECT AVG(e2.mark)
                      FROM enrolments e2
                      JOIN modules m2 ON m2.id = e2.module_id
                      WHERE m2.programme_id = m.programme_id)
ORDER BY p.name, AVG(e.mark), m.name;

Also list modules where nobody sat

If the examiner asks for modules without marks to appear with a NULL avg_mark, switch to LEFT JOIN enrolments and replace COUNT(*) with COUNT(e.module_id), so that a module with no enrolments counts 0 rather than 1. The filter becomes HAVING AVG(e.mark) IS NULL OR AVG(e.mark) < pa.prog_avg, and the derived table must also be joined with LEFT JOIN, because a programme where nobody sat still has a row in pa but one with no marks at all does not.

What the professor will ask

“If I change the outer JOIN with enrolments into a LEFT JOIN, does the result change? And would COUNT(*) still be correct?”

For this query the result does not change. A module with no enrolments, such as Operating Systems, would enter its group with one row in which every column of e is NULL. Its AVG(e.mark) would be NULL and HAVING would discard it, so it never reaches the output. But COUNT(*) would give it 1 enrolled student, because it counts the NULL-padded row. If the statement asked to display it, as in the second variant, you would need COUNT(e.module_id). The lesson: LEFT JOIN together with COUNT(*) almost always hides a counting bug.

On the syllabus

Querying with joins, subqueries and aggregation is core SQL material in undergraduate database courses. The syllabus of Carnegie Mellon University's 15-445/645 Database Systems lists query languages, SQL among them, next to storage, indexing and transaction processing. The course page of MIT's 6.830 Database Systems places the subject within a broader study of how database systems are built, from storage to distributed and parallel databases. The cost discussion in this exercise, about joins, automatic indexes and sorting, connects to that systems view.

Briefly, in Mexico, Fundamentos de Bases de Datos at UNAM's Facultad de Ciencias includes SQL as a data definition and query language, and Bases de Datos at the Instituto Politécnico Nacional (ISC 2020) has a unit on SQL after relational algebra and calculus. This exercise is practice for that content; none of these institutions set it.

Opinion · If I were…

If I were a student revising for this exam, I would stop trusting the example. The version that averages the module averages prints exactly the same rows as the correct query on the statement's data. Only the Chemistry test exposes it.

So I would spend the last ten minutes writing my own small dataset: one module with many marks, one with a single mark, and check the programme average by hand. A query that matches the example has not been tested. It has been tested when the wrong version breaks and yours does not.

Related articles from LaRebelión

Solved exercise: dictionaries, sets and tuples in PythonSolved exercise: shopping receipt with classes and dunders in PythonSolved exercise: stack and recursive expression evaluator in Python

• Programming exercises
• Solved Exercise: Validate and Extract Data with Regex in Python
• Solved exercise: count, filter and summarize a log file in Bash

Article generated with AI.larebelion

Byline · Chief editor · English edition · London

“Matching the example proves nothing; Chemistry is where the average-of-averages query gets caught.”