A database is an organised collection of related data stored so it can be searched and updated easily. Most databases store data in tables.
Tables, records and fields
A table is arranged in rows and columns. Each row is one record (one item, such as a single student). Each column is a field (one piece of information, such as name or age). A special field called the primary key gives every record a unique value so no two records are confused.
| id | name | age |
|---|---|---|
| 1 | Aisyah | 15 |
| 2 | Bo Han | 16 |
Key idea
We ask a database questions using SQL (Structured Query Language). SELECT reads data, INSERT adds a new record, and WHERE filters which records are affected.
Basic SQL statements
- Read every column:
SELECT * FROM students; - Read one column with a condition:
SELECT name FROM students WHERE age > 15; - Add a record:
INSERT INTO students VALUES (3, 'Chong', 15);
Example
From the table above, SELECT name FROM students WHERE age > 15; returns Bo Han, because only that record has an age greater than 15. Every SQL statement ends with a semicolon.
SQL lets us find exactly the data we need without reading the whole table by hand, even when the table holds thousands of records. Keywords like SELECT, FROM and WHERE are usually written in capital letters to make a statement easy to read, while the table and field names must match the database exactly. Getting the spelling and the semicolon right is part of writing correct SQL.