Databases and SQL
A relational database keeps data in tables of rows and columns that follow a schema. Watch a small shop's users and orders tables at work: a primary key names each row, SELECT asks a question, INSERT, UPDATE and DELETE change rows, a foreign key links each order to its user, and a JOIN pairs them back up. Then see why a plain file is not enough, and what NoSQL databases do differently.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01The users table has id as its primary key. What does that guarantee?
A primary key must be unique and never empty, so one id value always finds exactly one row. The DBMS refuses a second row with an id that is already taken.
02With the users in the figure (Ada in Paris, Sam in Cairo, Lina in Oslo), what does SELECT name FROM users WHERE city = 'Oslo' return?
WHERE keeps only the rows whose city is 'Oslo' (Lina's), and SELECT name gives back just the name column of those rows.
03What happens if you run UPDATE users SET city = 'Rome' with no WHERE clause?
WHERE picks which rows to change. Leave it out and the change applies to every row in the table. DELETE FROM users without WHERE would likewise remove every row.
04Each order has a user_id that refers to users.id. What is user_id?
A foreign key points at another table's key. The DBMS refuses an order whose user_id matches no user (SQLite only once PRAGMA foreign_keys = ON).
05SELECT u.name, o.item FROM orders AS o JOIN users AS u ON o.user_id = u.id ORDER BY o.id gives Ada tea, Lina mug, Ada cake, Sam pen. Why does Ada appear twice?
Ada's name is stored once, in users. Orders 1 and 3 both have user_id 1, so the JOIN pairs each of them with her row, and the answer shows her name on both.
Glossary
- Relational database
- A database that keeps data in tables of rows and columns and is queried with SQL, such as PostgreSQL, MySQL or SQLite.
- Schema
- The fixed design of a table: its name, its columns, each column's type and its rules (such as NOT NULL).
- Row and column
- A row is one thing (one user, one order); a column is one fact about every thing (its name, its city).
- Primary key
- A column (or set of columns) whose value is unique and never empty, so it names exactly one row.
- Foreign key
- A column whose values must match the key of a row in another table, linking the two tables.
- DBMS
- Database management system: the program (PostgreSQL, MySQL, SQLite) that stores the tables, checks every change against the schema and answers queries.
- SQL
- Structured Query Language: the language for asking (SELECT) and changing (INSERT, UPDATE, DELETE) data in a relational database.
- JOIN
- Part of a query that pairs rows of two tables wherever a condition holds, such as orders.user_id = users.id.
- NoSQL
- Databases that store data in shapes other than tables, such as documents (MongoDB), key-value pairs (Redis) or graphs (Neo4j).
Notes and sources
- Without ORDER BY, SQL does not promise any order for the rows of an answer. The JOIN asks for ORDER BY o.id; the figure shows rows in id order.
- SQLite only checks foreign keys after PRAGMA foreign_keys = ON (they are off by default, for backward compatibility). PostgreSQL and MySQL’s InnoDB check them by default.
- The foreign key also protects the other way round: with orders pointing at Ada, DELETE FROM users WHERE id = 1 is refused (FOREIGN KEY constraint failed), so no order is left pointing at nobody.
- The error messages shown are SQLite’s. PostgreSQL says the same in its own words, for example: duplicate key value violates unique constraint "users_pkey".
- Real databases rarely test every row one by one: an index can find matching rows directly. The answer is the same.
- MongoDB can also keep orders in their own collection and look them up with $lookup, and many SQL databases can store JSON documents. The shapes are choices, not walls.
- The Python example uses the sqlite3 module that ships with Python. Run it and it prints Ada tea, Lina mug, Ada cake, Sam pen, one per line.
- E. F. Codd, A Relational Model of Data for Large Shared Data Banks (Communications of the ACM, 1970)
- PostgreSQL documentation: Tutorial, Concepts
- PostgreSQL documentation: Constraints (primary keys, foreign keys)
- PostgreSQL documentation: Tutorial, Joins Between Tables
- PostgreSQL documentation: Inserting, Updating and Deleting Data
- PostgreSQL documentation: SELECT
- SQLite: SQLite Foreign Key Support
- Python Library Reference: sqlite3
- Silberschatz, Korth and Sudarshan, Database System Concepts, 7th ed. (ch. 1)
- MongoDB Manual: Documents
- MongoDB Manual: Data Modeling
Quiz Unlocks after the last step.