Databases

What Is Data

We learned earlier that data lives short term in Memory, and long term on the Disk.

But what is it?

If we want to be overly specific, it’s 0s and 1s representing high and low voltage used to convey information. But that’s not a useful answer for any of us.

If we want to be too generic, it’s the files a computer keeps in temporary and durable storage. Documents, spreadsheets, slide decks, audio, video, images, programs, etc.

Let’s focus on the form we’re unfortunately most used to. Spreadsheets.

Plans

id,name,monthly_price
1,Basic,49
2,Plus,89
3,Unlimited,129

Customers

id,first_name,last_name,email,phone,join_date,plan_id,status
1,Ronald,Appleseed,ronald@example.com,555-0101,2024-09-05,3,active
2,Sky,Scraper,sky@example.com,555-0102,2025-01-12,1,active
3,Jimothy,Pineapple,jimothy@example.com,555-0103,2025-03-30,2,paused
4,Dan,Ghost,dan@example.com,555-0104,2024-11-18,1,active
5,Simon,Says,simon@example.com,555-0105,2025-02-02,3,active
6,Harold,Motor,harold@example.com,555-0106,2024-07-21,2,cancelled
7,Auto,Mobile,auto@example.com,555-0107,2025-06-09,1,active
8,Sally,Seashell,sally@example.com,555-0108,2025-04-15,3,paused
9,Jill,Notjack,jill@example.com,555-0109,2026-01-07,2,active
10,Tommi,Best,tommi@example.com,555-0110,2026-05-30,1,active

Classes

id,title,instructor,start_time,capacity
1,Competitive Unit Testing,Dana,2026-07-22 18:00,20
2,Extreme Deployments,Leo,2026-07-22 19:00,15
3,Forgot About Git,Dana,2026-07-23 07:00,12
4,Proactive Rollbacks,Mira,2026-07-24 18:00,18
5,Merge Conflict Management,Leo,2026-07-24 07:00,10
6,Peaceful Pair Programming,Dana,2026-07-25 12:00,25

Enrollments

id,customer_id,class_id,signed_up_at,attended
1,1,1,2026-07-20 09:14,true
2,1,2,2026-07-20 09:15,false
3,2,1,2026-07-21 17:02,true
4,3,3,2026-07-21 20:41,true
5,2,4,2026-07-21 17:05,true
6,4,1,2026-07-20 10:30,true
7,4,5,2026-07-20 10:31,true
8,5,2,2026-07-22 08:00,true
9,5,5,2026-07-22 08:01,false
10,5,6,2026-07-22 08:02,true
11,6,3,2026-07-19 14:20,true
12,7,5,2026-07-23 11:45,true
13,7,1,2026-07-23 11:46,false
14,8,6,2026-07-23 19:10,true
15,8,5,2026-07-23 19:11,true
16,9,5,2026-07-24 06:30,true
17,9,2,2026-07-24 06:31,true
18,1,5,2026-07-24 06:35,true
19,4,6,2026-07-24 12:00,false
20,9,4,2026-07-24 12:05,true

What can we gather from this?

There are three plans: Basic, Plus, and Unlimited.

There are 10 customers. And every customer has a plan.

There are six classes.

There are 20 enrollments, each of which have a customer and a class.

How Is Data Served

We don’t want to manage moving the bits between Memory and the Disk ourselves. So we use dedicated programs called databases to provide us a more user-friendly interface to access and modify our data. The low level operations are still performed, but similar to how an automatic transmission car still shifts gears under the hood, the software abstracts it away from us as the user.

For many organizations and businesses, their database is a spreadsheet that’s stored on a shared cloud drive, or emailed around.

Sometimes you can reach incredible scale with a spreadsheet running behind the scenes. Level.fyi is a phenomenal case study in this. But like most businesses, they eventually hit bottlenecks and began adopting a Relational Database.

Structured Query Language

Structured Query Language (SQL) means many things. It is the language that is used to search (Query) Relational Databases.

