Leçon 1 · 20 min

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:

DateCustomerPhoneProductPriceQuantity
10/02Ana Torres81 1234 5678Chiapas coffee 500 g1892
10/05Ana Torres81 1234 5678Corn tortillas 1 kg28.503
10/07ana torres81 8765 4321Chiapas coffee1891
10/09Luis PérezBlack beans 900 gforty-two1

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.xlsx show 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:

  1. 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.
  2. Rules: the database refuses to store what breaks them: a negative price, a repeated code or a product without a name.
  3. 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:

SQL
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:

SQL
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.

Try it
Ce terminal d'entraînement nécessite JavaScript.
Essayez :

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:

SQL
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.

Try it
Ce terminal d'entraînement nécessite JavaScript.
Essayez :

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.

Try it
Ce terminal d'entraînement nécessite JavaScript.
Essayez :

SQLite answers in English, like almost every programming tool:

MessageWhat it means
cannot store TEXT value in REAL column products.priceYou can’t store text where a number goes.
UNIQUE constraint failed: products.codeA product with that code already exists; it must be unique.
CHECK constraint failed: price > 0The 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

What is the main advantage of a database over a shared spreadsheet?

What does SELECT name, price FROM products; show?

You type SELECT * FROM products and press Enter. Instead of the result you see ...>. What happened?

Challenge

A school keeps its students in a database. Use it to:

  1. Find out which tables it has.
  2. Show all the students with all their columns.
  3. Show only the name and class of each student.
  4. Show all the subjects.
  5. From what you see on screen: how many students are in class 1B?
Challenge
Ce terminal d'entraînement nécessite JavaScript.
Show a solution
SQL
.tables
SELECT * FROM students;
SELECT name, class FROM students;
SELECT * FROM subjects;
Output
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.

Terminez la leçon Répondez correctement aux quiz pour la marquer comme terminée.
Vous avez terminé le cours ! Vous avez gagné le badge: Databases Voir mes badges

Vous avez terminé le cours !

Vous avez gagné le badge: Databases

Voir mes badges
1 / 1

Construisez votre présence numérique

Commencez aujourd’hui : écrivez-nous, nous répondons en moins de 24 heures.