Overview
A database is organized storage for data. Tables hold related records. SQL is the language for asking questions about that data.
On this page8 sections
The idea
What goes wrong when three people edit one spreadsheet?
Picture 10,000 ShopKart orders in one shared sheet. Inventory, support and finance all edit it at once.
Three quiet accidents in one afternoon.
| Who | What they do | What breaks |
|---|---|---|
| Finance | Sorts column C but forgets columns A and B | Customer names now sit beside the wrong totals |
| Support | Types the word 'ten' into a price cell | Nothing stops it, so totals stop adding up |
| Inventory | Closes the window with a local autosave | Two other people's edits are silently overwritten |
A database is a spreadsheet with those accidents designed away.
What is a database, then?
Software built to avoid those accidents. A relational database keeps tables of records, with typed columns, linked by shared key values.
- Tables stored safely on disk
- Many people and services read and write at once
- Questions answered quickly
Relational is the most common kind, and the one every data warehouse builds on. Every table here has rows for records and columns for properties.
What is SQL?
SQL stands for Structured Query Language. Say 'sequel' or spell S-Q-L, both are fine. IBM created it in the 1970s.
SELECT order_id, order_total
FROM orders
WHERE order_status = 'paid';You say what you want, not how to loop through files. The answer comes back as another table, with its own rows and columns, so one answer can feed the next question.
Words you will see in this lesson
Six words. The rest of the lesson uses only these.
| Word | What it means |
|---|---|
| Database | A program that stores data safely and lets many people use it at once |
| SQL | The language for asking a database questions |
| Table | A grid of rows and columns |
| ID | A value that identifies one thing, such as a customer |
| Spreadsheet | A file with a grid you edit by hand |
| Query | A question written for the database |
Why this exists
What if one customer's facts live in 3 files?
A support agent gets a billing question. Contact details are in 1 file, orders in a spreadsheet, payments in a third.
- Customer calls
- Open the contacts file
- Open the orders sheet
- Open the payments file
- Compare by eye
Manual cross-checking leads to mistakes, out-of-sync records and slow answers.
How does a shared id fix that?
The database keeps one table per kind of fact. A shared column links them.
orders: 1 row per order
| orders.order_id | order_status | order_total |
|---|---|---|
| ORD-0001 | paid | 612.07 |
| ORD-0002 | refunded | 446.70 |
payments: points back to orders through order_id
| payments.payment_id | order_id | method | status |
|---|---|---|---|
| PAY-0001 | ORD-0001 | netbanking | captured |
| PAY-0002 | ORD-0002 | upi | refunded |
One SQL question can follow order_id across both tables. And each fact is stored once, so there is one place to update.
One shared id links the tables. Each fact lives in one place.
What happens when a customer changes their phone number?
In three loose files, someone must edit all three and hope nobody forgets one.
One authoritative place for each fact.
| Where the fact lives | Edits needed | Risk |
|---|---|---|
| Copied into 3 files | Three | One missed copy and the records disagree |
| 1 row in a customers table | One | Every query sees the new number at once |
This is why data engineers care about where a fact is stored. The next lessons show how tables keep it in one place.
Why can't a spreadsheet just get bigger?
A spreadsheet stops at about 1 million rows (Excel allows 1,048,576). A shop like ShopKart can produce millions of events a day.
- Spreadsheet sheetabout 1 million rows in Excel
- ShopKart eventsmillions a day
- Database enginebuilt for hundreds of millions or billions
High-volume facts need an engine designed for that size.
Picture this
What happens when you run a query?
You never open the tables and search them yourself. You describe the result.
You never open the tables and search them yourself. You describe what you want, and the engine does the searching.
The middle box is the engine. You do not say which file to open or how to loop through rows.
You describe the result. The engine decides how to get it.
What is inside the engine?
Three parts do the work you would otherwise write by hand.
- Parserchecks your syntax
- Optimizerpicks the cheapest way
- Executorreads blocks from storage
- Result tablecomes back to you
The optimizer may scan the table, use an index, or use several CPU cores at once. You do not choose.
How is that different from writing Python?
Python is imperative: you give every step. SQL is declarative: you give the goal.
Same goal, different amount of work.
| Style | What you write | Who decides how |
|---|---|---|
| Imperative (Python, Java) | Open the file, loop, split on commas, test, append to a list | You do |
| Declarative (SQL) | SELECT order_id FROM orders WHERE order_status = 'paid' | The engine does |
Why is a database safer than a spreadsheet?
Each item on the right is a guard rail against one accident from the start of this lesson.
Both hold tables. The difference shows up when the data is big, shared and important.
Column types stop 'ten' in a price cell. Permissions limit who can change a table. Transactions undo half-finished changes.
What would you say in an interview?
Interviewers ask why a company cannot track inventory in a spreadsheet. Name what is missing, not just the size limit.
- No control over two people changing the same cell at once.
- No rule that a price cell must hold a number.
- No way to undo a half-finished change.
- A ceiling near 1 million rows.
For SQL, add that it is declarative: engineers state the business rule, and the engine plans the execution.
A spoken version: "A spreadsheet trusts whoever types last. A database checks every change against rules, lets many people work at once, and can undo a change that failed halfway."
What does ACID guarantee?
ACID is the set of promises that keeps changes safe. Take one online payment.
Atomicity, Consistency, Isolation, Durability.
| Letter | Promise | In the payment example |
|---|---|---|
| Atomicity | All or nothing | Order turns paid and stock drops together, or neither does |
| Consistency | Only valid states | A price column never ends up holding text |
| Isolation | Transactions do not interfere | Two buyers of the last item cannot both win |
| Durability | Committed means kept | The payment survives a power cut a moment later |
Are there other kinds of database?
Yes. This track covers the relational kind because every warehouse is built on it.
Relational databases are the ones you query with SQL.
| Kind of database | Examples | Good at |
|---|---|---|
| Relational (SQL) | PostgreSQL, MySQL, Snowflake, BigQuery | Tables that relate to each other |
| Document | MongoDB | Flexible JSON-like records |
| Key-value | Redis | Very fast lookup by one key |
Warehouses like Redshift and engines like DuckDB, Trino and Spark SQL also speak SQL over tables.
Each table is a shelf of books and the engine is a librarian with a card index. You do not walk the shelves, you hand over a request and get the matching books back.
Should analysts query the live shop database?
Two kinds of database serve 2 jobs. OLTP runs the live app. OLAP answers big historical questions.
A pipeline copies data from OLTP to OLAP.
| OLTP | OLAP | |
|---|---|---|
| Stands for | Online Transaction Processing | Online Analytical Processing |
| Examples | PostgreSQL, MySQL | Snowflake, BigQuery, DuckDB |
| Typical query | Insert one order row | Sum 3 years of orders |
| Optimised for | Millions of tiny, fast queries | Scanning huge tables |
A giant query on the live database can exhaust memory and make checkout time out. Analysts query the warehouse instead.
Run heavy analysis on the warehouse, never on the live checkout database.
Is the editor the same thing as the engine?
At work they are separate. Tools like DBeaver and pgAdmin are clients that send SQL text to an engine running on a server.
- Client (editor)
- Sends SQL text over the network
- Engine on a server runs it
If the client freezes or closes, the engine on the server keeps running. The editor in this tab is a simplification: its engine runs inside the tab, so closing the tab stops it.
A small example
Which tables exist?
The editor in this tab is a relational database with its tables already loaded. Nothing to install. First ask what exists.
SHOW TABLES;name accounts_staging accounts_target ad_impressions batch_jobs comments contacts ... 58 tables in all, including orders, order_items and payments
Each name is one table. We will keep using orders.
What does the orders table start with?
These are the first 5 rows of orders, so you can follow the next query by hand.
First 5 rows of the real orders table.
| order_id | order_status | order_total (₹) |
|---|---|---|
| ORD-0001 | paid | 612.07 |
| ORD-0002 | refunded | 446.70 |
| ORD-0003 | paid | 1104.32 |
| ORD-0004 | cancelled | 339.90 |
| ORD-0005 | paid | 261.73 |
Predict: which of these 5 rows will the next query keep?
Which rows survive WHERE order_status = 'paid'?
Ask for up to five paid orders, with 2 columns.
SELECT order_id, order_total
FROM orders
WHERE order_status = 'paid'
LIMIT 5;order_id order_total ORD-0001 612.07 ORD-0003 1104.32 ORD-0005 261.73 ORD-0006 431.34 ORD-0007 396.72
ORD-0002 (refunded) and ORD-0004 (cancelled) are gone. The table is longer than 5 rows, so ORD-0006 and ORD-0007 fill the gaps.
What does the engine do with that query?
4 steps, in this order.
- It finds the orders table in its catalogue.
- It tests order_status = 'paid' on each row. Cancelled, refunded and pending rows drop out.
- It keeps only the order_id and order_total columns.
- It stops once 5 rows are collected.
The result is a new table. The orders table itself did not change.
Notice that you only said what you wanted: paid orders, two columns, five rows. You never said how to find them. The engine chooses the how. That is why SQL is called declarative: you describe the result, and the database works out the steps.
Is LIMIT 5 a promise of 5 rows?
No, it is a ceiling. Predict: how many rows come back if only three paid orders are above 1100?
SELECT order_id, order_total
FROM orders
WHERE order_status = 'paid'
AND order_total > 1100
LIMIT 5;order_id order_total ORD-0003 1104.32 ORD-0105 1389.16 ORD-0178 1262.06
3 rows came back. LIMIT only stops the engine early, it never invents rows.
LIMIT is a ceiling, not a promise.
What if you type order instead of orders?
Singular sounds natural. Predict what the parser says.
SELECT order_id, order_total
FROM order
WHERE order_status = 'paid'
LIMIT 5;Parser Error: syntax error at or near "order"
LINE 1: SELECT order_id, order_total FROM order WHERE order_status = 'paid' LIMIT 5
^ORDER is a reserved word, the start of ORDER BY. The parser expected a sort, not a table name. Check the exact name in SHOW TABLES.
What if you guess a table name?
A missing table gives a different error, and a helpful hint.
SELECT order_id, order_total
FROM orders_table
WHERE order_status = 'paid'
LIMIT 5;Catalog Error: Table with name orders_table does not exist!
Did you mean "orders"?
LINE 1: SELECT order_id, order_total FROM orders_table WHERE order_status = 'paid' LIMIT 5
^The engine looked in its catalogue, found nothing with that name, and suggested the closest one.
Can a SELECT break anything?
A SELECT never changes your tables, so experiment freely. Only statements that change data can damage it. (On a shared live database a huge SELECT can still slow other people down, which is why analysts use a warehouse.)
Run SELECT as often as you like. The result never changes the data.
| Statement | Changes the tables? |
|---|---|
| SELECT | No, it only reads |
| INSERT, UPDATE, DELETE, DROP | Yes, you must type them on purpose |
A query that only reads (it starts with SELECT) does not change the tables. You can experiment freely. The statements that do change data, like INSERT and DELETE, are separate words that you have to type on purpose.
How does the whole lesson replay on one question?
You ask: show up to five paid orders, with their totals.
The parser checks the syntax. The engine finds orders in its catalogue.
WHERE keeps ORD-0001, 0003, 0005 and the next paid rows. ORD-0002 and ORD-0004 drop out.
SELECT keeps 2 columns. LIMIT 5 stops after 5 rows.
A result table comes back. The orders table is unchanged.
Keep these five points.
- A database stores linked tables safely and lets many people use them at once.
- SQL is declarative: you describe the result, the engine finds the way.
- Spreadsheets do not enforce column types, do not control simultaneous edits, and stop at about 1 million rows. Databases do all three.
- OLTP runs the live app. OLAP answers big questions. Keep analysts off the live database.
- LIMIT is a ceiling, and SELECT never changes data.
Common beginner questions
Do I need to install a database to learn SQL?
Not here. The editor in this tab already has tables loaded, so you can start typing queries right away. At work you will usually connect to a database that lives in the cloud or on a company server, and the SQL you write is nearly the same.
Is SQL a programming language?
It is a language for asking questions about data, so it is a bit different from Python or JavaScript. You mostly describe the result you want rather than writing step-by-step instructions. Different databases add their own small variations, but the core you learn here works almost everywhere.
What is the difference between a database and a table?
A table is one grid of data, like orders. A database is the collection of tables plus the software that stores them and answers queries.
What comes next
The next lesson zooms into a single table: what rows and columns really are, and why every column has a data type. Keys, which link tables together, come right after that.
Practice
In the editor, run SHOW TABLES and read the names. Then preview the payments table by selecting all of its columns and limiting the result to 5 rows, the same way you previewed orders last lesson.
Going further: what a database refuses to do for you
A spreadsheet accepts anything you type. A database can be told what is allowed and will refuse the rest. These rules are constraints, and they are a large part of why a database is trusted as the system of record. Predict which of the inserts below the database accepts.
INSERT INTO accounts VALUES (3, NULL, 100);
INSERT INTO accounts VALUES (1, 'Dup', 100);
INSERT INTO accounts VALUES (4, 'Chen', -50);
INSERT INTO accounts VALUES (5, 'Chen', 75);
SELECT * FROM accounts ORDER BY idError: Constraint Error: NOT NULL constraint failed: accounts.owner Error: Constraint Error: Duplicate key "id: 1" violates primary key constraint. Error: Constraint Error: CHECK constraint failed on table accounts with expression CHECK((balance >= 0)) id owner balance 1 Asha 500 2 Ben 200 5 Chen 75
Three inserts were rejected: a missing owner (NOT NULL), a repeated id (PRIMARY KEY), and a negative balance (CHECK). Only the last valid row got in. The rule lives in the table, so every program that writes to it is held to the same rule.
The second protection is the transaction: a group of changes that succeeds together or not at all. Moving 300 from Asha to Ben is two updates. If the second fails, the first must be undone.
BEGIN;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
ROLLBACK;
SELECT * FROM accounts ORDER BY idError: Constraint Error: CHECK constraint failed on table accounts with expression CHECK((balance >= 0)) id owner balance 1 Asha 500 2 Ben 200
The third update would make Asha's balance negative, so the CHECK rejected it. Rolling back undid the first two as well, and both balances are back where they started (500 and 200). Without a transaction, Ben would have received 300 that never left Asha's account.
Try a new scenario
An order table has a customer_id column. How would you stop someone saving an order for a customer that does not exist, and where does that rule belong?
Work it out, then open the answer
Declare customer_id as a foreign key that references the customers table. The rule belongs in the database, not only in the app, because other programs, scripts and manual fixes also write to the table, and only a database rule applies to all of them.
Interview: what does ACID mean?
Atomic: a transaction fully happens or not at all. Consistent: rules such as constraints always hold. Isolated: concurrent transactions do not see each other's half-finished work. Durable: once committed, it survives a crash.
Close the lesson and explain it
Without scrolling up, explain what a constraint is, and why a transaction matters for a 2-step change such as a transfer.
Finished this lesson?
Mark it as read when you could explain it to someone else.