SQL: SELECT, JOIN and GROUP BY with worked examples

If you are in Year 5 at a technical institute with a computing specialisation (or in Year 4, depending on how the timetable is organised), from October to December you usually work on SQL: creating tables, inserting data, queries with JOINs and groupings. The topic has just started or is about to start, and the test or part of the exam that includes it falls five to nine weeks from now. From the ER diagram to tables (foreign keys, N:N relationships) and from JOINs to groupings are the points where I see most marks lost, so it is worth fixing the mental scheme now.

Don’t confuse it with the post on ER, the relational model and SQL for the university exam: that one starts from normalisation and modelling at university-course level. Here we stay on queries, with a small example you can redo by hand. If you are in Year 5, in the same period you also have systems and networks: the same rule applies, better to start early.

The example schema

Three tables, with the N:N relationship between students and courses resolved by the enrolments table, which contains the two foreign keys:

CREATE TABLE students (
  id         INT PRIMARY KEY,
  name       VARCHAR(30),
  class_name VARCHAR(5)
);

CREATE TABLE courses (
  id    INT PRIMARY KEY,
  title VARCHAR(30),
  hours INT
);

CREATE TABLE enrolments (
  student_id INT REFERENCES students(id),
  course_id  INT REFERENCES courses(id),
  grade      INT,
  PRIMARY KEY (student_id, course_id)
);

Data:

Note: Luca (id 4) has no enrolments. Keep an eye on him: he is the reason LEFT JOIN exists.

SELECT, WHERE, ORDER BY

The order in which you write a query is not the order in which it is executed. The logical one is: FROM (where from), WHERE (which rows), GROUP BY (how to group them), HAVING (which groups), SELECT (which columns), ORDER BY (in what order). Understanding this solves half the mistakes with HAVING.

SELECT name
FROM students
WHERE class_name = '5B'
ORDER BY name;

Result: Luca, Sara.

INNER JOIN and LEFT JOIN

To find out who got which grade in which course you need all three tables. The join condition links foreign key and primary key:

SELECT s.name, c.title, e.grade
FROM students s
JOIN enrolments e ON e.student_id = s.id
JOIN courses c    ON c.id = e.course_id
ORDER BY s.name, c.title;

Six rows, as many as the enrolments. Luca does not appear: JOIN on its own is an INNER JOIN, it keeps only the rows with a match. To keep the students with no enrolments too:

SELECT s.name, e.course_id
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.id
WHERE s.name = 'Luca';

Result: one row, Luca with course_id NULL. LEFT JOIN keeps all the rows of the left-hand table and fills with NULL where there is no match.

GROUP BY, aggregate functions, HAVING

GROUP BY forms a group for each value of the column indicated, and the aggregate functions (COUNT, SUM, AVG, MIN, MAX) work on each group. Every column in SELECT that is not inside an aggregate must appear in the GROUP BY.

WHERE and HAVING: WHERE filters the rows before grouping and cannot contain aggregates; HAVING filters the groups afterwards and can contain them.

Typical mistakes

• Forgetting the join condition and getting the Cartesian product (here $4\times 6=24$ rows instead of 6).
• Putting an aggregate in the WHERE (WHERE COUNT(*) > 1 is an error: you need HAVING).
• A column in SELECT that is not in the GROUP BY and not aggregated.
• COUNT(*) with LEFT JOIN: it also counts the NULL row, so Luca counts as 1 instead of 0. To count enrolments use COUNT(e.course_id).
• A condition on the right-hand table in the WHERE of a LEFT JOIN: it effectively turns the LEFT JOIN into an INNER JOIN, because the NULL rows are discarded.
• N:N with no bridge table, or a bridge with no composite primary key.

Exercise 1: average grade per course

For each course, the title, the number of students enrolled and the average grade rounded to two decimal places.

SELECT c.title, COUNT(*) AS enrolled, ROUND(AVG(e.grade), 2) AS average
FROM courses c
JOIN enrolments e ON e.course_id = c.id
GROUP BY c.title
ORDER BY c.title;

Calculations. Database: grades 8, 6, 9, sum 23, average $23/3=7.67$. Networks: 7 and 8, average 7.5. Python: only 7.

Exercise 2: students with their number of courses, even zero

List all the students with how many courses they take, including those who take zero.

SELECT s.name, COUNT(e.course_id) AS n_courses
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.id
GROUP BY s.name
ORDER BY s.name;

Calculations. Anna: 2 enrolments. Luca: no enrolments, COUNT(e.course_id) skips the NULL and gives 0. Marco: 1. Sara: 3.

Exercise 3: WHERE against HAVING

Students with at least two exams and an average grade of at least 7.5.

SELECT s.name, COUNT(*) AS exams, AVG(e.grade) AS average
FROM students s
JOIN enrolments e ON e.student_id = s.id
GROUP BY s.name
HAVING COUNT(*) >= 2 AND AVG(e.grade) >= 7.5
ORDER BY s.name;

Calculations. Anna: 2 exams, average $(8+7)/2=7.5$, passes. Marco: 1 exam, discarded. Sara: 3 exams, average $(9+8+7)/3=8$, passes. Luca does not appear (INNER JOIN).

Try rewriting the same request by putting COUNT(*) >= 2 in the WHERE: the database will give you an error. Understanding why is the quickest way to remember the difference.

Test yourself

There is not yet a dedicated computer science practice test. To train on the method (reading a text, translating it into steps, checking the calculations) you can use the maths and physics practice tests for the liceo. The best way to prepare for the exam is to rewrite the queries in the example by hand, predict the result before running them and then check. If you want to do it with me, I give one-to-one online lessons.