Why do we call it Relational?

The real answer is that it comes from Relational Algebra and Calculus.

Let’s answer it by first looking at that same data when it is not Normalized.

first_name,last_name,email,phone,join_date,status,plan_name,monthly_price,class_title,instructor,start_time,capacity,signed_up_at,attended
Ronald,Appleseed,ronald@example.com,555-0101,2024-09-05,active,Unlimited,129,Competitive Unit Testing,Dana,2026-07-22 18:00,20,2026-07-20 09:14,true
Ronald,Appleseed,ronald@example.com,555-0101,2024-09-05,active,Unlimited,129,Extreme Deployments,Leo,2026-07-22 19:00,15,2026-07-20 09:15,false
Sky,Scraper,sky@example.com,555-0102,2025-01-12,active,Basic,49,Competitive Unit Testing,Dana,2026-07-22 18:00,20,2026-07-21 17:02,true
Jimothy,Pineapple,jimothy@example.com,555-0103,2025-03-30,paused,Plus,89,Forgot About Git,Dana,2026-07-23 07:00,12,2026-07-21 20:41,true
Sky,Scraper,sky@example.com,555-0102,2025-01-12,active,Basic,49,Proactive Rollbacks,Mira,2026-07-24 18:00,18,2026-07-21 17:05,true
Dan,Ghost,dan@example.com,555-0104,2024-11-18,active,Basic,49,Competitive Unit Testing,Dana,2026-07-22 18:00,20,2026-07-20 10:30,true
Dan,Ghost,dan@example.com,555-0104,2024-11-18,active,Basic,49,Merge Conflict Management,Leo,2026-07-24 07:00,10,2026-07-20 10:31,true
Simon,Says,simon@example.com,555-0105,2025-02-02,active,Unlimited,129,Extreme Deployments,Leo,2026-07-22 19:00,15,2026-07-22 08:00,true
Simon,Says,simon@example.com,555-0105,2025-02-02,active,Unlimited,129,Merge Conflict Management,Leo,2026-07-24 07:00,10,2026-07-22 08:01,false
Simon,Says,simon@example.com,555-0105,2025-02-02,active,Unlimited,129,Peaceful Pair Programming,Dana,2026-07-25 12:00,25,2026-07-22 08:02,true
Harold,Motor,harold@example.com,555-0106,2024-07-21,cancelled,Plus,89,Forgot About Git,Dana,2026-07-23 07:00,12,2026-07-19 14:20,true
Auto,Mobile,auto@example.com,555-0107,2025-06-09,active,Basic,49,Merge Conflict Management,Leo,2026-07-24 07:00,10,2026-07-23 11:45,true
Auto,Mobile,auto@example.com,555-0107,2025-06-09,active,Basic,49,Competitive Unit Testing,Dana,2026-07-22 18:00,20,2026-07-23 11:46,false
Sally,Seashell,sally@example.com,555-0108,2025-04-15,paused,Unlimited,129,Peaceful Pair Programming,Dana,2026-07-25 12:00,25,2026-07-23 19:10,true
Sally,Seashell,sally@example.com,555-0108,2025-04-15,paused,Unlimited,129,Merge Conflict Management,Leo,2026-07-24 07:00,10,2026-07-23 19:11,true
Jill,Notjack,jill@example.com,555-0109,2026-01-07,active,Plus,89,Merge Conflict Management,Leo,2026-07-24 07:00,10,2026-07-24 06:30,true
Jill,Notjack,jill@example.com,555-0109,2026-01-07,active,Plus,89,Extreme Deployments,Leo,2026-07-22 19:00,15,2026-07-24 06:31,true
Ronald,Appleseed,ronald@example.com,555-0101,2024-09-05,active,Unlimited,129,Merge Conflict Management,Leo,2026-07-24 07:00,10,2026-07-24 06:35,true
Dan,Ghost,dan@example.com,555-0104,2024-11-18,active,Basic,49,Peaceful Pair Programming,Dana,2026-07-25 12:00,25,2026-07-24 12:00,false
Jill,Notjack,jill@example.com,555-0109,2026-01-07,active,Plus,89,Proactive Rollbacks,Mira,2026-07-24 18:00,18,2026-07-24 12:05,true
Tommi,Best,tommi@example.com,555-0110,2026-05-30,active,Basic,49,,,,,,

