SQL Queries, Joins, Indexes and Database Security

The anatomy of a query. SELECT names the columns wanted; FROM names the table; WHERE filters rows with a condition; ORDER BY sorts the result; GROUP BY collapses rows sharing a value so that aggregate functions — COUNT, SUM, AVG, MAX, MIN — can be applied per group, and HAVING filters those groups. Read a query in execution order, not written order: FROM and JOIN build the working table first, WHERE removes rows, GROUP BY and HAVING collapse and filter, ORDER BY sorts, and SELECT picks columns last. Most exam errors come from confusing WHERE (filters rows before grouping) with HAVING (filters groups after).

Joins. Normalisation splits data across tables; a join reassembles it. A foreign key in one table holds the primary key of another, and JOIN ... ON matches rows where they are equal. An inner join keeps only matching pairs — a student with no enrolments does not appear; a left join keeps every row of the left table, with empty values where there is no match, which is how you list students including those with no enrolments. Joining three tables chains two ON conditions. The join is what makes normalisation workable: data is stored once and reassembled on demand.

Indexes. Without an index, finding rows with a given value means scanning the whole table. An index is a separate sorted structure (typically a tree) on one column that lets the database jump to matching rows, so a search on an indexed column is logarithmic instead of linear. The cost: every insert, update or delete must also update the index, so writes slow down, and each index takes storage. The rule is to index columns used in WHERE and JOIN on tables that are read far more than written, and not to index everything.

Security and integrity. A database concentrates an organisation's most valuable data, so it needs layered protection: access rights per user or role (read-only for reporting, no access to salary tables for most staff); encryption of sensitive columns and of backups; audit logs of who read or changed what; backups stored off-site and tested by restoring them; and validation rules in the schema (types, ranges, foreign keys) so that bad data is rejected at the door. The classic attack is SQL injection: a program builds a query by pasting in text the user typed, so a user who types a fragment of SQL gets it executed with the program's rights. The defence is parameterised queries, which send user input as data that can never be interpreted as code. Finally, a centralised database gives a single version of the truth — but is also a single point of failure and a single target, which is the standard evaluation point.