>_ IzzyLab

Hands-on task

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

Look inside the database

easy~5 min

You've been handed an online cookware shop. All its data lives in an SQLite database — a single file, /root/shop.db.

Open the database and look around: which tables it has and which columns the products table has. Find the column that stores a product's price and write its name to /root/answer.txt with a command like this: echo name > /root/answer.txt — put what you found in place of name.

What you'll practise:
  • sqlite3
  • .tables
  • .schema
  • tables and columns

Topic breakdown

A database keeps data in tables. A table is like a sheet in Excel: it has columns (each with a name and a type — a number, text…) and rows — the records themselves.

SQLite is the simplest kind of database: all of it lives in one ordinary file, and no separate server is needed. To work with it there's the sqlite3 console. Here's how to look around a library database:

$ sqlite3 /tmp/library.db
sqlite> .tables
books    readers
sqlite> .schema books
CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT, year INTEGER);
sqlite> .quit

sqlite3 file opens the database, and instead of the usual terminal prompt you see sqlite>: now the database console reads your commands. .tables shows the list of tables, .schema name shows how a table is built, and .quit takes you back to the terminal.

Commands that start with a dot are the console's own special commands; you write them without a ; at the end. .schema shows the command the table was created with: the brackets list the columns and their types — INTEGER (a whole number), TEXT (text). The words PRIMARY KEY next to id mean it's the row's unique number — more on that in the INSERT task.

What's checked

  1. File /root/answer.txt created
  2. The file holds the name of the price column from the products table

What it looks like