SQL and MySQL course Β· Module 11: Capstone project

The power of questions - JOIN and GROUP BY

2 min read Β·
In this lesson3

Data without questions is a pile of dead scrolls. The true power of the Library awakens only when we link shelves and count patterns. Let us recall the two pillars of this art.

JOIN - linking shelves

JOIN matches rows from two tables on a shared column. Most often we join a table by its foreign key. We write the matching condition after the ON keyword:

SELECT c.imie, w.data_wypozyczenia
FROM czytelnicy c
JOIN wypozyczenia w ON c.id = w.czytelnik_id;
  • INNER JOIN (the default JOIN) - returns only matched rows.
  • LEFT JOIN - keeps ALL rows from the left table, even if they have no match on the right (empty fields are filled with NULL).

GROUP BY - counting in groups

GROUP BY merges rows with the same value into one group, and aggregate functions compute something for each group:

SELECT czytelnik_id, COUNT(*) AS liczba
FROM wypozyczenia
GROUP BY czytelnik_id;

This query answers: "How many times did each reader borrow something?". The most common functions are COUNT(), SUM(), AVG(), MIN(), MAX().

JOIN + GROUP BY together

Combined, they produce reports - the heart of every library:

SELECT c.imie, COUNT(*) AS liczba
FROM czytelnicy c
JOIN wypozyczenia w ON c.id = w.czytelnik_id
GROUP BY c.id;

First we link the shelves, then we group and count. This is how knowledge is born from raw data!

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which kind of join will you use to show ALL readers, including those who borrowed nothing?

  2. 2. What do we write after the ON keyword in a JOIN?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a query that joins the czytelnicy (c) and wypozyczenia (w) tables on the condition c.id = w.czytelnik_id, then for each reader shows their imie and the number of loans as liczba. Group the results by c.id.

  • Code editor

    Write a query that joins the ksiazki (k) and kategorie (kat) tables on the condition k.kategoria_id = kat.id, then shows the category name (kat.nazwa) and the average price as srednia. Group the results by kat.id.

  • Vertical ordering

    Arrange the clauses of the reporting query in the correct order (top to bottom):

  1. QuestionWhich JOIN keeps unmatched left rows
  2. QuestionThe ON join condition
  3. Coding taskHow many loans each reader has
  4. QuestionAggregate function for counting rows
  5. Coding taskAverage book price per category
  6. Put in orderA report of readers' loans

Useful articles