What is a database?
Why a shared spreadsheet stops working, what a database is, what SQL is, and your first query in a real SQLite.
Data everywhere
Every time you send a message, pay with a card, play a song or sign up for an event, an app stores data: who you are, what you did, when and how much. And it doesn’t just store it: it searches it in milliseconds (your latest messages), cross-references it (the songs people like you enjoy) and protects it so two purchases at the same moment don’t sell the same last ticket.
Behind almost all of those apps there’s a database. In this course you’ll learn to design them well: what to store, how to organize it and how to relate information so it doesn’t turn into a mess.
When the spreadsheet is no longer enough
Imagine a small grocery store that records its sales in a shared spreadsheet:
| Date | Customer | Phone | Product | Price | Quantity |
|---|---|---|---|---|---|
| 10/02 | Ana Torres | 81 1234 5678 | Chiapas coffee 500 g | 189 | 2 |
| 10/05 | Ana Torres | 81 1234 5678 | Corn tortillas 1 kg | 28.50 | 3 |
| 10/07 | ana torres | 81 8765 4321 | Chiapas coffee | 189 | 1 |
| 10/09 | Luis Pérez | Black beans 900 g | forty-two | 1 |
It works… until it grows. Look at the problems:
- Repeated data: Ana’s name and phone are written on every sale. If she changes her number, it has to be fixed on every row.
- Data that doesn’t match: are “Ana Torres” and “ana torres” the same person? Which of her two phones is the right one? Is “Chiapas coffee” the same product as “Chiapas coffee 500 g”?
- No rules: nobody stopped someone from typing the price as
forty-two. Now the sum of sales fails or, worse, gives a wrong number. - Several people at once: if two employees edit the sheet at the same time, one can accidentally erase what the other wrote. And soon files like
sales_FINAL_v2_good.xlsxshow up. - Volume: with 200 rows everything is fine. With 2 million, opening and searching the sheet becomes painfully slow.
A database solves exactly these problems.
So, what is a database?
A database is a collection of data that is organized and stored so it can be queried and modified reliably, even by many people and programs at the same time.
Three ideas set it apart from a loose spreadsheet:
- Structure: before storing anything, you define what data it has and of what type. For example: a product has a code, a name, a price and a stock count, and the price is always a number.
- Rules: the database refuses to store what breaks them: a negative price, a repeated code or a product without a name.
- Queries: you ask it questions (“which products have fewer than 20 units?”) and it answers instantly, even with millions of records.
The program that manages the database, the one that stores the data, enforces the rules and answers the queries, is called a database management system (DBMS). There are many, and they don’t all organize data the same way: some store it in tables (MySQL, PostgreSQL, SQLite) and others in documents, key-value pairs or graphs (MongoDB, Firestore, Redis, Neo4j). Getting to know both families and choosing the right one for each project is exactly what you’ll see in module 2.
SQL: the language of databases
Database systems that store tables, called relational, understand SQL (pronounced “S-Q-L” or “sequel”). It’s a language for asking the database for things with almost-English sentences:
SELECT name, price FROM products;It reads as it’s written: “select the name and the price from the products”.
In this course we use SQLite, a small and widely used database system: it’s inside your phone, your browser and thousands of apps. What you learn with it works almost the same in MySQL or PostgreSQL.
A real SQLite, in your browser
The terminals in this course are not a simulation: each one is a real SQLite that runs only in your browser. You can write any query, even ones we haven’t covered yet, and you won’t break anything. If something goes wrong, press Reset and you’re back at the start.
Your first query
This terminal already has a database with the store’s inventory. When you see sqlite>, the terminal is waiting for an instruction.
First, ask it which tables it has with .tables. Commands that start with a dot are not SQL: they’re shortcuts of the SQLite terminal.
Then ask for all the products:
SELECT * FROM products;The asterisk * means “all the columns”. The semicolon ; at the end tells SQLite you’ve finished writing the instruction.
Try it: See which tables there are and then show all the products.
The answer comes back as a table: each row is a product and each column is a piece of data about that product (code, name, price…). You’ll look at these pieces carefully in the next lesson.
If you see ...>
If you press Enter without the ;, SQLite thinks your instruction continues on the next line and shows ...>. It’s not an error: type ; and press Enter. This lets you write long queries across several lines.
Asking for only some columns
You rarely need every column. Instead of *, write the ones you want, separated by commas, in the order you want to see them:
SELECT name, price FROM products;SQL doesn’t care about upper or lower case in its keywords: select and SELECT work the same. By convention SQL keywords (SELECT, FROM) are written in uppercase and the names of your tables and columns in lowercase, so you can tell them apart at a glance.
Try it: Show only the name and price of the products. Then try name and stock.
The database enforces the rules
Remember the price forty-two in the spreadsheet? When the products table was created we defined rules: the price must be a number greater than zero and no two products can share a code.
The following instructions try to store products that break those rules. You don’t need to understand how they’re written yet (you’ll see that in module 3); just run them and read what SQLite answers.
Try it: Run each instruction and look at the answer.
SQLite answers in English, like almost every programming tool:
| Message | What it means |
|---|---|
cannot store TEXT value in REAL column products.price | You can’t store text where a number goes. |
UNIQUE constraint failed: products.code | A product with that code already exists; it must be unique. |
CHECK constraint failed: price > 0 | The price doesn’t satisfy the rule of being greater than zero. |
In all three cases nothing was saved: the table is still clean. You can check with SELECT * FROM products;. That’s the big difference from the spreadsheet: errors are stopped at the door instead of sneaking into your data.
What you’ll learn
- Module 1: the pieces of a database: tables, rows, columns, documents and data types.
- Module 2: relational and non-relational databases (documents, key-value, wide-column and graphs), and how to choose the right one for each project.
- Module 3: creating, reading, updating and deleting data with SQL (known as CRUD).
- Module 4: keys and relationships: how customers connect to their purchases.
- Module 5: normalization: the rules that keep your data from repeating or contradicting itself.
- Module 6: NoSQL in practice: CRUD with documents and how to design them.
- Module 7: you’ll design a store’s database both ways, relational and with documents, and compare which fits what.
Test what you learned
Challenge
A school keeps its students in a database. Use it to:
- Find out which tables it has.
- Show all the students with all their columns.
- Show only the name and class of each student.
- Show all the subjects.
- From what you see on screen: how many students are in class 1B?
Show a solution
.tables
SELECT * FROM students;
SELECT name, class FROM students;
SELECT * FROM subjects;sqlite> .tables
students subjects
sqlite> SELECT * FROM students;
┌────┬────────────┬─────────────────┬───────┐
│ id │ student_no │ name │ class │
├────┼────────────┼─────────────────┼───────┤
│ 1 │ A001 │ Sofía Ramírez │ 1A │
│ 2 │ A002 │ Diego Hernández │ 1B │
│ 3 │ A003 │ Valeria Cruz │ 1A │
│ 4 │ A004 │ Mateo Gómez │ 1B │
│ 5 │ A005 │ Camila Flores │ 1B │
│ 6 │ A006 │ Emiliano Ruiz │ 1A │
└────┴────────────┴─────────────────┴───────┘
sqlite> SELECT name, class FROM students;
┌─────────────────┬───────┐
│ name │ class │
├─────────────────┼───────┤
│ Sofía Ramírez │ 1A │
│ Diego Hernández │ 1B │
│ Valeria Cruz │ 1A │
│ Mateo Gómez │ 1B │
│ Camila Flores │ 1B │
│ Emiliano Ruiz │ 1A │
└─────────────────┴───────┘
sqlite> SELECT * FROM subjects;
┌────┬─────────┐
│ id │ name │
├────┼─────────┤
│ 1 │ Math │
│ 2 │ Spanish │
│ 3 │ Science │
│ 4 │ History │
└────┴─────────┘Class 1B has 3 students: Diego, Mateo and Camila. Counting by hand works with 6 students, but not with 6,000; in module 3 you’ll learn to ask the database to filter and count for you.
