Lesson 2 of 4 in this module

2/33 in track

What is a database?

sql8 min

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.

WhoWhat they doWhat breaks
FinanceSorts column C but forgets columns A and BCustomer names now sit beside the wrong totals
SupportTypes the word 'ten' into a price cellNothing stops it, so totals stop adding up
InventoryCloses the window with a local autosaveTwo 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.

  1. Tables stored safely on disk
  2. Many people and services read and write at once
  1. 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.

SQLRead it as English: from orders, keep the paid ones, show 2 columns
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.

WordWhat it means
DatabaseA program that stores data safely and lets many people use it at once
SQLThe language for asking a database questions
TableA grid of rows and columns
IDA value that identifies one thing, such as a customer
SpreadsheetA file with a grid you edit by hand
QueryA 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.

  1. Customer calls
  2. Open the contacts file
  3. Open the orders sheet
  4. Open the payments file
  1. 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_idorder_statusorder_total
ORD-0001paid612.07
ORD-0002refunded446.70

payments: points back to orders through order_id

payments.payment_idorder_idmethodstatus
PAY-0001ORD-0001netbankingcaptured
PAY-0002ORD-0002upirefunded

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 livesEdits neededRisk
Copied into 3 filesThreeOne missed copy and the records disagree
1 row in a customers tableOneEvery 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.

  1. Spreadsheet sheetabout 1 million rows in Excel
  2. ShopKart eventsmillions a day
  1. 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.

What happens when you run a query
You write a SQLquestionThe database enginereads the tablesA result table comesback

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.

  1. Parserchecks your syntax
  2. Optimizerpicks the cheapest way
  1. Executorreads blocks from storage
  2. 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.

StyleWhat you writeWho decides how
Imperative (Python, Java)Open the file, loop, split on commas, test, append to a listYou 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.

Spreadsheet vs database
SpreadsheetDatabasevsOne grid per sheetAny value in any cellSlows down at very large sizesEasy to overwrite by accidentMany linked tablesEach column has a typeBuilt for millions of rowsMany users at once, safely

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.

LetterPromiseIn the payment example
AtomicityAll or nothingOrder turns paid and stock drops together, or neither does
ConsistencyOnly valid statesA price column never ends up holding text
IsolationTransactions do not interfereTwo buyers of the last item cannot both win
DurabilityCommitted means keptThe 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 databaseExamplesGood at
Relational (SQL)PostgreSQL, MySQL, Snowflake, BigQueryTables that relate to each other
DocumentMongoDBFlexible JSON-like records
Key-valueRedisVery fast lookup by one key

Warehouses like Redshift and engines like DuckDB, Trino and Spark SQL also speak SQL over tables.

IdeaA library with a card index

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.

OLTPOLAP
Stands forOnline Transaction ProcessingOnline Analytical Processing
ExamplesPostgreSQL, MySQLSnowflake, BigQuery, DuckDB
Typical queryInsert one order rowSum 3 years of orders
Optimised forMillions of tiny, fast queriesScanning 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.

  1. Client (editor)
  2. Sends SQL text over the network
  3. 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.

SQLList every table in the database
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_idorder_statusorder_total (₹)
ORD-0001paid612.07
ORD-0002refunded446.70
ORD-0003paid1104.32
ORD-0004cancelled339.90
ORD-0005paid261.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.

SQLAsk for up to five paid orders
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.

  1. It finds the orders table in its catalogue.
  2. It tests order_status = 'paid' on each row. Cancelled, refunded and pending rows drop out.
  3. It keeps only the order_id and order_total columns.
  4. 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?

SQLA ceiling of five, but fewer rows match
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.

SQLSingular table name
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.

SQLA table that does not exist
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.

StatementChanges the tables?
SELECTNo, it only reads
INSERT, UPDATE, DELETE, DROPYes, you must type them on purpose
Watch outRunning a SELECT cannot break anything

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?

One query, start to finish
  1. You ask: show up to five paid orders, with their totals.

  2. The parser checks the syntax. The engine finds orders in its catalogue.

  3. WHERE keeps ORD-0001, 0003, 0005 and the next paid rows. ORD-0002 and ORD-0004 drop out.

  4. SELECT keeps 2 columns. LIMIT 5 stops after 5 rows.

  5. A result table comes back. The orders table is unchanged.

Keep these five points.

  1. A database stores linked tables safely and lets many people use them at once.
  2. SQL is declarative: you describe the result, the engine finds the way.
  3. Spreadsheets do not enforce column types, do not control simultaneous edits, and stop at about 1 million rows. Databases do all three.
  4. OLTP runs the live app. OLAP answers big questions. Keep analysts off the live database.
  5. 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.

SQLConstraints reject bad rows
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 id
Error: 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.

SQLA transfer that fails halfway, then rolls back
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 id
Error: 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.

Rate:
Was this useful?