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

The mistakes that cost the most marks

Every one of these is avoidable with a second look at the question.

• Choosing a name or an email as a primary key. Names repeat and emails change. A key must be unique and stable.
• 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 exam answer is less duplication and no inconsistent copies.
• 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 flat files break down

A flat file database keeps everything in one table. As soon as one thing relates to many others it forces you to repeat data, which wastes space and, worse, lets copies disagree with each other. Update one copy and the others are now wrong. A relational database stores data in several linked tables, each about one kind of thing, so each fact is stored once and every table can refer to it. The advantages over a flat file are less duplication, less storage, no risk of inconsistent copies, easier updating in one place, and easier control over who can see which table.

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. Relationships are usually one to many. One customer has many orders, so the order table carries a customer ID as a foreign key.

Reading data with SELECT

SQL is the language used to define, query and change the 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 is better practice because it returns only what you need. 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 is almost always used with a WHERE clause, because UPDATE without WHERE changes every record in the table. DELETE FROM removes records, again almost always with a WHERE clause, for the same reason. When a question gives you a table structure, read the field names exactly as printed and use them exactly. Inventing or misspelling a field name loses the mark.

The link to cyber security

SQL is also the reason SQL injection exists. If user input is pasted straight into a query, the user can write query logic of their own and read or destroy data. That is why the defence is input validation and parameterised queries. A question in this topic can cross over into section 3.6, and the answer is the same in both places.