Relational databases and SQL: free AQA GCSE Computer Science practice questions

Mistakes to avoid

• Choosing a name as a primary key. Two people can share a name, but a primary key must uniquely identify each record.
• Writing UPDATE or DELETE without a WHERE clause. It changes or removes every record.
• Putting numbers in quotes or leaving quotes off text in a WHERE clause.
• Saying the advantage of a relational database is that it is faster. The point is that it helps to eliminate data redundancy and data inconsistency.
• Misspelling the field names given in the question. Use them exactly as printed.
• Putting the foreign key in the wrong table. In a one to many relationship it goes in the many table.

Why a relational database helps

A relational database stores data in several linked tables, each about one kind of thing, so each fact can be stored once and every table can refer to it through keys. That helps to eliminate data redundancy and data inconsistency, because a fact is updated in one place instead of in every copy.

Tables, records, fields and keys

A table holds records, which are the rows. Each record holds fields, which are the columns, and every field has a data type. A primary key is a field that uniquely identifies each record in a table. It cannot be empty and cannot repeat, which is why a surname is a bad primary key and an ID number is a good one. A foreign key is a field in one table that holds the primary key of another table. That is what links the two. For example, one customer can have many orders, so the order table carries a customer ID as a foreign key.

Reading data with SELECT

SQL is the language used to retrieve, insert, edit and delete data in a relational database. SELECT chooses which fields to return. FROM says which table they come from. WHERE filters which records. ORDER BY sorts the results, with ASC for ascending or DESC for descending. SELECT * FROM Members returns every field of every record. Naming the fields instead returns only those fields. WHERE conditions use the relational operators and can be combined with AND, OR and NOT. Text values go in quotes and numbers do not, as in WHERE Surname = 'Patel' AND Age > 15.

Changing data with INSERT, UPDATE and DELETE

INSERT INTO adds a new record. You give the table, the fields and the values in matching order. UPDATE changes existing records, and its WHERE clause picks which records change; without WHERE, every record in the table would change. DELETE FROM removes records, again using WHERE to pick which ones, for the same reason. When a question gives you a table structure, read the field names exactly as printed and use them exactly.