SQL for Everyone: Chapter 1, free to read

Chapter 1: What Is a Database?

Version: SQL · SQLite 3.45

Why a Database at All?

Almost every app on your phone keeps lists. A shop app keeps products and orders. A messaging app keeps contacts and messages. A university keeps students, courses and marks. The lists look simple on the screen, but behind the screen someone has to store them, find things in them fast, change one entry without breaking the others, and let many people use them at the same time.

You could keep a list in a spreadsheet, and for a birthday-party guest list a spreadsheet is perfect. Trouble starts when the list grows teeth:

• Two people edit at once. One person changes a price while another deletes the row. A spreadsheet file has no traffic controller; a database is built around one.
• The same fact lives in ten places. A customer changes address, and you must hunt down every copy. Miss one copy and the data disagrees with itself.
• Questions get specific. "Show every customer in Dhaka who ordered in March and spent over 5,000" is a painful afternoon of filtering by hand in a spreadsheet. In a database it is one short question.
• Size. A spreadsheet with two million rows crawls. Databases answer questions over millions of rows in a blink, because finding things fast is their whole job.

A database is a program that stores organised data and answers questions about it. The organising is done with tables, and the asking is done in a language called SQL (pronounced "sequel" or "S-Q-L" — both camps are loud, neither is wrong). SQL has been the standard way to talk to databases since the 1970s, and the same basic questions work in almost every database system you will ever meet.

This book teaches standard SQL using SQLite, a database that lives in a single ordinary file on your disk. No server, no accounts, no installation ceremony. You type questions, SQLite answers. What you learn transfers: the same SELECT you write here works, with small dialect differences, in the big systems used at work.

NOTE SQLite stores a whole database — every table — in one file, usually named with a .db ending. You can copy that file, back it up, or email it, and the database travels with it. Big systems like PostgreSQL and MySQL run as separate server programs instead; the SQL you speak to them is close kin to what you learn here.

Tables, Rows and Columns

A database holds one or more tables. A table stores one kind of thing: books, or employees, or products. Picture a table as a neat grid:

• A column is one kind of fact about the thing: a book's title, its author, the year it came out. Every value in a column is the same kind of fact, and the column has a name.
• A row is one complete item: one book, with its title, its author and its year sitting side by side.
• The column names across the top describe what each row means.

The parts of a table, using a tiny book collection:

Compare this with a spreadsheet. A spreadsheet cell can hold anything anywhere: a date in one row, a note in the next, a joke in the third. A database table is stricter, and the strictness is the point. The year column holds years, the title column holds titles, and that discipline is what lets the database search, sort and compare without guessing.

The Primary Key, in Plain Words

Rows in a table need a way to be told apart. Two books can share a title. Two employees can share a name. So tables usually carry an id column whose only job is identity: a number that belongs to one row and no other. That column is called the primary key.

Think of it as a roll number in a class. Names repeat; roll numbers do not. When later chapters connect tables — "this order belongs to that customer" — they connect by these id numbers, never by names. In the diagram above, id is the primary key of books : book 2 is Dune, always, even if another copy of Dune joins the table later as book 4.

SQLite treats a column declared INTEGER PRIMARY KEY kindly: if you do not supply a number when adding a row, it picks the next free one for you. Chapter 7 looks at keys and table design properly. For now, hold the simple picture: every row gets a number of its own.

Where SQL Runs All Day

You have used SQL today, almost surely, without seeing it. When a shopping app shows your orders, the app asks its database a question in SQL and builds the screen from the answer. When you search a library catalogue, check a bank balance, or scroll a feed, SQL questions fly behind the glass. The pattern never changes:

How a question travels from you to an answer:

The app does not open the data file and rummage. It sends a written question and receives rows. In this book you play the role of the app: you type the questions yourself, in a small program called the sqlite3 shell, and read the answers with your own eyes. That is the best way to learn what the apps are doing.

