SQL and databases · Lesson
Search for text fragments, select multiple values, use ranges and distinguish NULL from zero and the empty string.
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.
LIKE matches a text pattern. % means 'zero or more characters'; _ means 'exactly one character'. 'Il%' finds titles beginning with Il, while '%rosa%' finds a title containing rosa. In SQLite LIKE is usually case-insensitive for ASCII letters, not for all accented letters.
SELECT titolo FROM libri WHERE titolo LIKE '%rosa%';Expected result: One book: Il nome della rosa.
IN compares a field to a list of values: autore IN ('George Orwell', 'Isaac Asimov') is equivalent to an OR between two equalities. NOT IN excludes listed values. Enclose text values in single quotes.
SELECT titolo, autore FROM libri WHERE autore IN ('George Orwell', 'Isaac Asimov');Expected result: Three books: Orwell's two books and Io, Robot.
BETWEEN 1945 AND 1954 includes both 1945 and 1954. It is equivalent to anno >= 1945 AND anno <= 1954. NOT BETWEEN selects values outside the range; use parentheses when combining conditions if needed.
SELECT titolo, anno FROM libri WHERE anno BETWEEN 1945 AND 1954 ORDER BY anno;Expected result: Five books: 1945, 1949, 1950, 1953 and 1954.
NULL indicates a missing or unknown value. Do not use anno = NULL; use anno IS NULL. Use IS NOT NULL for present values. Every year is populated in biblioteca.db, so the query below returns no rows.
SELECT titolo FROM libri WHERE anno IS NULL;Expected result: No rows: all eight books have a year.
This query creates a temporary two-row dataset using WITH and VALUES: it exists only while the statement runs. One book has no year and is found using IS NULL. You can run it and return to libri without changing your database.
WITH esempi(titolo, anno) AS (VALUES ('Libro senza anno', NULL), ('Libro noto', 2001)) SELECT titolo FROM esempi WHERE anno IS NULL;Expected result: One row: Libro senza anno.
Write a query for each task. Run it here or in DB Browser; check columns, rows and conditions.
Find books whose title contains the word rosa.
Expected row count: 1
Show all books whose title begins with Il.
Expected row count: 3
Show all books by George Orwell or Isaac Asimov using IN.
Expected row count: 3
Show books published from 1945 through 1954 inclusive, oldest first.
Expected row count: 5
Find books that belong to neither Fantasy nor Narrativa using NOT IN.
Expected row count: 4
Find books by authors whose name contains Orwell, published before 1950.
Expected row count: 2
Show books whose year lies outside the inclusive 1950–1980 range.
Expected row count: 4
Find books with no year in biblioteca.db using IS NULL.
Expected row count: 0
Use the explanation's temporary dataset to find the book with a NULL year without changing libri.
Expected row count: 1
Show titles beginning with Il and published from 1940 through 1960 inclusive.
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.