SQL Foundations

Data outgrows files the moment your questions start crossing separate pieces of data: "Which member borrowed which book?", "What changed this week?", "Who still has something due?" A database is organized storage that a program can ask questions of and change later, like a staffed library desk instead of a loose stack of notebooks. SQL is the language many databases use for those questions and changes.

Relational shape

A relational database is a database that stores data in connected tables, named grids where related facts live together. A row is one saved item in a table, and a column is one named fact each row can carry. Think of a table as a clipboard: one row per line, one column per labeled box.

A schema is the table plan: table names, column names, and rules for what belongs where, like the blank form taped above a clipboard before anyone fills in rows. A primary key is a column whose value uniquely identifies one row, like a library card number. A foreign key is a column that points at a primary key in another table, like writing that card number on a checkout slip.

Here is a tiny SQLite database that lives only for this run. SQLite is a small database engine, a program that stores tables and runs SQL, included with Python through the standard library sqlite3 module.

Two setup commands appear before the first real question: CREATE TABLE defines a table's columns, and INSERT adds rows. Column rules use INTEGER, whole-number storage, TEXT, text storage, and NOT NULL, a rule that the column must have a value. The final command uses SELECT, a SQL statement, one complete command sent to the database, that reads chosen columns; FROM names the table and ORDER BY sorts the answer rows.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);
 
INSERT INTO members VALUES
    (1, 'Mina', 'Mumbai'),
    (2, 'Ravi', 'Bengaluru'),
    (3, 'Noor', 'Delhi'),
    (4, 'Isha', 'Pune');
""")
 
rows = connection.execute("""
SELECT name, city
FROM members
ORDER BY name;
""")
 
for name, city in rows:
    print(f"{name} ({city})")
 
connection.close()

Isha (Pune)
Mina (Mumbai)
Noor (Delhi)
Ravi (Bengaluru)

The SQL keywords are conventionally uppercase so the shape is easy to scan. The table and column names stay lowercase because they are names you chose.

SELECT asks for rows

A query is a SQL statement that asks for data back, and a result set is the table-shaped answer the database returns.

The most common query is a SELECT statement. WHERE is a filter that keeps only rows whose yes-or-no test passes, like a door checklist for records.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    genre TEXT NOT NULL,
    pages INTEGER NOT NULL
);
 
INSERT INTO books VALUES
    (1, 'Practical Python', 'Python', 280),
    (2, 'Data Stories', 'Data', 210),
    (3, 'Small Data Projects', 'Python', 320),
    (4, 'Clean APIs', 'Web', 260);
""")
 
rows = connection.execute("""
SELECT title, pages
FROM books
WHERE genre = 'Python' AND pages < 350
ORDER BY pages;
""")
 
for title, pages in rows:
    print(f"{title}: {pages} pages")
 
connection.close()

Practical Python: 280 pages
Small Data Projects: 320 pages

Notice the division of labor. SQL filters and sorts the rows; Python prints the rows it receives. You do not load every book into Python and then hunt through it by hand.

Try it - change the question

Change the WHERE line, then run again. Try city = 'Pune', name < 'R', or remove the WHERE line and keep the ORDER BY.

Change rows

INSERT adds a new row, like writing a new line on the correct clipboard. UPDATE changes existing rows. DELETE removes rows.

For UPDATE and DELETE, build the habit now: use WHERE unless you truly mean "every row." Without it, SQL does exactly what you wrote.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);
 
INSERT INTO members VALUES
    (1, 'Mina', 'Mumbai'),
    (2, 'Ravi', 'Bengaluru'),
    (3, 'Noor', 'Delhi');
""")
 
connection.execute("""
INSERT INTO members (member_id, name, city)
VALUES (4, 'Isha', 'Pune');
""")
 
connection.execute("""
UPDATE members
SET city = 'Hyderabad'
WHERE name = 'Ravi';
""")
 
connection.execute("""
DELETE FROM members
WHERE name = 'Noor';
""")
 
rows = connection.execute("""
SELECT member_id, name, city
FROM members
ORDER BY member_id;
""")
 
for member_id, name, city in rows:
    print(f"{member_id}: {name} in {city}")
 
connection.close()

1: Mina in Mumbai
2: Ravi in Hyderabad
4: Isha in Pune

These four verbs cover the daily rhythm of SQL work: read rows, add rows, fix rows, remove rows. Real database systems add more machinery around safety and speed, but the first mental model is this small.

Connect tables

The real reason relational databases matter is that one table does not have to carry every fact. Members live in members, books live in books, and borrowing events live in checkouts.

A JOIN is a SQL operation that combines rows from tables by matching related columns. Reuse the pandas merge picture from Section 21: you are clipping cards together by matching IDs, not trusting that row positions line up.

A plain inner join keeps only rows where the match exists on both sides. The ON part tells SQL which values must match.

The FOREIGN KEY ... REFERENCES ... lines document the pointers between tables: each checkout member points to members.member_id, and each checkout book points to books.book_id.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);
 
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    genre TEXT NOT NULL
);
 
CREATE TABLE checkouts (
    checkout_id INTEGER PRIMARY KEY,
    member_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    due_date TEXT NOT NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);
 
INSERT INTO members VALUES
    (1, 'Mina', 'Mumbai'),
    (2, 'Ravi', 'Bengaluru'),
    (3, 'Noor', 'Delhi');
 
INSERT INTO books VALUES
    (10, 'Practical Python', 'Python'),
    (11, 'Data Stories', 'Data'),
    (12, 'Clean APIs', 'Web');
 
INSERT INTO checkouts VALUES
    (100, 1, 10, '2026-07-12'),
    (101, 2, 12, '2026-07-10'),
    (102, 3, 11, '2026-07-15');
""")
 
rows = connection.execute("""
SELECT members.name, books.title, checkouts.due_date
FROM checkouts
JOIN members ON checkouts.member_id = members.member_id
JOIN books ON checkouts.book_id = books.book_id
ORDER BY checkouts.due_date;
""")
 
for name, title, due_date in rows:
    print(f"{name} borrowed {title}, due {due_date}")
 
connection.close()

Ravi borrowed Clean APIs, due 2026-07-10
Mina borrowed Practical Python, due 2026-07-12
Noor borrowed Data Stories, due 2026-07-15

When a column name appears in more than one table, write table.column so SQL knows exactly which one you mean. member_id exists in both members and checkouts; members.member_id and checkouts.member_id remove the guesswork.

Reading SQL as a sentence

SQL looks less strange when you read it in chunks:

• SELECT members.name, books.title means "show these columns."
• FROM checkouts means "start from checkout rows."
• JOIN members ON ... means "attach matching member rows."
• JOIN books ON ... means "attach matching book rows."
• WHERE ... means "keep only rows that pass this condition."
• ORDER BY ... means "sort the result set."

You write SELECT first because SQL is designed for humans to see the requested columns first. The database engine still figures out an efficient way to collect, match, filter, and return the rows.

You now have the SQL foundation: tables, keys, reads, writes, filters, and joins. Next, Python & Databases turns this into application code with sqlite3, parameterized queries, SQL commands that receive values separately from the SQL text, and SQL injection, the bug where untrusted text gets treated as database code.