SQLite itself is probably already in your pocket. It is built into Android and iOS, into web browsers, and into a long list of desktop apps, where it quietly holds bookmarks, message histories, settings and saved games — always the same arrangement of tables, rows and columns, always answering the same kind of SQL questions. The big server databases at banks and airlines run the same play with heavier machinery. Learn the play here, on a file you own, and you have learned the shape of all of it.

Picture the small shop down the road keeping its stock list in a shared spreadsheet. It works in January. By June, two staff members keep private copies because the shared one is slow to open, the copies disagree about how many chargers are left, and nobody knows which copy the delivery boy updated. Nothing in that story is a spreadsheet failure — the tool did what it promises. The list simply outgrew "a grid a person reads" and became "data a business runs on". That crossing point, where a list needs a traffic controller, one agreed copy, and answers to specific questions on demand, is where a database stops being overkill and starts being the simpler tool.

Meet the sqlite3 Shell

The sqlite3 shell is a command-line program: you run it in a terminal, type SQL, and it prints answers. Check whether you already have it by typing this in a terminal:

sqlite3 --version

If a version line answers back (this book uses 3.45.1), you are ready. If not, download the free tools from sqlite.org — the "command-line tools" package for your system — and try again. On many Linux systems the package is named sqlite3 ; on a Mac it often comes preinstalled.

Start the shell by naming the file you want to work in:

sqlite3 mybooks.db

Three things happen. The file mybooks.db is created if it does not exist yet. The prompt changes to sqlite> , which means "I am listening". And from here, everything you type goes to this database until you leave with .quit .

Inside the shell there are two kinds of lines, and mixing them up is the first beginner stumble:

• SQL statements do the real work on tables. They end with a semicolon: SELECT * FROM books;
• Dot commands are orders to the shell program itself, not to the database. They start with a dot and take no semicolon: .tables , .quit , .help . GOTCHA If you press Enter and the prompt changes to ...> and stays there, the shell is waiting for the rest of an SQL statement — almost always a missing semicolon. Type ; and press Enter to finish it, or press Ctrl+C to cancel the half- typed statement and start again.

Before you ask any questions, set up the answer format this book uses, so your screen matches the pages:

.headers on
.mode box

.headers on prints column names at the top of every answer. .mode box draws the answer as a tidy box with lines around it. You type these two lines once per session; the shell forgets them when you quit.

Your First Table: books

Time to build something. A table is made with CREATE TABLE , followed by the table's name and, in parentheses, the columns with the kind of data each one holds. TEXT means words, INTEGER means whole numbers:

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

Read it slowly. The table is called books . It has four columns: id , a whole number that acts as the primary key; title , text that must always be present ( NOT NULL — a book without a title is not allowed in); author , text; and year , a whole number.

Run it, and the shell says… nothing at all. Silence is normal here. CREATE TABLE builds an empty table; there is no answer to print. To confirm the table exists, ask the shell to show the table's recipe with .schema :

.schema books

Output:

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

The shell repeats your recipe back, word for word. The table exists. It is also completely empty, which the next section fixes.

Putting Rows In

INSERT INTO adds rows. You name the table, list the columns in parentheses, then give the values in the same order. Text values wear single quotes; numbers do not:

INSERT INTO books (id, title, author, year) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien',
1937), (2, 'Dune', 'Frank Herbert', 1965), (3, 'Pride and Prejudice', 'Jane Austen', 1813);

This one statement adds three rows at once — three sets of values, separated by commas. Again the shell prints nothing: adding rows produces no answer table. So check the result with your first real question. SELECT means "show me", * means "every column":

SELECT * FROM books;

Output:

┌────┬─────────────────────┬────────────────┬──────┐
│ id │        title        │     author     │ year │
├────┼─────────────────────┼────────────────┼──────┤
│ 1  │ The Hobbit          │ J.R.R. Tolkien │ 1937 │
│ 2  │ Dune                │ Frank Herbert  │ 1965 │
│ 3  │ Pride and Prejudice │ Jane Austen    │ 1813 │
└────┴─────────────────────┴────────────────┴──────┘

There they are: three rows, four columns, headers on top because of the .headers on setting. You have a database. Everything else in this book is a refinement of what just happened: store rows in tables, then ask sharper and sharper questions about them.

