SQL and databases · Lesson
Use COUNT, DISTINCT, MIN, MAX, AVG and SUM to turn many rows into a few meaningful summaries.
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.
COUNT(*) counts all rows; COUNT(anno) counts only non-NULL year values. They are equal in the starter database because all eight books have a year. The aggregate returns one summary row rather than one row per book.
SELECT COUNT(*) AS numero_libri FROM libri;Expected result: One row: numero_libri = 8.
SELECT DISTINCT genere shows each genre only once. COUNT(DISTINCT genere) instead returns the number of different genres. AS gives the calculated column a readable name.
SELECT COUNT(DISTINCT genere) AS generi_diversi FROM libri;Expected result: One row: generi_diversi = 5.
MIN(anno) gives the earliest publication year, MAX(anno) the latest and AVG(anno) the arithmetic mean of known years. The average can contain decimals: in SQLite ROUND(AVG(anno), 1) shows one decimal place.
SELECT MIN(anno) AS primo_anno, MAX(anno) AS ultimo_anno, ROUND(AVG(anno), 1) AS media_anni FROM libri;Expected result: One row: 1943, 1997, 1958.9 (approximately).
WHERE filters rows before COUNT counts them. Here we ask how many books were published after 1950 without listing them. To see which ones, replace COUNT with SELECT titolo, anno.
SELECT COUNT(*) AS libri_dopo_1950 FROM libri WHERE anno > 1950;Expected result: One row: libri_dopo_1950 = 4.
SUM adds numeric column values: adding publication years does not give a useful quantity. Instead, use a temporary dataset with the number of available copies of three books. The query does not change biblioteca.db.
WITH copie(quantita) AS (VALUES (2), (3), (1)) SELECT SUM(quantita) AS copie_totali FROM copie;Expected result: One row: copie_totali = 6.
You can request multiple aggregate functions in one SELECT. Do not confuse the result's row count (one) with the value returned by COUNT (eight). Other aggregates generally ignore NULL values; COUNT(*) still counts the rows.
SELECT COUNT(*) AS totale, MIN(anno) AS prima_pubblicazione, MAX(anno) AS ultima_pubblicazione FROM libri;Expected result: One row: total = 8, earliest year = 1943, latest year = 1997.
Write a query for each task. Run it here or in DB Browser; check columns, rows and conditions.
Count all books using COUNT(*).
Expected row count: 1
Count the distinct authors in the table.
Expected row count: 1
Find the earliest and latest publication years with MIN and MAX.
Expected row count: 1
Calculate the average publication year, rounded to one decimal place.
Expected row count: 1
Count how many books were published after 1950.
Expected row count: 1
Count the distinct genres in biblioteca.db.
Expected row count: 1
Count science fiction books.
Expected row count: 1
Sum the quantities (2, 3, 1) of the temporary dataset shown above.
Expected row count: 1
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.