Databases and SQL Basics
What is a database?
A database is an organised collection of related data that can be stored, searched and updated easily. Examples in Kenya include the KNEC candidate registry, M-Pesa transaction records and school admissions lists. A Database Management System (DBMS) such as MySQL, PostgreSQL or Microsoft Access manages the data.
Structure
- Table: data arranged in rows and columns about one subject (e.g. students).
- Field (column): one attribute, e.g. name.
- Record (row): all data about one item.
- Data types: text, number, date, Boolean.
| id | name | class | marks |
|---|---|---|---|
| 1 | Wanjiku | 11A | 78 |
| 2 | Otieno | 11B | 65 |
| 3 | Fatuma | 11A | 91 |
Keys and relationships
- Primary key: uniquely identifies each record (id).
- Foreign key: a field linking to another table's primary key.
- Relationships: one-to-one, one-to-many (one class has many students), many-to-many.
Benefits of using keys: avoids duplication, keeps data consistent (integrity) and speeds searches.
SQL (Structured Query Language)
SQL is the standard language for relational databases. Commands end with a semicolon.
SELECT
SELECT name, marks FROM students; shows two columns.
SELECT * FROM students; shows all columns.
WHERE and ORDER BY
SELECT name FROM students WHERE marks >= 70;
SELECT * FROM students ORDER BY marks DESC;
INSERT
INSERT INTO students (id, name, class, marks) VALUES (4, 'Kamau', '11B', 55);
UPDATE
UPDATE students SET marks = 60 WHERE id = 4;
DELETE
DELETE FROM students WHERE id = 4;
Always include WHERE in UPDATE and DELETE. Without it every record changes.
Worked example 1
Show the names of Class 11A students sorted by marks, highest first.
SELECT name FROM students WHERE class = '11A' ORDER BY marks DESC;
Result: Fatuma, Wanjiku.
Worked example 2: aggregates
Find the average mark: SELECT AVG(marks) FROM students;
For the table above: (78 + 65 + 91) / 3 = 78.
Other functions: COUNT(), SUM(), MAX(), MIN().
Data security and ethics
- Use passwords and user roles.
- Back up regularly.
- Follow the Kenya Data Protection Act, 2019: collect only necessary personal data and protect it.
Try it
- Design a table for a school library with at least five fields and choose a primary key.
- Write SQL to show students with marks between 50 and 80.
- Write SQL to count students in class 11A.
- Explain why a foreign key is needed between students and classes tables.