SQL and MySQL course Β· Module 11: Capstone project
The power of questions - JOIN and GROUP BY
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. Which kind of join will you use to show ALL readers, including those who borrowed nothing?
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):