What a relational database is

Why it matters

Almost every app you use keeps its data in a database: the orders in a shop, the patients in a clinic system, the tickets in a help desk, the scores in a mobile game. Data analysts query those databases to answer questions, developers write code that reads and changes them, and database administrators keep them fast and safe. The language all of these people share is SQL (Structured Query Language, often said "sequel").

Before writing any SQL, it helps to know what you are talking to. This lesson explains the four ideas everything else builds on: tables, rows, keys and schemas.

A database is a set of tables

A relational database stores data in tables. A table looks like a spreadsheet sheet with strict rules:

• Each column has a name and a type, such as INTEGER, TEXT or REAL (a decimal number).
• Each row is one record: one customer, one order, one book.
• Every row has the same columns. A cell may be empty (SQL calls this NULL), but you cannot add an extra column to a single row.

Here is a small books table:

The word "relational" comes from mathematics: a table is a relation, a set of rows that all share the same shape. In practice it also hints at the real power of the model: tables can be related to each other.

Keys: how rows are identified and linked

A primary key is a column (or set of columns) whose value is unique for every row and never empty. In the table above, id is the primary key. Two books might share a title or a price, but never an id. When a program wants "book 3", it asks for the row whose id is 3.

A foreign key is a column in one table that stores the primary key of a row in another table. Imagine an orders table:

book_id is a foreign key pointing at books.id. Order 101 is for two copies of Dune. Instead of copying the title and price into every order, the database stores the book once and refers to it. If the price changes, it changes in one place. You will work with keys in depth in module 3.

The schema: the shape of the data

The schema is the description of the database: which tables exist, which columns each has, their types, and the rules (constraints) such as "id is the primary key" or "price cannot be empty". A schema is written in SQL too:

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

You will write your own CREATE TABLE statements in module 4. For now, the key point is that the schema is decided up front, and the database enforces it. That strictness is a feature: it keeps bad data out.

What SQL looks like

SQL is declarative: you describe the result you want, and the database works out how to get it. To ask for cheap books you write:

SELECT title, price
FROM books
WHERE price < 20;

You did not say "loop over every row and check the price". The database might scan the table, or it might use an index (a lookup structure, like the index at the back of a book) to jump straight to the answer. Either way, you get:

SQL keywords such as SELECT and FROM are not case-sensitive, but writing them in capitals is a common convention that makes queries easier to read. Statements end with a semicolon.

Database engines and dialects

SQL is a standard, but each database engine adds its own extras. Common engines are PostgreSQL, MySQL, SQL Server, Oracle and SQLite. The core you learn in this course (SELECT, WHERE, GROUP BY, JOIN, INSERT, UPDATE, CREATE TABLE) works almost the same everywhere. Function names and date handling differ more, and lessons point out the differences when they matter.

The exercises in this course run on SQLite, directly in your browser. SQLite is a full SQL database stored in a single file. It ships inside every smartphone and web browser, which makes it a good engine to learn on.

Relational is not the only kind

You will also hear about "NoSQL" databases, such as document stores (MongoDB) and key-value stores (Redis). They trade some of the relational rules for flexibility or speed in specific situations. Relational databases remain the default for business data, and SQL is one of the most requested skills in data and software job postings, so it is worth learning well.

Common mistakes

• Treating a table like a spreadsheet. Spreadsheets let you put anything anywhere. A table has fixed columns and one kind of thing per table: customers in one table, orders in another.
• Storing the same fact twice. Copying a customer's address into every order means the copies drift apart. Store it once and link to it with a key.
• Using names as identifiers. Names change and repeat. Use a dedicated key column such as id.

Quick recap

• A relational database stores data in tables of rows and typed columns.
• A primary key uniquely identifies each row; a foreign key points at a row in another table.
• The schema defines tables, columns, types and rules, and the database enforces it.
• SQL is declarative: you describe the result, the engine finds it.
• This course uses SQLite; the core SQL carries over to PostgreSQL, MySQL and others.

Try it

This lesson has no code exercise. Open the Do tab and take the quiz to check that the vocabulary has stuck before you write your first query in the next lesson.