Introduction to Databases and Tables

What problem does a database solve?

Imagine you are building an app for a library. You need to remember every book, who borrowed it, and when it is due back. You could keep all that in a text file or a list inside your program — but the moment you have thousands of books and hundreds of borrowers, that approach falls apart. Searching becomes slow, two people editing at once causes chaos, and a single typo can corrupt everything.

A database is software designed exactly for this job: storing data in an organised way so it can be searched, updated and protected reliably, even when many programs use it at the same time. This is the same idea behind almost every website and app you use. If you have read What Is an API, picture the database as the place an API reaches into when it needs real data.

Tables, rows and columns

The most common kind is a relational database, which stores data in tables. A table is like a grid:

• Each column (also called a field) describes one piece of information, such as title or author.
• Each row (also called a record) is one complete item — one book.

Here is a books table:

id	title	author	year
1	The Hobbit	J.R.R. Tolkien	1937
2	Matilda	Roald Dahl	1988
3	Holes	Louis Sachar	1998

The columns are id, title, author and year. There are three rows, one per book.

The primary key

Look at the id column. Its job is to give every row a value that is unique — no two books share the same id. A column used this way is called the primary key. It matters because titles can repeat (two different books could be called Holes), but the primary key never does. When your program says "give me book 2", the database can find exactly one row, instantly.

Asking questions with SQL

To talk to a relational database you use SQL (Structured Query Language). The most important command is SELECT, which reads data. Its basic shape is:

SELECT columns
FROM table
WHERE condition;

To get every column of every book:

SELECT * FROM books;

The * means "all columns". To get just the titles of books published after 1980:

SELECT title
FROM books
WHERE year > 1980;

WHERE is a filter — it keeps only the rows where the condition is true. For our table, that query returns Matilda and Holes. This is the same kind of conditional thinking you met in Making Decisions with If, now applied to whole tables of data.

Adding, changing and removing data

Three more commands cover the rest of the basics:

-- add a new row
INSERT INTO books (id, title, author, year)
VALUES (4, 'Wonder', 'R.J. Palacio', 2012);

-- change an existing row
UPDATE books
SET year = 2013
WHERE id = 4;

-- remove a row
DELETE FROM books
WHERE id = 4;

Notice how UPDATE and DELETE both use WHERE to say which rows to act on. Forgetting the WHERE on a DELETE would remove every row — a famous and painful beginner mistake.

Relationships: linking tables

Now suppose you also want to track who borrowed each book. You could add a borrower_name column to books — but if the same person borrows many books you would type their name over and over, and a single misspelling would break things. Instead, you make a second table and link them.

A members table:

id	name
10	Aisha
11	Ben

And a loans table that connects a book to a member:

id	book_id	member_id
1	2	10
2	3	11

Here book_id and member_id are foreign keys — they point at the primary key of another table. Loan 1 connects book 2 (Matilda) to member 10 (Aisha). The data lives in exactly one place, so there is nothing to keep in sync by hand. Pulling related data back together is done with a JOIN, the next thing to learn after this lesson.

A complete worked example

Most computers can run SQLite for free, and you can experiment online by searching for an "SQLite online" sandbox. Here is a full script that creates a table, fills it, and queries it:

-- 1. Create the table
CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  TEXT,
  author TEXT,
  year   INTEGER
);

-- 2. Add some rows
INSERT INTO books (id, title, author, year) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien', 1937),
  (2, 'Matilda',    'Roald Dahl',     1988),
  (3, 'Holes',      'Louis Sachar',   1998);

-- 3. Ask a question: modern books, newest first
SELECT title, year
FROM books
WHERE year > 1980
ORDER BY year DESC;

Reading the result: CREATE TABLE defines the columns and marks id as the primary key. INSERT adds three rows. The final SELECT filters to books after 1980 and ORDER BY year DESC sorts them newest-first, returning Holes (1998) then Matilda (1988).

Try it yourself

1. Filter differently. Change the WHERE to WHERE author = 'Roald Dahl'. What comes back?
2. Sort the other way. Swap DESC for ASC and watch the order flip to oldest-first.
3. Add a column. Recreate the table with an extra pages INTEGER column, insert page counts, then SELECT title FROM books WHERE pages < 300;.

Challenge: Build the two-table version. Create members and loans tables as shown above, insert a few rows, then write a query that selects all loans WHERE member_id = 10. Once that works, look up the SQL JOIN keyword and try to combine books, loans and members so the result shows the book title next to the borrower's name.