A shop that never forgets
You buy a hoodie from an online shop. You log in. The shop knows your name. It takes the hoodie out of stock. It remembers your order. Next week it shows you a matching cap.
A database is an organised store of data. One holds all of this memory.
Key words
| Word | What it means |
|---|---|
| Database | An organised store of data that programs can search and change. |
| Table | A grid in a database. Each row is one record and each column is one fact. |
| Query | A question you ask a database. |
| SQL | The language used to ask a database questions and to change its data. |
| User management | Keeping track of who can log in and what each person may do. |
| Stock | How many of each item you have to sell. |
| Personalisation | Changing what a page shows to fit one person. |
| Diagnostics | Finding out what went wrong, using records of what happened. |
| Millisecond | One thousandth of a second. 1,000 milliseconds is 1 second. |
| Log | A list of events with the time they happened. |
The cells students fill in are left blank in this preview.
Reading a query
| You see | Say it in words |
|---|---|
| SELECT name, role | Show me the name and role columns. |
| FROM users | Look in the users table. |
| WHERE role = 'admin' | Keep only the rows where the role is admin. |
| ORDER BY stock | Sort the rows, smallest first. |
| JOIN products ON ... | Put two tables side by side where the ids match. |
| SUM(...), AVG(...) | Add up a column, or find its average. |
| GROUP BY page | Give one answer for each page. |
| INSERT, UPDATE | Add a new row, or change a row. |
| -- some words | A note for people. The database skips it. |
The cells students fill in are left blank in this preview.
Try it: user management
-- Who can log in as an admin?
SELECT name, role FROM users WHERE role = 'admin';
-- Change the query to list all customers.
Why not a text file?
A file is fine for ten names. For ten thousand customers, a database is much better.
A database finds one record fast. It stops two people changing the same data at once. It keeps the data safe if the power fails. It can answer many different questions about the same data.
Quick check
Have a go
Quick check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Four jobs for a database
| Job | What the database holds | Example question |
|---|---|---|
| User management | Accounts, roles and passwords (stored scrambled, so nobody can read them) | Is this person an admin? |
| E-commerce | Products, stock, orders | How many mugs are left? |
| Diagnostics | Logs of errors and events | Which page had errors yesterday? |
| Performance analysis | How long each page took to load | Which page is slowest? |
The cells students fill in are left blank in this preview.
Try it: stock and orders
-- Which products have fewer than 5 left?
SELECT name, stock FROM products WHERE stock < 5;
-- How many mugs has each customer ordered?
SELECT users.name, SUM(orders.quantity)
FROM orders
JOIN users ON orders.user_id = users.user_id
WHERE orders.product_id = 2
GROUP BY users.name;
Try it: process an order
-- Ben buys one Cap (product 3). Two things must happen: record the order, and take one off the stock.
INSERT INTO orders VALUES (5, 2, 3, 1);
UPDATE products SET stock = stock - 1 WHERE product_id = 3;
-- How many caps are left? It was 2.
SELECT name, stock FROM products WHERE product_id = 3;