>_ IzzyLab

Hands-on task

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

Book catalog: INSERT and SELECT

medium~10 min

The library database has an empty table books(id, title, pages). Add three books to it: (1,'Dune',412), (2,'Solaris',204), (3,'Neuromancer',271). Then select the titles of books shorter than 300 pages in alphabetical order and write them to /root/short_books.txt — one title per line, no headers or borders.

What you'll practise:
  • postgresql
  • insert
  • select
  • where
  • order by

Topic breakdown

INSERT adds rows, and several records can be inserted in one query — the tuples in VALUES are listed separated by commas, in the order of the table's columns. A selection is a projection (which columns to return), a filter (WHERE) and sorting (ORDER BY); for a text column, ascending order is alphabetical order. By default psql draws a table with a header and a summary line — for humans. To make the result usable in files and scripts, switch on tuples-only mode (-t) and unaligned output (-A).

What's checked

  1. books has three books
  2. data inserted correctly
  3. the file lists books shorter than 300 pages, alphabetically

What it looks like