>_ IzzyLab

Hands-on task

This task is step 4 of the course “PostgreSQL: from your first database to transactions”

PostgreSQL: INSERT and SELECT

medium~10 min

In the shop database there is an empty table products(id, name, price). Insert three items: (1,'pen',50), (2,'lamp',150), (3,'desk',300). Then select the names of items more expensive than 100, alphabetically, and write them into /root/expensive.txt — one name per line, without the table header.

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

Topic breakdown

A multi-row insert is done with a single INSERT query where the tuples in VALUES are simply listed comma-separated — no separate query per row is needed; the order of values in a tuple must match the order of columns. A selection is built from a filter (WHERE) and a sort (ORDER BY), and for a text column the default sort (ascending) is exactly alphabetical order. Mental model: you must select exactly the column required, otherwise extra fields end up in the output. For the result to land in the file as a clean list without borders and headers, psql is run in tuples-only and unaligned mode (-tA).

What's checked

  1. three items inserted
  2. file has items over 100 alphabetically

What it looks like