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.

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.