SQL and databases · Lesson
Query biblioteca.db, choose columns and filter rows using comparisons and logical operators.
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.
SQL means Structured Query Language. It lets you query and manage relational databases. Open biblioteca.db in DB Browser for SQLite, choose Execute SQL, enter the query and run it. You can try the same commands in the lab on this page.
SELECT * FROM libri;Expected result: All 8 rows and all columns.
SELECT chooses which columns to display; FROM names the table. An asterisk (*) selects all columns. Commas separate column names; a semicolon ends the statement. A SELECT query does not modify the database.
SELECT titolo, autore FROM libri;Expected result: 8 rows with only the title and author columns.
WHERE returns only rows that satisfy a condition. Put text values inside single quotes: 'George Orwell'. You can combine WHERE with selected columns.
SELECT titolo, anno FROM libri WHERE autore = 'George Orwell';Expected result: Two books: 1984 and La fattoria degli animali.
Compare values with = (equal), <> (not equal), >, <, >= and <=. A book published in 1950 does not satisfy anno > 1950; use >= to include it. Without ORDER BY, result order is not guaranteed.
SELECT titolo, anno FROM libri WHERE anno > 1950;Expected result: 4 rows: Harry Potter…, Il Signore degli Anelli, Fahrenheit 451 and Il nome della rosa.
AND requires all conditions to be true; OR requires at least one; NOT negates a condition. Use parentheses when combining AND and OR to make the intended logic explicit.
SELECT titolo FROM libri WHERE genere = 'Fantascienza' AND anno > 1950;Expected result: One row: Fahrenheit 451 (Io, Robot was published in 1950).
OR combines two sets of results. NOT genere = 'Fantasy' excludes Fantasy books, as does genere <> 'Fantasy' when genere is not NULL.
SELECT titolo, genere FROM libri WHERE genere = 'Fantasy' OR genere = 'Fantascienza';Expected result: 4 rows: two Fantasy books and two science fiction books.
Write a query for each task. Run it here or in DB Browser; check columns, rows and conditions.
Show only title and genre for all books.
Expected row count: 8
Show the title and year of George Orwell's books.
Expected row count: 2
Show every column of books published after 1960.
Expected row count: 2
Show titles and years of books published before 1950.
Expected row count: 3
Show only the titles of Fantasy books.
Expected row count: 2
Show title, author and year for science fiction published after 1950.
Expected row count: 1
Show all books published before 1950 or after 1980.
Expected row count: 4
Show all books whose genre is not Narrativa.
Expected row count: 6
Show books by George Orwell published before 1948.
Expected row count: 1
Show title, author and year of books published from 1900 through 1999 inclusive.
Expected row count: 8
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.