11. Databases and Structured Query Language (SQL)

• A table holds data about one kind of thing (students, books).
• A record is one row — all the data about one item.
• A field is one column — one piece of data about every item (e.g. FirstName).

Defining a single-table database from requirements means listing each field, its data type, a sample record, and the validation for each field:

Phone numbers and IDs with leading zeros are text — you never do arithmetic on them.

A primary key is a field that uniquely identifies each record in a table — no two records can have the same value, and it cannot be empty.

Choosing one: pick a field whose values can never repeat (StudentID, ISBN, OrderNumber). Names, dates of birth and towns are not suitable because two records could share them. If no field is unique, add an ID field.

A relational database stores data in linked tables, so data such as a class's teacher is stored once instead of being repeated in every student record (less duplication and fewer inconsistencies).

A foreign key is a field in one table that is the primary key of another table. It establishes the link so the two tables can be joined.

CLASS

STUDENT

ClassID is the primary key of CLASS and a foreign key in STUDENT. One class has many students (a one-to-many link). Validation on the foreign key: its value must already exist in CLASS.

To join the tables, match each record in STUDENT to the record in CLASS whose ClassID is the same:

In SQL this is INNER JOIN … ON STUDENT.ClassID = CLASS.ClassID (see §11.2). Only records with a matching value in both tables appear.

• SELECT — the fields to show (* = all fields).
• FROM — the table.
• WHERE — the condition records must meet; combine conditions with AND (both true) / OR (either true). Text values go in quotes; numbers do not.

SELECT FirstName, Age
FROM STUDENT
WHERE Age > 15 AND ClassID = '10A';

Result: Hina 16 and Sara 17 (Ayesha is 15, so not > 15; Bilal is in 10B).

ORDER BY field ASC sorts A→Z / smallest first (the default); DESC sorts Z→A / largest first. It comes after WHERE.

SELECT FirstName, Fees
FROM STUDENT
ORDER BY Fees DESC;

Result order: the three 300.00 students, then Omar 275.50, then the two 250.00 students. Add a second field to break ties: ORDER BY Fees DESC, FirstName ASC.

SUM(field) adds the values in a numeric field; COUNT(field) or COUNT(*) counts the matching records. Each returns one value.

SELECT SUM(Fees)
FROM STUDENT
WHERE ClassID = '10A';

Result: 800.00 (250.00 + 250.00 + 300.00).

SELECT COUNT(StudentID)
FROM STUDENT
WHERE ClassID = '10B' OR Age = 17;

Result: 3 (Bilal and Omar are in 10B; Sara is 17).

Adds a new record. List the fields, then the values in the same order.

INSERT INTO STUDENT (StudentID, FirstName, ClassID, Age, Fees)
VALUES ('S006', 'Zara', '10B', 15, 300.00);

After this, SELECT COUNT(*) FROM STUDENT; gives 6. If values are given for every field in table order, the field list may be left out.

INNER JOIN combines records from two tables where the ON condition matches — normally the foreign key = primary key. Write fields as Table.Field when both tables are involved.

SELECT STUDENT.FirstName, CLASS.Teacher
FROM STUDENT
INNER JOIN CLASS ON STUDENT.ClassID = CLASS.ClassID
WHERE CLASS.Room = 'R14'
ORDER BY STUDENT.FirstName ASC;

Result: Bilal, Ms Ali and Omar, Ms Ali — the students taught in room R14, sorted by name.

Order of clauses: SELECT … FROM … INNER JOIN … ON … WHERE … ORDER BY ….

Common faults to look for: