The relational model: tables, rows, columns

Discover how a database organizes information into tables, and create your first table in SQLite.

8 min · 3 questions

A database is a piece of software that stores information in an organized, lasting way, even when the computer is turned off. The most widespread model is the relational model: data is arranged into tables. A table looks like a spreadsheet: each column describes a property (a title, a price...) and each row represents a complete item (one specific book, for example). SQL (Structured Query Language) is the language used to create these tables and to query their contents. Each command you send to the database is called a query.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  genre TEXT,
  year INTEGER,
  price REAL
);

Let's break down this query. CREATE TABLE books creates a table named books. Inside the parentheses, each line declares a column: its name, then its type, meaning the kind of data it accepts. In SQLite, the main types are INTEGER (whole number), REAL (decimal number), TEXT (text) and BLOB (binary data such as an image). The absence of a value is written NULL. Finally, PRIMARY KEY designates the primary key: a column whose value uniquely identifies each row, like a case number.

INSERT INTO books (id, title, author, genre, year, price)
VALUES
(1, 'Dune', 'Herbert', 'sf', 1965, 9.9),
(2, 'Fondation', 'Asimov', 'sf', 1951, 8.5),
(3, '1984', 'Orwell', 'sf', 1949, 7.2),
(4, 'Croc-Blanc', 'London', 'aventure', 1906, 5.5),
(5, 'Les Robots', 'Asimov', 'sf', 1950, 7.9),
(6, 'Deux ans de vacances', 'Verne', 'aventure', 1888, 6.9),
(7, 'Le Petit Prince', 'Saint-Exupery', 'conte', 1943, 6.5);

The INSERT INTO query adds rows to the table: we'll cover it in detail in the last lesson. Notice that text values are wrapped in single quotes ('Dune'), never in double quotes. Now let's check that everything is in place with a read query: SELECT.

SELECT * FROM books;

You should see 7 rows and 6 columns: your first database works. Keep the CREATE TABLE + INSERT script from this lesson safe: every following lesson relies on this same books table. If you close your browser or your terminal, just run it again to start over from the same point.

Knowledge check

Make sure you remember the key points of this lesson.

1. In a table, what does a row represent?
   • A property, such as the title or the price
   • A complete item, such as one specific book
   • The data type of a column
2. Which SQLite type is suitable for storing a price like 6.5?
   • INTEGER
   • TEXT
   • REAL
   • BLOB
3. What is the purpose of the PRIMARY KEY clause on the id column?
   • To automatically sort the rows in alphabetical order
   • To uniquely identify each row
   • To forbid negative numbers