Why Not Just a Spreadsheet?

Spreadsheets break at scale

A spreadsheet is also made of rows and columns, which makes the question of why anyone needs a database a fair one. The answer is that a spreadsheet is a picture of data that a person edits by hand, while a database is a service that programs talk to. The differences show up fast:

• Size. A spreadsheet slows to a crawl around a hundred thousand rows. Databases handle billions.
• Many users at once. Two people editing one file overwrite each other. A database safely handles thousands of simultaneous readers and writers.
• Rules. Nothing stops a spreadsheet cell from holding "three" where an age should be. A database can refuse bad data, and you will set those rules up in unit 7.
• Questions. In SQL, "average age of every dog owned by users who signed up this year" is one query. In a spreadsheet it is an afternoon of clicking.

Plain files, whether .txt or .csv, have all the same problems, plus you would have to write every bit of the searching and updating code yourself.

The weakness that bites first at scale

Picture an app with 40 million users where 2,000 of them save changes in the same moment. The weakness that matters most there is that simultaneous edits to one file overwrite each other.

It is worth being precise about which problems are and are not in play. Spreadsheets can store text and compute averages perfectly well, so neither of those is the sticking point. What they cannot do is coordinate thousands of concurrent writers without losing changes, because the last save simply wins and the others vanish.

Databases are built for exactly this situation. Unit 10 covers the mechanism, called transactions, that makes concurrent writing safe.

Asking for specific columns

SELECT * grabs every column, but usually you only want some. List the column names you want, separated by commas:

SELECT name, age FROM pets;

Read it right to left: from the pets table, select the name and age columns. The columns come back in the order you list them, and every row is still included. Choosing columns never removes rows, it only trims what each row shows.

Selecting two columns instead of all three

The same three pets go into the table, but the SELECT now names two columns instead of using *, so each output row shows only the name and the age. That is why there is a single | separator per line rather than two.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

SELECT name, age FROM pets;

Output

Biscuit|3
Mochi|5
Ziggy|2

Every row is still returned, because there is no filter yet. Naming columns narrows the query sideways, choosing which fields come back, and it is what you want in real applications: fetching columns nobody uses wastes memory and network time on every single request.

Selecting a single column

Narrowing the SELECT all the way down to one column makes each output line a single word, with no separator at all.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

SELECT species FROM pets;

Output

dog
cat
parrot

Only the final SELECT line differs from the previous example. The table definition and the three inserts are untouched, which is worth noticing in itself: the shape of a query is completely independent of how the data got there.

Note also that three rows come back even though only three distinct species exist here. SELECT returns one output row per table row, not one per distinct value, and removing duplicates is a separate job for DISTINCT.

Column order follows the SELECT

To get each pet's species and then its name, in that order, the query is:

SELECT species, name FROM pets;

The pattern is SELECT column1, column2 FROM table;, listing the columns you want in the order you want them, and naming the table after FROM.

The detail worth locking in is that the output column order follows your SELECT list, not the order the columns were declared in CREATE TABLE. Since pets was created as name, species, age, this query deliberately reverses the first two, and the database obliges without complaint.