>_ IzzyLab

Hands-on task

This task is step 9 of the course “Databases in an evening: SQLite, PostgreSQL, Redis”

Books and authors with a JOIN

medium~7 min

The library database has the tables authors(id, name, country) and books(id, author_id, title). For every book, output the pair author|title, sorted alphabetically by book title, into /root/catalog.txt (psql -tA format, fields separated by |). Authors without books don't go into the list.

Write the query in the file /root/books.sql in the Editor tab — a multi-line JOIN is easier to read there — and run it with psql.

What you'll practise:
  • join
  • inner join
  • foreign key
  • order by
  • psql -tA

Topic breakdown

Relational databases store data without repetition: a book doesn't hold the author's name but a reference to their row in another table (a foreign key). To get a readable result, the tables are joined with JOIN and a condition in ON — for each book the author row with the matching id is found. An inner join keeps only the pairs found on both sides, so an author without books won't make it into the output; if you needed them, you'd take an outer join (LEFT JOIN from authors). Table aliases shorten the query and remove the ambiguity of same-named columns like id.

What's checked

  1. author|title for all books, in alphabetical order of titles
  2. a query with a JOIN is in /root/books.sql

What it looks like