SQL and databases · Lesson
Get statistics by genre and author, filter books with WHERE and filter groups with HAVING.
These lessons use biblioteca.db: a libri table with eight rows. In the lab you can try queries immediately and download the same database to continue with DB Browser for SQLite.
Experiment freely: use «Reset» whenever you want to return to the original eight books.
GROUP BY genere groups books sharing a genre. COUNT(*) calculates how many books belong to each group: the result has one row per genre, not per book. Add ORDER BY genere to make the displayed order stable.
SELECT genere, COUNT(*) AS quanti FROM libri GROUP BY genere ORDER BY genere;Expected result: Five groups: Distopia 1, Fantascienza 2, Fantasy 2, Giallo 1, Narrativa 2.
WHERE anno >= 1950 removes older books before forming groups. Compare this with the previous query: only three genres remain. WHERE cannot filter COUNT(*) results: HAVING is for that.
SELECT genere, COUNT(*) AS quanti FROM libri WHERE anno >= 1950 GROUP BY genere ORDER BY genere;Expected result: Three groups: Fantascienza 2, Fantasy 2, Giallo 1.
HAVING COUNT(*) >= 2 keeps only genres containing two or more books. WHERE genere = 'Fantasy' instead filters individual rows before grouping. Remember the clause order: FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
SELECT genere, COUNT(*) AS quanti FROM libri GROUP BY genere HAVING COUNT(*) >= 2 ORDER BY genere;Expected result: Three groups: Fantascienza, Fantasy and Narrativa (two books each).
GROUP BY autore finds authors who appear more than once. In this dataset only George Orwell has two books. Do not add titolo to a query grouped by author: a single title would not necessarily represent every book in the group.
SELECT autore, COUNT(*) AS numero_libri FROM libri GROUP BY autore HAVING COUNT(*) > 1;Expected result: One row: George Orwell, 2.
You can sort groups by COUNT(*) DESC; if genres have the same number of books, use genere ASC as a second sort key. LIMIT 2 shows the first two groups in that order.
SELECT genere, COUNT(*) AS quanti FROM libri GROUP BY genere ORDER BY quanti DESC, genere ASC LIMIT 2;Expected result: Two groups: Fantascienza and Fantasy, with 2 books each.
Write a query for each task. Run it here or in DB Browser; check columns, rows and conditions.
Count books by genre and sort genre names alphabetically.
Expected row count: 5
Count books from 1950 onward by genre, sorting by genre.
Expected row count: 3
Show the author and book count for authors who have written more than one book in the database.
Expected row count: 1
Show genre and book count only for genres with at least two books.
Expected row count: 3
Show genre and book count; sort by descending book count, then ascending genre, and display only the first two.
Expected row count: 2
Show the earliest and latest publication years for each genre.
Expected row count: 5
Among books published after 1950, show genre and book count for genres with at least two books.
Expected row count: 1
Count books by author; show only the three authors with the most books, breaking ties by name.
Expected row count: 3
Show genre and average publication year for genres whose average is greater than 1960.
Expected row count: 2
Show genre and book count only for genres represented by a single book.
Expected row count: 2
Choose «Check exercise» to verify your result. If you get stuck, use «Help me» for gradual hints without seeing the full solution immediately.
ConceptStep separates what is required from what is recommended or useful as reinforcement. Only required prerequisites block your progress.
Relationships: solid line = required · dashed line = recommended · dotted line = reinforcement.