Notice how much data is duplicated in each row. Let’s say Ronald wants to update his email. In the flat file, there are three rows that need changed. If he wants to add another class, then we have a fourth copy of his email. On the flip side, notice how Tommi’s row has empty entries because she hasn’t signed up for a class yet?

We solve this problem by Normalizing the data. That is a fancy way to say we reduce it down to something we can represent as related structures without duplication. In database terms we call these tables. In normal people words, we call them sheets.

So back to the original example.

A Plan consists of an id, name, and monthly_price.

CREATE TABLE plans (
    id            INTEGER PRIMARY KEY,
    name          TEXT,
    monthly_price INTEGER
);

CREATE TABLE is a pretty self-explanatory command, it creates a table.

Let’s look at some more examples.

A Customer consists of an id, first_name, last_name, email, phone, join_date, plan_id, status.

CREATE TABLE customers (
    id         INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name  TEXT,
    email      TEXT,
    phone      TEXT,
    join_date  DATE,
    plan_id    INTEGER REFERENCES plans(id),
    status     TEXT
);

A Class consists of an id, title, instructor, start_time, capacity

CREATE TABLE classes (
    id         INTEGER PRIMARY KEY,
    title      TEXT,
    instructor TEXT,
    start_time TIMESTAMP,
    capacity   INTEGER
);

An Enrollment consists of an id, customer_id, and class_id, signed_up_at, and attended.

CREATE TABLE enrollments (
    id           INTEGER PRIMARY KEY,
    customer_id  INTEGER REFERENCES customers(id),
    class_id     INTEGER REFERENCES classes(id),
    signed_up_at TIMESTAMP,
    attended     BOOLEAN
);

First, notice how every column in the sheet has a type next to it. Integers, text, timestamps, and booleans (true/false). This provides the Structure in SQL. The vocab word for these table definitions is Schema.

Second, notice how the tables are connected.

Customers has a column that refers to Plans. We call this a one-to-many relationship because a customer can only have one plan, but a plan can have multiple customers.

We can contrast that with enrollments which refers to classes and customers. A customer can have multiple classes, and a class can have more than one customer sign up for it. We call this a many-to-many relationship.

Thus making the database relational, because the different schemas can be connected to one-another.

Now let’s focus on the Query Language part of SQL.

SELECT * FROM plans;

id,name,monthly_price
1,Basic,49
2,Plus,89
3,Unlimited,129

Here we have two keywords, SELECT and FROM. We use SELECT to query (search) the data. We are instructing the database to select the rows we’re interested in.

We use FROM to pick the table(s) that we’re interested in.

Between the SELECT and FROM we specify the columns we want to look at. If we want all of them, we use a *, we call it a wildcard.

SELECT first_name, last_name, status
FROM customers
WHERE status = 'paused';

first_name,last_name,status
Jimothy,Pineapple,paused
Sally,Seashell,paused

Let’s look at another example.

Here we are querying the first_name, last_name, and status from the customers table.

But we also introduced a new keyword, WHERE. This lets us filter down the data with a conditional. In this case, we’re only interested in the row if the status column has a value of ‘paused’.

SELECT title, capacity
FROM classes
ORDER BY capacity DESC
LIMIT 3;

title,capacity
Peaceful Pair Programming,25
Competitive Unit Testing,20
Proactive Rollbacks,18

Third example. We like getting our reps in.

Here we’re querying the title and capacity from the classes table. But we have some new keywords.

ORDER BY, like it says, it lets us order the output by a specific column. DESC is short for descending order, we can also use ASC for the reverse.

