Relational Databases and SQL

Avoiding Data Inconsistency and Redundancy

The best way to avoid storing a customer's address twice is to store the address once in a Customers table and link to it from Orders via a foreign key.

Concept of a Database

A database is an organised collection of data.

Concept of a Relational Database

A flat-file database stores all data in a single table (or file) without explicit links to other tables.

Primary Keys and Foreign Keys

A composite primary key uses two or more columns together for uniqueness — useful when no single column is unique on its own.

Queries Across Two Tables

In AQA-style GCSE SQL, two tables are joined using a WHERE clause that matches the key fields: ... FROM A, B WHERE A.id = B.a_id.

SQL — DELETE FROM

DELETE FROM removes records (rows) from a database table.

SQL — INSERT INTO

INSERT INTO Books DEFAULT VALUES inserts a row using each column's declared default value (where defined).

SQL — ORDER BY (ASC / DESC)

ASC stands for ascending order — smallest to largest, or A→Z for text.

SQL — SELECT, FROM, WHERE

The core SQL data-manipulation commands are: SELECT (retrieve data from a table), INSERT INTO (add a new record), UPDATE (change data in existing records), and DELETE FROM (remove records).

SQL — UPDATE

The SET clause specifies the new field values to assign.