SQL and databases · Lesson
Sort books by year and author, choose how many results to display and learn why LIMIT needs an explicit ordering.
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.
SELECT without ORDER BY does not guarantee row order, even if results appear unchanged in our small database. ORDER BY anno ASC sorts books from oldest to newest. ASC (ascending) is optional.
SELECT titolo, anno FROM libri ORDER BY anno ASC;Expected result: 8 rows; Il piccolo principe (1943) first and Harry Potter e la pietra filosofale (1997) last.
DESC reverses the order. Try anno DESC to list newer books first. Sorting by year is different from sorting titles alphabetically.
SELECT titolo, anno FROM libri ORDER BY anno DESC;Expected result: 8 rows; Harry Potter… (1997) first and Il piccolo principe (1943) last.
You can list multiple columns separated by commas. SQL sorts by author first; when books share an author, title decides their relative order. For instance, George Orwell's two books are sorted by title.
SELECT autore, titolo FROM libri ORDER BY autore ASC, titolo ASC;Expected result: 8 rows sorted alphabetically by author and then title.
LIMIT 3 displays only the first three rows in the specified order. OFFSET 2 skips the first two and shows the following rows. Without ORDER BY, the meaning of 'first three rows' is not stable: always sort before selecting a subset.
SELECT titolo, anno FROM libri ORDER BY anno DESC LIMIT 3 OFFSET 2;Expected result: Three rows: Il Signore degli Anelli (1954), Fahrenheit 451 (1953), Io, Robot (1950).
Use clauses in the order SELECT … FROM … WHERE … ORDER BY … LIMIT …. Filter books first, sort the remaining books, and only then limit the rows shown.
SELECT titolo, anno FROM libri WHERE genere = 'Fantasy' ORDER BY anno DESC LIMIT 1;Expected result: One book: Harry Potter e la pietra filosofale (1997).
Write a query for each task. Run it here or in DB Browser; check columns, rows and conditions.
Show the title and year of all books, oldest first.
Expected row count: 8
Show the title and year of the three newest books.
Expected row count: 3
Show title and author, sorting by author and then by title.
Expected row count: 8
Show the Fantasy books, newest first.
Expected row count: 2
Find the earliest published book: show title and year.
Expected row count: 1
Sort books by year ascending, skip the first two and show the next three.
Expected row count: 3
Among books from 1950 onward, show the two oldest.
Expected row count: 2
Show the five newest books; break year ties using the title.
Expected row count: 5
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.