>_ IzzyLab

Hands-on task

This task is step 2 of the course “Your first database”

Your first SELECT: a price list

easy~5 min

The manager needs a price list: the name and price of every product in the goods table of the /root/shop.db database.

Save them to /root/prices.txt: one line per product, name first, then price, separated by | — the way sqlite3 prints by default. There must be no other columns and no header line in the file. Any order of lines is fine.

What you'll practise:
  • sqlite3
  • select
  • choosing columns
  • output redirection

Topic breakdown

Data is read from a table with the SELECT command. After SELECT you write which columns you need, and after FROM — which table to take them from:

SELECT * FROM books;            -- all columns
SELECT title, year FROM books;  -- only two columns, in this order

* means "all columns", and everything after -- is a comment the database doesn't read. Every SQL command in the console ends with ; — without it sqlite3 decides the command isn't finished yet and shows the ...> prompt.

You can also run a query without entering the console — pass it as the second argument. Then, like any command's output, it can be sent to a file with >:

sqlite3 /tmp/library.db "SELECT title FROM books" > /tmp/titles.txt

By default sqlite3 prints each row of the result on its own line and separates column values with |.

What's checked

  1. File /root/prices.txt created
  2. Each line has exactly two values
  3. The file has the names and prices of all products

What it looks like