PRO TIP When a statement prints nothing, do not guess whether it worked. Ask the database. A quick SELECT after a change is the habit that separates calm database work from hopeful database work.

Asking Two Small Questions

SELECT does not have to fetch every column. Name the columns you want, in the order you want them:

SELECT title, author FROM books;

Output:

┌─────────────────────┬────────────────┐
│        title        │     author     │
├─────────────────────┼────────────────┤
│ The Hobbit          │ J.R.R. Tolkien │
│ Dune                │ Frank Herbert  │
│ Pride and Prejudice │ Jane Austen    │
└─────────────────────┴────────────────┘

Same rows, narrower answer. Now add a condition. WHERE keeps only the rows that pass a test — here, books printed before 1900:

SELECT title, year FROM books WHERE year < 1900;

Output:

┌─────────────────────┬──────┐
│        title        │ year │
├─────────────────────┼──────┤
│ Pride and Prejudice │ 1813 │
└─────────────────────┴──────┘

One row survives: the Austen. The other two were read, tested, and left out of the answer. Filtering with WHERE is such a workhorse that Chapter 3 is devoted to it, and Chapter 2 first teaches SELECT in full. For now, notice the shape of every question you will ever ask: name the columns, name the table, then narrow the rows.

When SQLite Says No

Sooner or later you will type a table name slightly wrong. Do it now, on purpose, and meet the error while the stakes are low. There is no table called book — the real name is books :

SELECT title FROM book;

Output:

Parse error: no such table: book

Read errors literally; they are usually honest. This one says exactly what is wrong: SQLite looked for a table named book and did not find one. The fix is the one-letter difference. Nothing was harmed — a failed question changes nothing in the database — and the shell stays open, ready for the corrected line.

NOTE Error wording shifts a little between database systems and even between SQLite versions (older ones start with "Error:" instead of "Parse error:"). The useful part — no such table: book — stays stable. Read for that part first.

Looking Around: .tables and .schema

Two dot commands earn their keep in every session. .tables lists the tables in the current database:

.tables

Output:

books

And .schema , which you met above, shows how a table was created — the fastest way to remind yourself of column names before writing a question:

.schema books

Output:

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

When you sit down at an unfamiliar database, .tables then .schema on the interesting tables is the standard first minute. It is the database equivalent of reading the table of contents before the book.

Summary

• A database stores organised data in tables and answers questions about it; a spreadsheet is fine for small, single-person lists and strains under sharing, size and specific questions.
• A table holds one kind of thing. Columns are kinds of facts, rows are individual items, and the primary key is an id column that gives every row an identity of its own.
• Apps talk to databases by sending SQL questions and receiving rows back. In this book you send the questions yourself through the sqlite3 shell.
• In the shell, SQL statements end with a semicolon; dot commands like .tables and .schema start with a dot and control the shell itself.
• CREATE TABLE builds a table, INSERT INTO adds rows, and SELECT asks questions. Statements that change things print nothing, so verify them with a SELECT .

Try It Yourself

Work in the same mybooks.db session, with the books table from this chapter.

• Show only the title and the year of every book.

Answer

SELECT title, year FROM books;

Output:

┌─────────────────────┬──────┐
│        title        │ year │
├─────────────────────┼──────┤
│ The Hobbit          │ 1937 │
│ Dune                │ 1965 │
│ Pride and Prejudice │ 1813 │
└─────────────────────┴──────┘

• Show every column of the books published after 1900.

Answer

SELECT * FROM books WHERE year > 1900;

Output:

┌────┬────────────┬────────────────┬──────┐
│ id │   title    │     author     │ year │
├────┼────────────┼────────────────┼──────┤
│ 1  │ The Hobbit │ J.R.R. Tolkien │ 1937 │
│ 2  │ Dune       │ Frank Herbert  │ 1965 │
└────┴────────────┴────────────────┴──────┘

The Hobbit (1937) and Dune (1965) pass the test; Pride and Prejudice (1813) does not.

• Who wrote Dune? Ask for just the author of the book whose title is Dune .

Answer