What a database is
A database is an organised store of data. It keeps the data so that many programs and people can find it, change it and trust it. Here, one database holds a small library’s books.
A database management system (DBMS) is the program that runs a database: PostgreSQL, MySQL and SQLite are three. It stores the data on disk and answers questions. Apps never edit those files; they ask the DBMS.
Data lives in tables. This one is called books. Each row is one book. Each column is one fact about it: its id, name or year. Every value in a column has the same type.
It looks like a spreadsheet, but a spreadsheet cell, like a plain file, takes anything you type. A DBMS checks every change against the table’s rules. Try to store the year ‘soon’ and PostgreSQL refuses: year must be a whole number.
Your first query
Apps ask questions in SQL, the language that table-based (relational) databases understand. This query means: give me the name of every book from after 2000. The app sends it to the DBMS as plain text.
The DBMS does not work left to right. It starts with FROM: which table holds the data? Here, books, with all 5 of its rows.
Next, WHERE. The DBMS tests every row: is its year greater than 2000? Dune (1965) and Holes (1998) fail. Coraline, The Road and Wonder pass, so only those rows are kept.
Last, SELECT says which columns to give back. Only name is asked for, so id and year are left out. 3 rows of one column: that is the answer.
The DBMS sends the answer back to the app as a small table of its own: Coraline, The Road and Wonder. The query said what to find; the DBMS worked out how.
Many users, one truth
While the library app asks again, the front desk adds Piranesi (2020). The DBMS lets both work at the same time, and each sees a consistent snapshot of the data, never half of a change.
Before the DBMS says a change is saved, it writes it to a log on disk. If the power then fails, it replays the log when it restarts, so every saved change is still there.
What happens
;; step 1/11 · database database an organised store of data it lets many programs and people find it (ask questions) change it (add, edit, delete) trust it (rules, no half changes, no lost changes) this one: the books of a small library
Without ORDER BY, SQL does not promise any order for the rows of an answer. The figure shows them in id order; add ORDER BY id to ask for that. Real databases rarely test every row one by one: an index can find the matching rows directly, as a later lesson shows. The answer is the same.