Every time you check your bank balance, stream a song, or track a package, a database answers your question in milliseconds — often while millions of other people ask theirs at the same time. Spreadsheets fall over at a few hundred thousand rows; databases shrug at billions. By the end of this lesson you'll know exactly what a database is, how it organizes data, and why SQL is the one language every data job posting mentions.
What you'll learn
What
A database is an organized collection of data stored on a computer so it can be searched, updated, and protected reliably. A relational database — the most common kind — stores data in tables made of rows and columns, and you talk to it using SQL (Structured Query Language).
Why
Files and spreadsheets can't handle many people reading and writing at once, can't enforce rules about what data is valid, and slow to a crawl as data grows. Databases solve all three problems — which is why nearly every company keeps its most important data in one.
Where it's used
Every serious application — e-commerce checkouts, hospital records, banking ledgers, analytics dashboards — sits on top of a database. As a data professional, the database is where your raw material lives.
Where this runs in production
Shopify runs one of the world's largest MySQL fleets. Every product, order, and customer for over a million merchants lives in relational tables, sharded across many database servers.
Instacart's catalog, orders, and shopper assignments are stored in PostgreSQL. When a shopper marks an item as picked, that's a row update in a relational table.
Stripe processes payments where a lost or duplicated row means real money. Relational databases enforce the strict consistency rules that make this possible.
Imagine a company's records room. Each filing cabinet is a table (one for customers, one for orders). Each folder inside a cabinet is a row — one customer, one order. Each labeled field on the folder's form is a column (name, email, signup date). The strict clerk who fetches folders, refuses malformed forms, and stops two people from editing the same folder at once? That's the RDBMS. The analogy breaks down in one way: a real clerk is slow, while an RDBMS can search millions of folders in milliseconds using indexes.
A table stores one kind of thing (customers, orders, products). A row (also called a record) is one instance of that thing — one specific customer. A column (also called a field) is one attribute every row has — email, country, signup_date. Every column has a data type: INTEGER, TEXT, DATE, NUMERIC, and so on.
A primary key is a column (or set of columns) whose value uniquely identifies each row — no duplicates, no missing values. A foreign key is a column in one table that holds the primary key value of a row in another table, creating the relationship. orders.customer_id → customers.customer_id is the classic example.
-- The schema we'll use throughout this module: -- customers(customer_id, name, email, country, signup_date) -- products(product_id, product_name, category, unit_price) -- orders(order_id, customer_id, order_date, status, total_amount) -- order_items(order_item_id, order_id, product_id, quantity, unit_price) SELECT name, country FROM customers;
This is your first look at SQL. The comment lines (starting with --) describe our e-commerce schema: four related tables. The query below them asks for two columns from the customers table. You'll write queries like this yourself in the next lesson — for now, notice how readable it is: it's close to plain English.
Spreadsheets let any cell hold anything — a number today, a note tomorrow. Databases enforce a schema: every value in a column must match the column's declared type, and constraints (like 'customer_id must be unique') are enforced by the RDBMS itself. This rigidity is a feature — it's what keeps millions of rows trustworthy.
Click any table to see its columns and role. Arrows show foreign-key relationships — how rows in one table point to rows in another.
You've just been given access to the customers table at an online bookshop. First task: understand what one row means.
Each line after the header is one row — one customer. Each vertical slice is a column. Row 1 tells a complete story: customer #1 is Amara, from Nigeria, who signed up on 2025-03-14.
Your task
Time to run your very first query. The customers table holds five customers of an online bookshop. Complete the query so it returns every column of every row — the SQL equivalent of opening the filing cabinet and looking inside. The * symbol means 'all columns'.
customer_id | name | email | country | signup_date -------------+-------+------------------+---------+------------- 1 | Amara | amara@mail.com | Nigeria | 2025-03-14 2 | Wei | wei@mail.com | China | 2025-04-02 3 | Priya | priya@mail.com | India | 2025-04-19 4 | Jamal | jamal@mail.com | Egypt | 2025-05-07 5 | Alice | alice@mail.com | Canada | 2025-05-21 (5 rows)
Write your solution in the editor on the right, then hit Run.
In a relational database, what does one row of a table represent?
What does an RDBMS do that a plain file of data cannot?
The orders table has a customer_id column containing values that also appear in customers.customer_id. In the orders table, customer_id is a…
Which spreadsheet weakness explains the 'unknown' value appearing in a date column?
The bookshop also has a products table: products(product_id, product_name, category, unit_price). Write a query that returns all columns and all rows from products.
Your company uses PostgreSQL, but a job posting requires 'SQL Server experience'. How transferable is your SQL knowledge?
What is a relational database, and what are its core building blocks?
A relational database organizes data into tables, where each table stores one kind of entity — customers, orders, products. Each row is one record and each column is one typed attribute of that record. Tables are linked through keys: a primary key uniquely identifies each row, and a foreign key in another table references it, which is what makes the model 'relational'. The software that manages all of this — enforcing types and constraints, coordinating concurrent users, and answering SQL queries — is the RDBMS, such as PostgreSQL or MySQL.
When would you choose a relational database over a spreadsheet or a flat file, and what trade-offs come with that choice?
I'd choose a relational database once data integrity, scale, or concurrency matters. A database enforces a schema — a DATE column can't silently hold the text 'unknown' — and it handles many simultaneous readers and writers without corrupting data, which spreadsheets can't. It also stays fast at millions of rows thanks to indexes, where spreadsheets degrade badly past a few hundred thousand. The trade-offs are upfront design effort — you must define tables, types, and keys before loading data — and operational overhead, since someone has to run and back up the database. For a quick one-off analysis of a few thousand rows, a spreadsheet or CSV is genuinely fine; the database earns its cost as soon as the data becomes shared, long-lived, or business-critical.
What's the difference between an OLTP database and an analytical data warehouse, and why do companies run both?
OLTP (Online Transaction Processing) systems handle the live operations of a business — inserting an order, updating a shipment status — with many tiny, fast transactions per second, typically on row-oriented databases like PostgreSQL or MySQL. OLAP (Online Analytical Processing) workloads are the opposite shape: a few large queries that scan millions of rows to compute aggregates, run on column-oriented warehouses like BigQuery, Snowflake, or Redshift. Running heavy analytics on the production OLTP database would compete with customer-facing transactions for resources and could slow checkouts, so companies copy data into the warehouse via ETL/ELT pipelines. Analysts then query the warehouse freely without touching production. Importantly, both worlds speak SQL — the same SELECT, WHERE, and GROUP BY skills apply, which is why SQL fluency is the most portable skill in data. The main practical difference an analyst notices is data freshness: warehouse data is usually minutes to a day behind production.
Common Mistakes to Avoid
1) Treating a database like a spreadsheet — designing one giant table with repeated customer details instead of separate related tables, which causes update anomalies. 2) Confusing rows with columns when reading a schema: a row is one record; a column is one attribute across all records. 3) Assuming primary keys and foreign keys are the same thing — a primary key identifies rows in its own table; a foreign key references another table's primary key and can repeat. 4) Believing each database product has its own query language — core SQL is standardized; only dialect details differ. 5) Storing money in floating-point columns instead of NUMERIC/DECIMAL, which introduces rounding errors that exact decimal types avoid.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: what is a relational database, using a school as the example?' • 'Quiz me on primary keys vs foreign keys with five quick questions.' • 'Show me how the customers and orders tables would look if we wrongly merged them into one table.' • 'Explain the difference between PostgreSQL, MySQL, and SQLite in two sentences each.' • 'Interview mode: ask me what an RDBMS does and grade my answer.'
Glossary
Database — an organized, managed collection of data. Table — a grid storing one kind of entity, made of rows and columns. Row (record) — one instance of the entity, e.g. one customer. Column (field) — one typed attribute every row has, e.g. email. Schema — the formal definition of tables, columns, types, and constraints. Primary key — the column whose value uniquely identifies each row. Foreign key — a column referencing another table's primary key, creating a relationship. RDBMS — Relational Database Management System, the software (PostgreSQL, MySQL) that manages relational data. SQL — Structured Query Language, the standard language for querying relational databases. Dialect — one RDBMS's specific flavor of SQL. Constraint — a rule the database enforces, like NOT NULL or CHECK. Data warehouse — an analytics-optimized database (BigQuery, Snowflake) fed by copies of production data.
Recommended Resources
• Docs: the PostgreSQL Tutorial chapter 'What Is PostgreSQL?' and 'Concepts' — a gentle official introduction to tables and rows. • Read: 'A Relational Model of Data for Large Shared Data Banks' summaries — Edgar Codd's 1970 idea is why this lesson exists. • Practice: sketch the tables you'd design for an app you use daily (Spotify, a food-delivery app) — name the primary and foreign keys. • Next in DSM: SELECT & FROM, where you stop reading about tables and start querying them yourself.
Recap
✓ A relational database stores data in tables: rows are records, columns are typed attributes. ✓ The RDBMS (PostgreSQL, MySQL, SQLite, SQL Server) enforces the schema, coordinates users, and answers queries. ✓ A primary key uniquely identifies each row; a foreign key references another table's primary key to relate tables. ✓ Storing each fact exactly once — instead of copying it — prevents update anomalies. ✓ SQL is a declarative standard: you describe what data you want, and it works nearly identically across dialects. ✓ Production (OLTP) databases handle live transactions; warehouses (OLAP) handle analytics — both speak SQL. Next up: SELECT & FROM. You now know where the data lives — next you'll write real queries against it: choosing columns, renaming them with aliases, and de-duplicating results with DISTINCT. How do you ask a table with a million rows for exactly the two columns you need? That's lesson two.
Run your code to see the output here.