Databases and SQL
Computer Science · GCSE · The School
The vocabulary
A TABLE holds data about one kind of thing. A RECORD (row) is one of them. A FIELD (column) is one property. A PRIMARY KEY is a field whose value is unique for every record — the thing that lets you say exactly which record you mean. Get these four words right and database questions become straightforward.
Asking questions in SQL
SELECT name, score FROM students WHERE score > 60 ORDER BY score DESC; — choose the columns, name the table, filter the rows, sort the result. Almost every query you meet at this level is those four parts. Read it aloud in that order and SQL stops looking like code and starts looking like a sentence.
Why split into related tables
One giant table repeats the same class name on every pupil row: waste, and worse, a chance to spell it differently in two places. Split into Students and Classes, link them by a key, and each fact is stored ONCE. Storing a fact once is the whole idea behind relational databases.
One big table holds every order with the customer's name and address repeated on each row. Alice has ordered six times, so her address is stored six times — and when she moves, five rows get updated and one does not. Split it: a CUSTOMER table with CustomerID as primary key and the address stored ONCE, and an ORDERS table holding CustomerID instead. `SELECT * FROM Orders WHERE CustomerID = 7` joins them back when you need it.
Keys are what make the tables one database
A primary key uniquely identifies a row and must never repeat or be blank. A foreign key is a primary key from another table, stored so the two can be joined. Without them you have spreadsheets in the same file; with them the database can guarantee that every order belongs to a customer who exists.
SQL is more than SELECT
Exams ask for the other three too: INSERT INTO adds a row, UPDATE changes existing rows, DELETE removes them — and the last two are dangerous without WHERE, which is a favourite exam point. UPDATE with no WHERE changes every row in the table. Write the WHERE clause first and the action second.