The big idea: INSERT adds a row. UPDATE changes values in rows that already exist. DELETE removes rows.
All three are DML — the table's structure is untouched.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
Start Studying Free Full access to Aimnova Pro · cancel anytime
The most expensive mistake in SQL: An UPDATE or DELETE with no WHERE clause applies to every row in the table.
The database does not warn you. It does exactly what you asked, to all of it.
With a WHERE clause
- UPDATE Student SET YearGroup = 12 WHERE StudentID = 7
- Changes one row — the one identified
- Run a SELECT with the same WHERE first, to see what it will hit
- Habit worth building before you ever touch real data
Without one
- UPDATE Student SET YearGroup = 12
- Puts every student in Year 12
- No warning, no confirmation, and no undo once committed
- The same omission on DELETE empties the table
The rule of thumb: Write the WHERE clause first, test it with a SELECT, and only then change SELECT into UPDATE or DELETE.
It costs ten seconds and it is why professionals still do it.
Stop wasting time on topics you know
Our AI identifies your weak areas and focuses your study time where it matters. No more overstudying easy topics.
Try Smart Study FreeYour first topic is free to keep • No credit card required
An index is a second structure: An index is a separate sorted structure the database keeps so it can find rows quickly, instead of reading the whole table.
It makes reads fast — and it has to be kept up to date whenever the data changes.
1
Why an index speeds reads up
2
Why it slows writes down
3
Fragmentation
4
Rebuilding and reorganising
The bulk-load trick: When loading a very large amount of data, administrators often drop the indexes first, load everything, and rebuild them afterwards.
Building an index once at the end is far cheaper than updating it for every row on the way in.
How this is tested — you must write the statement with a correct WHERE, or explain the index trade. It comes up two ways:
Paper 1 Section A
- Explain how data is updated, or write the statement, 2-4 marks
- State the effect of omitting WHERE
- Explain the performance implications of indexes
Paper 1 Section B — case study
- Write the statements the case study would need
- Explain why their updates have become slow
The classic trap: Saying indexes "make the database faster". They make reads faster and writes slower. A question about slow updates is usually asking you to notice exactly that.
IB-style questionExplain[4 marks]
A shop's stock table is indexed on ProductID and QuantityInStock. Staff report that stock updates have become very slow. Explain why, and what could be done.
Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.