LIMIT should be obvious, we’re limiting the number of results that come back.

SELECT customers.first_name, customers.last_name, plans.name
FROM customers
JOIN plans ON plans.id = customers.plan_id
WHERE customers.status = 'active';

first_name,last_name,name
Ronald,Appleseed,Unlimited
Sky,Scraper,Basic
Dan,Ghost,Basic
Simon,Says,Unlimited
Auto,Mobile,Basic
Jill,Notjack,Plus
Tommi,Best,Basic

This is where it gets interesting.

What if we want to look at multiple tables?

We can use the JOIN keyword to combine multiple tables. But you need an ON to accompany it.

In our example, we want the first name, last name, and plan name for every active customer.

In order to do this, we needed to combine rows where the id in the plan table matched the plan_id in the customers table.

SELECT customers.first_name, customers.last_name
FROM enrollments
JOIN customers ON customers.id = enrollments.customer_id
JOIN classes ON classes.id = enrollments.class_id
WHERE classes.title = 'Merge Conflict Management'
  AND enrollments.attended = true;

first_name,last_name
Dan,Ghost
Auto,Mobile
Sally,Seashell
Jill,Notjack
Ronald,Appleseed

See how we’re not just limited to one table to join on, we can stack multiple.

We’re also not limited to a single condition for the WHERE, we can use an AND to chain filters.

SELECT classes.title, COUNT(*) AS signups
FROM enrollments
JOIN classes ON classes.id = enrollments.class_id
GROUP BY classes.title
ORDER BY signups DESC;

title,signups
Merge Conflict Management,6
Competitive Unit Testing,4
Extreme Deployments,3
Peaceful Pair Programming,3
Forgot About Git,2
Proactive Rollbacks,2

What if we’re not interested in the individual rows, but how many of them there are?

We can use COUNT(). This is a function that counts how many rows come back. (*) is using the wildcard to count every row. If we named a column instead, it would only count the rows where that column has a value.

These other math functions SUM, AVG, MIN, and MAX work similarly.

AS lets us rename a column in the output (it does not modify the original data). Without it the header would be COUNT(*).

GROUP BY is what makes the count useful. It lets us bucket rows together by a column, in this case, the title column in the classes table. Without grouping the values, we would get back a 20, the total number of enrollments.

If any of y’all have had the unfortunate experience of making a pivot table, it’s the same idea.

Beyond SELECT

What if we want to add data?

INSERT INTO enrollments (customer_id, class_id, signed_up_at, attended)
VALUES (10, 5, '2026-07-21 10:32', false);

INSERT INTO tells the database that we want to create new records. It needs to be followed by a table so that the system knows where to put the new data.

After the table, we specify the columns we want to add to.

Then we follow up with VALUES where we specify exactly what data we want to insert.

What if we want to update it?

UPDATE customers
SET email = 'ronald@dullahan.io'
WHERE id = 1;

UPDATE picks the table. SET picks the column and its new value.

Back to the start of the last section. If Ronald needs to update his email, three rows need to be modified.

Here it’s one row with one command to update it.

How about deletes?

DELETE FROM enrollments
WHERE customer_id = 6;

DELETE works very similarly to a SELECT, except we’re removing data instead of searching it.

This warning applies to both of these. If you forget the WHERE, you’ve just updated or deleted every row in the table.

Can we change the structure of tables?

ALTER TABLE classes
ADD COLUMN room TEXT;

We can use ALTER TABLE followed by the table name.

Here we are adding a new text column named room.

If we don’t like what a column is called, we can change it.

ALTER TABLE classes RENAME COLUMN room TO classroom;

And we can also remove a column.

ALTER TABLE classes DROP COLUMN classroom;

We can also DROP entire tables

Note that different SQL systems have slightly different flavors of the syntax, but these are pretty consistent across all of them.

Takeaways

If your computer’s fan kicks on when you’re opening a spreadsheet, it’s probably time to migrate it over to a proper database.

Stay tuned for next time when we build software on top of data.