Databases
Tables, fields, records, primary keys, data types and SQL — SELECT, WHERE, ORDER BY, SUM and COUNT.
1 Database Basics
A database is an organised collection of related data. Data is stored in tables made up of rows and columns.
| Term | Definition | Analogy | ||
|---|---|---|---|---|
| Table | A grid of rows and columns that stores related data about one entity type | Field | Record | Primary Key |
Example — Students Table
| StudentID ⚷ | StudentName | Score | Grade | Passed |
|---|---|---|---|---|
| 1001 | 1002 | 1003 | 1004 | 1005 |
⚷ StudentID is the Primary Key — each value is unique and identifies exactly one student.
2 Primary Keys
A primary key is a field whose value is unique for every record and never blank. It is used to uniquely identify each record in the table.
In exam questions asking you to identify a suitable primary key — always choose a field where every value is guaranteed to be different. Never choose a name (two people can share the same name). Student ID, National ID, and product codes are good choices.
3 Data Types in Databases
| Data Type | Description | Example |
|---|---|---|
| Text / Alphanumeric} -- | ||
| Character} -- | ||
| Boolean} -- | ||
| Integer} -- | ||
| Real} -- | ||
| Date / Time} -- |
4 SQL Introduction
SQL (Structured Query Language) is the standard language for querying and managing relational databases. For this syllabus you only need to work with a single table.
5 SELECT & FROM
-- Select ALL fields from the table SELECT * FROM Students; -- Select specific fields only SELECT StudentName, Score FROM Students;
Use * to select all fields. List specific field names separated by commas to retrieve only what you need.
6 WHERE Clause
-- Students who scored 50 or above (passed) SELECT StudentName, Score FROM Students WHERE Score >= 50; -- Students with Grade = 'A' (text value in single quotes) SELECT * FROM Students WHERE Grade = 'A'; -- Multiple conditions with AND SELECT StudentName FROM Students WHERE Score >= 50 AND Score <= 70; -- Multiple conditions with OR SELECT * FROM Students WHERE Grade = 'A' OR Grade = 'B';
Text values in SQL use single quotes: 'A'. Numbers do not use quotes: 50. This is a very common mistake.
7 ORDER BY
-- Lowest score first (ASC = ascending, is the default) SELECT StudentName, Score FROM Students ORDER BY Score ASC; -- Highest score first (DESC = descending) SELECT StudentName, Score FROM Students ORDER BY Score DESC; -- Combine WHERE and ORDER BY SELECT StudentName, Score FROM Students WHERE Passed = 'TRUE' ORDER BY StudentName ASC;
8 SUM & COUNT
-- Total of all scores SELECT SUM(Score) FROM Students; -- Count ALL records in the table SELECT COUNT(*) FROM Students; -- Count students who passed SELECT COUNT(*) FROM Students WHERE Passed = 'TRUE'; -- Sum of scores for grade A students only SELECT SUM(Score) FROM Students WHERE Grade = 'A';
9 Full Worked Examples
Using this Products table for all queries below:
| ProductID ⚷ | ProductName | Category | Price | Stock |
|---|---|---|---|---|
| P001 | P002 | P003 | P004 | P005 |
SELECT ProductName, Price FROM Products WHERE Category = 'Electronics' ORDER BY Price DESC;
Result: Laptop Pro (899.99) then Monitor 27" (349.99)
SELECT COUNT(*) FROM Products WHERE Category = 'Accessories';
Result: 3 (USB Cable, Wireless Mouse, Keyboard)
SELECT SUM(Stock) FROM Products WHERE Price < 50;
Result: 200 + 80 = 280 (USB Cable and Wireless Mouse qualify)
SELECT * FROM Products ORDER BY ProductName ASC;
Result: Keyboard, Laptop Pro, Monitor 27", USB Cable, Wireless Mouse
In exams you may be asked to write a SQL query OR to state the output from a given query. Practise both directions. The most common mistake is forgetting quotes around text values in WHERE clauses.