Databases and tables

Introduction

A database is an organised collection of data. A database management system (DBMS) is the program that stores the data and answers questions about it. SQLite, MySQL, PostgreSQL, Oracle and SQL Server are all DBMSs. Your bank, your university's results and every app on your phone keep their data in one.

Almost every application you use is a program in front of a database. Learning SQL means you can ask that data any question directly, without writing a new program each time.

How it works

Why not just a file?

A program could write its data to a text file. That works until:

• many people use the data at once (two bank tellers changing one account),
• the data must follow rules (no two students with the same ID, no negative balance),
• you want to ask new questions ("which students in Dhaka scored over 80?") without writing a new program each time,
• the computer crashes halfway through a change.

A DBMS handles all four, so programs do not have to.

Tables

Most databases are relational: the data is kept in tables (the theory calls them relations).

• Each column (an attribute) has a name and a type: year is a whole number, title is text.
• Each row (a tuple, or record) is one thing: here, one book.
• The table's design, its name and columns, is its schema. The rows in it right now are its instance: the schema rarely changes, the rows change all the time.

Syntax

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER);
INSERT INTO books (title, year) VALUES ('Clean Code', 2008);
SELECT title, year FROM books WHERE year > 2000;

Example

You talk to a relational DBMS in SQL (Structured Query Language, said "S-Q-L" or "sequel"). SQL is declarative: you say what you want, and the DBMS works out how to get it.

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

INSERT INTO books (title, author, year) VALUES
  ('The C Programming Language', 'Kernighan and Ritchie', 1978),
  ('Clean Code', 'Robert Martin', 2008),
  ('Atomic Habits', 'James Clear', 2018);

SELECT * FROM books;

SELECT title, year FROM books WHERE year > 2000;

Press Visualize, then step through. Each step runs one statement (statements end with ;). The Tables panel shows the whole database after each one; new rows are marked, and a query's result appears above the tables and in Output.

• CREATE TABLE makes an empty table with the columns you list.
• INSERT INTO adds rows.
• SELECT asks a question. It never changes the data; it returns a new table, the result.

SQL keywords can be written in any case (select works too), but most people write them in capitals so they stand out. Text values go in single quotes: 'Clean Code'.

Line by line

1. CREATE TABLE books makes an empty table with four columns, and id INTEGER PRIMARY KEY numbers the rows.
2. INSERT INTO books (title, author, year) VALUES ... adds three rows in one statement. The ids 1, 2 and 3 are given automatically.
3. SELECT * FROM books; returns every column of every row, so the first result has 3 rows.
4. SELECT title, year FROM books WHERE year > 2000; keeps only the books after 2000 and only two columns, so the second result has 2 rows.

Expected result

id  title                       author                 year
--  --------------------------  ---------------------  ----
1   The C Programming Language  Kernighan and Ritchie  1978
2   Clean Code                  Robert Martin          2008
3   Atomic Habits               James Clear            2018
(3 rows)

title          year
-------------  ----
Clean Code     2008
Atomic Habits  2018
(2 rows)

In the real world

University results systems, bank accounts, airline bookings and the contacts on your phone are all stored in databases like this. SQLite in particular is built into every Android and iPhone, and into most web browsers.

Common mistakes

• Thinking SELECT changes the data: it only returns a result.
• Writing text values in double quotes: SQL text goes in single quotes, such as 'Clean Code'.
• Forgetting the semicolon between statements.

Best practices

• Give tables and columns short, clear names in lower case.
• Write SQL keywords in capitals so that they stand out.
• Run a SELECT after every change while learning, to see what happened.

Summary

A database is an organised collection of data, managed by a DBMS. Relational databases store data in tables of rows and columns, and SQL is the declarative language used to create tables, add rows and ask questions.

Mark lesson complete