Relational Databases and SQL
What's covered
Key facts
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 foreign key is a field in one table that holds the primary key value of a record in another table, used to link the two.
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 (title, year) VALUES ('Hamlet', 1603) adds a new row to the Books table.
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.
Practice questions with answers
A taste of the 91 questions in this topic, answers marked. Sign up to practise the full set with spaced repetition.
What is data redundancy?
- •Data stored across multiple tables using foreign keys
- •Data that has been encrypted and backed up
- •Missing values in a database record
- ✓Same data stored more than once
Why use a database instead of separate files?
- •Always smaller in size than text files
- ✓Better organised and easier to query
- •Removes the need for storage devices
- •Runs much faster than the CPU only
What is the main advantage of a relational database?
- ✓Reduces data duplication across tables
- •Removes the need for primary keys
- •Runs queries entirely without indexes
- •Stores every record in just one table
Why use a foreign key?
- •To compress data in the database file
- ✓To link records across two tables
- •To remove the need for primary keys
- •To store random keys in each record
What links two tables in a relational query?
- •A common SQL alias
- ✓A matching foreign key
- •A shared table name
- •An identical column data type
Which SQL removes Sam from Students?
- ✓DELETE FROM Students WHERE name = 'Sam'
- •DELETE Sam FROM Students table now
- •DROP Students WHERE name = 'Sam' only
- •REMOVE FROM Students WHERE name = 'Sam'
Three minutes a day. Knowledge that sticks.
Practise all 91 Relational Databases and SQL questions with spaced repetition, so what you revise today is still there in the exam. Free to use, no card needed.