>_ IzzyLab

Hands-on task

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

INSERT: a new product

easy~5 min

A new product has arrived at the shop. Add it to the goods table (database /root/shop.db):

  • name — Thermos;
  • price — 18;
  • in stock — 15 items.

The database will give the new product its number (id) itself. Leave the other products alone.

What you'll practise:
  • sqlite3
  • insert
  • primary key

Topic breakdown

A new row is added with INSERT INTO. In the brackets after the table name go the columns you're filling in, and after VALUES — the values in the same order:

INSERT INTO books (title, year) VALUES ('The Hobbit', 1937);

The id column isn't listed here — and it doesn't need to be. If a column is declared as INTEGER PRIMARY KEY (a primary key — the row's unique number), SQLite puts the next free number into it by itself.

You can skip the column list altogether: INSERT INTO books VALUES (...), but then there must be as many values as there are columns in the table — otherwise you get the error table books has 3 columns but 2 values were supplied.

Text in SQL is written in single quotes. If you pass the query from the terminal, put the whole SQL in double quotes — then the single quotes inside won't get in the way:

sqlite3 /tmp/library.db "INSERT INTO books (title, year) VALUES ('Dune', 1965)"

What's checked

  1. The Thermos is added to goods once
  2. The new product has the right price and quantity
  3. The other products haven't changed

What it looks like